#!/usr/bin/env python
"""
GDPR Data Management
Handles user data export, deletion, and compliance requirements.
"""

import json
import time
from datetime import datetime, timedelta
from typing import Dict, Any, Optional, List
import logging
import sys
from pathlib import Path

# Add parent directory to path for imports
sys.path.insert(0, str(Path(__file__).parent.parent))
from database.transactions import transaction

logger = logging.getLogger(__name__)


class DataManagementService:
    """Manages GDPR and privacy compliance for user data."""

    def __init__(self, db_connection=None):
        """
        Initialize the data management service.

        Args:
            db_connection: MySQL database connection
        """
        self.db = db_connection

    def export_player_data(self, player_id: str) -> Dict[str, Any]:
        """
        Export all personal data for a player (GDPR Article 20 - Data Portability).

        Uses REPEATABLE READ isolation to ensure consistent snapshot of data.

        Args:
            player_id: Player's unique identifier

        Returns:
            Dictionary containing all player data
        """
        if not self.db:
            raise Exception("Database connection not available")

        try:
            export_data = {
                'export_date': datetime.now().isoformat(),
                'player_id': player_id,
                'format_version': '1.0'
            }

            # Use transaction with REPEATABLE READ isolation for consistent data snapshot
            with transaction(conn=self.db, isolation_level='REPEATABLE READ') as (conn, cursor):
                # Export user account data
                cursor.execute("""
                    SELECT userID, firstname, lastname, sex, email, dateCreated, lastLogin
                    FROM users
                    WHERE userID = %s
                """, (player_id,))
                user_data = cursor.fetchone()
                if not user_data:
                    raise Exception(f"Player {player_id} not found")
                export_data['account'] = user_data

                # Export character data
                cursor.execute("""
                    SELECT * FROM characters
                    WHERE userID = %s
                """, (player_id,))
                export_data['character'] = cursor.fetchone()

                # Export relationships
                cursor.execute("""
                    SELECT * FROM relationships
                    WHERE player_id = %s OR target_id = %s
                """, (player_id, player_id))
                export_data['relationships'] = cursor.fetchall()

                # Export activities/events history
                cursor.execute("""
                    SELECT * FROM player_events
                    WHERE player_id = %s
                    ORDER BY timestamp DESC
                    LIMIT 1000
                """, (player_id,))
                export_data['events'] = cursor.fetchall()

                # Export purchases and transactions
                cursor.execute("""
                    SELECT transaction_id, item_id, amount, currency, timestamp, status
                    FROM transactions
                    WHERE player_id = %s
                    ORDER BY timestamp DESC
                """, (player_id,))
                export_data['purchases'] = cursor.fetchall()

                # Export achievements
                cursor.execute("""
                    SELECT * FROM achievements
                    WHERE player_id = %s
                """, (player_id,))
                export_data['achievements'] = cursor.fetchall()

                # Export analytics events
                cursor.execute("""
                    SELECT event_name, properties, timestamp
                    FROM analytics_events
                    WHERE player_id = %s
                    ORDER BY timestamp DESC
                    LIMIT 5000
                """, (player_id,))
                export_data['analytics'] = cursor.fetchall()

                # Export preferences and settings
                cursor.execute("""
                    SELECT * FROM player_settings
                    WHERE player_id = %s
                """, (player_id,))
                export_data['settings'] = cursor.fetchone()

                # Transaction will auto-commit (read-only, but ensures consistency)

            logger.info(f"Exported data for player {player_id}")
            return export_data

        except Exception as e:
            logger.error(f"Failed to export player data: {e}")
            raise

    def delete_player_account(self, player_id: str, reason: Optional[str] = None) -> bool:
        """
        Soft delete a player account (GDPR Article 17 - Right to Erasure).
        Marks account as deleted and schedules permanent deletion after 30 days.

        Args:
            player_id: Player's unique identifier
            reason: Optional deletion reason

        Returns:
            True if successful
        """
        if not self.db:
            raise Exception("Database connection not available")

        try:
            cursor = self.db.cursor()

            # Check if account exists
            cursor.execute("SELECT userID FROM users WHERE userID = %s", (player_id,))
            if not cursor.fetchone():
                raise Exception(f"Player {player_id} not found")

            # Mark account as deleted (soft delete)
            deletion_date = datetime.now()
            permanent_deletion_date = deletion_date + timedelta(days=30)

            cursor.execute("""
                UPDATE users
                SET status = 'deleted',
                    deleted_at = %s,
                    permanent_deletion_at = %s,
                    deletion_reason = %s
                WHERE userID = %s
            """, (deletion_date, permanent_deletion_date, reason, player_id))

            # Anonymize immediately visible personal data
            cursor.execute("""
                UPDATE users
                SET email = CONCAT('deleted_', userID, '@deleted.local'),
                    firstname = 'Deleted',
                    lastname = 'User'
                WHERE userID = %s
            """, (player_id,))

            # Mark character as deleted
            cursor.execute("""
                UPDATE characters
                SET status = 'deleted'
                WHERE userID = %s
            """, (player_id,))

            # Log the deletion request
            cursor.execute("""
                INSERT INTO deletion_log (player_id, requested_at, scheduled_deletion_at, reason, status)
                VALUES (%s, %s, %s, %s, 'pending')
            """, (player_id, deletion_date, permanent_deletion_date, reason))

            self.db.commit()
            cursor.close()

            logger.info(f"Soft deleted account for player {player_id}, permanent deletion scheduled for {permanent_deletion_date}")
            return True

        except Exception as e:
            logger.error(f"Failed to delete player account: {e}")
            if self.db:
                self.db.rollback()
            raise

    def hard_delete_player_data(self, player_id: str) -> bool:
        """
        Permanently delete all player data (irreversible).
        This should only be called after the 30-day grace period.

        Args:
            player_id: Player's unique identifier

        Returns:
            True if successful
        """
        if not self.db:
            raise Exception("Database connection not available")

        try:
            cursor = self.db.cursor()

            # Verify account is marked for deletion and grace period has passed
            cursor.execute("""
                SELECT permanent_deletion_at
                FROM users
                WHERE userID = %s AND status = 'deleted'
            """, (player_id,))
            result = cursor.fetchone()

            if not result:
                raise Exception(f"Player {player_id} not found or not marked for deletion")

            permanent_deletion_at = result[0]
            if datetime.now() < permanent_deletion_at:
                raise Exception(f"Grace period not expired. Deletion scheduled for {permanent_deletion_at}")

            # Delete from all tables (in correct order to respect foreign keys)
            # SAFE_TABLES whitelist to prevent SQL injection
            SAFE_TABLES = {
                'analytics_events',
                'player_events',
                'achievements',
                'transactions',
                'relationships',
                'player_settings',
                'analytics_sessions',
                'daily_rewards',
                'purchases',
                'conversations',
                'characters',
                'users'
            }

            tables = [
                'analytics_events',
                'player_events',
                'achievements',
                'transactions',
                'relationships',
                'player_settings',
                'analytics_sessions',
                'daily_rewards',
                'purchases',
                'conversations',
                'characters',
                'users'
            ]

            for table in tables:
                try:
                    # Validate table name against whitelist before using in query
                    if table not in SAFE_TABLES:
                        logger.error(f"Attempted to delete from non-whitelisted table: {table}")
                        continue
                    cursor.execute(f"DELETE FROM {table} WHERE player_id = %s OR userID = %s", (player_id, player_id))
                except Exception as e:
                    # Table might not exist or use different column name
                    logger.warning(f"Could not delete from {table}: {e}")

            # Update deletion log
            cursor.execute("""
                UPDATE deletion_log
                SET status = 'completed',
                    completed_at = %s
                WHERE player_id = %s
            """, (datetime.now(), player_id))

            self.db.commit()
            cursor.close()

            logger.info(f"Permanently deleted all data for player {player_id}")
            return True

        except Exception as e:
            logger.error(f"Failed to permanently delete player data: {e}")
            if self.db:
                self.db.rollback()
            raise

    def restore_deleted_account(self, player_id: str) -> bool:
        """
        Restore a soft-deleted account within the 30-day grace period.

        Args:
            player_id: Player's unique identifier

        Returns:
            True if successful
        """
        if not self.db:
            raise Exception("Database connection not available")

        try:
            cursor = self.db.cursor()

            # Check if account is soft-deleted and within grace period
            cursor.execute("""
                SELECT permanent_deletion_at, email
                FROM users
                WHERE userID = %s AND status = 'deleted'
            """, (player_id,))
            result = cursor.fetchone()

            if not result:
                raise Exception(f"Player {player_id} not found or not deleted")

            permanent_deletion_at, email = result
            if datetime.now() >= permanent_deletion_at:
                raise Exception("Grace period expired, account cannot be restored")

            # Restore account status
            cursor.execute("""
                UPDATE users
                SET status = 'active',
                    deleted_at = NULL,
                    permanent_deletion_at = NULL,
                    deletion_reason = NULL
                WHERE userID = %s
            """, (player_id,))

            # Restore character status
            cursor.execute("""
                UPDATE characters
                SET status = 'alive'
                WHERE userID = %s
            """, (player_id,))

            # Log the restoration
            cursor.execute("""
                UPDATE deletion_log
                SET status = 'restored',
                    completed_at = %s
                WHERE player_id = %s AND status = 'pending'
            """, (datetime.now(), player_id))

            self.db.commit()
            cursor.close()

            logger.info(f"Restored account for player {player_id}")
            return True

        except Exception as e:
            logger.error(f"Failed to restore account: {e}")
            if self.db:
                self.db.rollback()
            raise

    def hard_delete_expired_accounts(self) -> int:
        """
        Permanently delete all accounts that have passed their grace period.
        This should be run as a daily cron job.

        Returns:
            Number of accounts deleted
        """
        if not self.db:
            raise Exception("Database connection not available")

        try:
            cursor = self.db.cursor()

            # Find accounts ready for permanent deletion
            cursor.execute("""
                SELECT userID
                FROM users
                WHERE status = 'deleted'
                AND permanent_deletion_at <= %s
            """, (datetime.now(),))

            accounts = cursor.fetchall()
            cursor.close()

            deleted_count = 0
            for (player_id,) in accounts:
                try:
                    self.hard_delete_player_data(player_id)
                    deleted_count += 1
                except Exception as e:
                    logger.error(f"Failed to delete expired account {player_id}: {e}")

            logger.info(f"Permanently deleted {deleted_count} expired accounts")
            return deleted_count

        except Exception as e:
            logger.error(f"Failed to delete expired accounts: {e}")
            raise

    def get_data_summary(self, player_id: str) -> Dict[str, Any]:
        """
        Get a summary of what data we have for a player.

        Args:
            player_id: Player's unique identifier

        Returns:
            Dictionary with data summary
        """
        if not self.db:
            raise Exception("Database connection not available")

        try:
            cursor = self.db.cursor()

            summary = {
                'player_id': player_id,
                'data_categories': {}
            }

            # Count records in each table
            # SAFE_TABLES whitelist to prevent SQL injection
            SAFE_TABLES = {
                'relationships',
                'player_events',
                'transactions',
                'achievements',
                'analytics_events',
                'conversations'
            }

            tables = {
                'relationships': 'Relationships with other characters',
                'player_events': 'Game events and activities',
                'transactions': 'Purchase history',
                'achievements': 'Achievements earned',
                'analytics_events': 'Analytics and usage data',
                'conversations': 'Conversation history'
            }

            for table, description in tables.items():
                try:
                    # Validate table name against whitelist before using in query
                    if table not in SAFE_TABLES:
                        logger.error(f"Attempted to query non-whitelisted table: {table}")
                        continue
                    cursor.execute(f"SELECT COUNT(*) FROM {table} WHERE player_id = %s OR userID = %s", (player_id, player_id))
                    count = cursor.fetchone()[0]
                    summary['data_categories'][table] = {
                        'description': description,
                        'record_count': count
                    }
                except Exception:
                    pass

            cursor.close()
            return summary

        except Exception as e:
            logger.error(f"Failed to get data summary: {e}")
            raise


# Global data management service
_data_service = None


def get_data_service(db_connection=None) -> DataManagementService:
    """
    Get or create the global data management service instance.

    Args:
        db_connection: Optional database connection

    Returns:
        DataManagementService instance
    """
    global _data_service
    if _data_service is None:
        _data_service = DataManagementService(db_connection)
    return _data_service


# Convenience functions
def export_user_data(player_id: str, db_connection=None) -> str:
    """
    Export user data as JSON string.

    Args:
        player_id: Player's unique identifier
        db_connection: Optional database connection

    Returns:
        JSON string of exported data
    """
    service = get_data_service(db_connection)
    data = service.export_player_data(player_id)
    return json.dumps(data, indent=2, default=str)


def request_account_deletion(player_id: str, reason: Optional[str] = None, db_connection=None) -> bool:
    """
    Request account deletion.

    Args:
        player_id: Player's unique identifier
        reason: Optional deletion reason
        db_connection: Optional database connection

    Returns:
        True if successful
    """
    service = get_data_service(db_connection)
    return service.delete_player_account(player_id, reason)


# SQL for required tables
"""
-- Deletion log table
CREATE TABLE IF NOT EXISTS deletion_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    player_id VARCHAR(36) NOT NULL,
    requested_at TIMESTAMP NOT NULL,
    scheduled_deletion_at TIMESTAMP NOT NULL,
    completed_at TIMESTAMP NULL,
    reason TEXT,
    status ENUM('pending', 'completed', 'restored') DEFAULT 'pending',
    INDEX idx_player_id (player_id),
    INDEX idx_scheduled (scheduled_deletion_at),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add deletion fields to users table
ALTER TABLE users
ADD COLUMN deleted_at TIMESTAMP NULL,
ADD COLUMN permanent_deletion_at TIMESTAMP NULL,
ADD COLUMN deletion_reason TEXT NULL;
"""