<?php
require_once 'includes/config.php';
require_once 'includes/functions.php';
require_once 'includes/auth.php';
require_once 'includes/db.php';

class PanelAlignmentHandler {
    private $db;
    private $auth;
    private $existingCount;
    private $statistics;
    private $attributes;
    private $progress = 0;

    public function __construct() {
        $this->db = Database::getInstance();
        $this->auth = new Auth();
        $this->loadData();
    }

public function analyzePanelData() {
    try {
        $directives = [];
        
        // Rule 1: Age-based checks
        $violations = $this->db->query("
            SELECT p.panelist_id, p.attribute_values,
                   (SELECT value FROM JSON_TABLE(p.attribute_values, 
                        '$[\"3\"]' COLUMNS(value VARCHAR(255) PATH '$')) AS jt) as age,
                   (SELECT value FROM JSON_TABLE(p.attribute_values, 
                        '$[\"2\"]' COLUMNS(value VARCHAR(255) PATH '$')) AS jt) as gender,
                   (SELECT value FROM JSON_TABLE(p.attribute_values, 
                        '$[\"8\"]' COLUMNS(value VARCHAR(255) PATH '$')) AS jt) as employment,
                   (SELECT value FROM JSON_TABLE(p.attribute_values, 
                        '$[\"6\"]' COLUMNS(value VARCHAR(255) PATH '$')) AS jt) as income
            FROM panel_data p
        ");

        while ($record = $violations->fetch_assoc()) {
            $age = $record['age'];
            $employment = $record['employment'];
            $income = $record['income'];
            
            // Check 1: Under 15 with employment
            if (preg_match('/^(0-4|5-9|10-14)/', $age) && $employment !== '-') {
                $this->createDirective(
                    "Age-Employment Mismatch: Panelists under 15 years should not have employment status",
                    $record['panelist_id']
                );
            }

            // Check 2: Under 18 with high income
            if (preg_match('/^(0-4|5-9|10-14|15-19)/', $age) && 
                strpos($income, 'Middle Class') !== false || strpos($income, 'Rich') !== false) {
                $this->createDirective(
                    "Age-Income Inconsistency: Underage panelists with unrealistic income levels",
                    $record['panelist_id']
                );
            }

            // Check 3: Elderly (80+) in regular employment
            if ($age === '80+ years' && $employment === 'Regular Wages/Salaried Employees') {
                $this->createDirective(
                    "Age-Employment Anomaly: Elderly panelists (80+) listed with regular employment",
                    $record['panelist_id']
                );
            }
        }

        return [
            'success' => true,
            'message' => 'Panel analysis completed'
        ];

    } catch (Exception $e) {
        error_log("Panel analysis error: " . $e->getMessage());
        return [
            'success' => false,
            'message' => $e->getMessage()
        ];
    }
}

private function createDirective($description, $panelistId) {
    $this->db->query("START TRANSACTION");
    
    try {
        // Check if similar directive exists
        $checkSql = "SELECT id FROM panel_directives WHERE description = '" . 
            $this->db->escape($description) . "' AND status = 'pending'";
        $existing = $this->db->query($checkSql);

        if ($existing && $existing->num_rows > 0) {
            $directiveId = $existing->fetch_assoc()['id'];
        } else {
            // Create new directive
            $sql = "INSERT INTO panel_directives (description) VALUES ('" . 
                $this->db->escape($description) . "')";
            if (!$this->db->query($sql)) {
                throw new Exception("Failed to create directive");
            }
            $directiveId = $this->db->getLastInsertId();
        }

        // Add record to directive
        $sql = "INSERT INTO directive_records (directive_id, panelist_id) VALUES (
            $directiveId, '" . $this->db->escape($panelistId) . "'
        )";
        if (!$this->db->query($sql)) {
            throw new Exception("Failed to add record to directive");
        }

        $this->db->query("COMMIT");
    } catch (Exception $e) {
        $this->db->query("ROLLBACK");
        throw $e;
    }
}

    private function loadData() {
        // Get existing panel count
        $result = $this->db->query("SELECT COUNT(*) as count FROM panel_data");
        $this->existingCount = $result->fetch_assoc()['count'];

        // Load statistics
        $this->statistics = [];
        $stats_query = $this->db->query("
            SELECT s.*, 
                   GROUP_CONCAT(DISTINCT a.id) as attribute_ids,
                   GROUP_CONCAT(DISTINCT a.name) as attribute_names
            FROM statistics s
            JOIN statistic_attributes sa ON s.id = sa.statistic_id
            JOIN attributes a ON a.id = sa.attribute_id
            GROUP BY s.id
        ");
        while ($stat = $stats_query->fetch_assoc()) {
            $this->statistics[] = $stat;
        }

        // Load attributes
        $this->attributes = [];
        $attr_query = $this->db->query("SELECT * FROM attributes ORDER BY created_at ASC");
        while ($attr = $attr_query->fetch_assoc()) {
            $this->attributes[] = $attr;
        }
    }

    public function deletePanelData() {
        try {
            // Check if user is admin
            if (!$this->auth->isAdmin()) {
                throw new Exception("Unauthorized: Only administrators can delete panel data");
            }

            // Begin transaction
            $this->db->query("START TRANSACTION");

            // Delete all panel data
            $sql = "TRUNCATE TABLE panel_data";
            if (!$this->db->query($sql)) {
                throw new Exception("Failed to delete panel data");
            }

            // Log the action (optional)
            $userId = $_SESSION['user_id'];
            $timestamp = date('Y-m-d H:i:s');
            error_log("Panel data deleted by user ID: $userId at $timestamp");

            $this->db->query("COMMIT");

            return [
                'success' => true,
                'message' => 'Panel data deleted successfully'
            ];
        } catch (Exception $e) {
            $this->db->query("ROLLBACK");
            error_log("Delete panel error: " . $e->getMessage());
            return [
                'success' => false,
                'message' => $e->getMessage()
            ];
        }
    }

    public function calculateAlignmentScore() {
    try {
        // If panel is empty, return 100% deviation (0% alignment)
        $countCheck = $this->db->query("SELECT COUNT(*) as count FROM panel_data");
        if ($countCheck->fetch_assoc()['count'] == 0) {
            return [
                'success' => true,
                'score' => 100.00, // Maximum deviation
                'message' => 'Panel is empty'
            ];
        }

        $totalDeviation = 0;
        $combinationCount = 0;
        
        $query = $this->db->query("
            SELECT s.id, s.name, sc.combination_values, sc.percentage as target_percentage,
                (
                    SELECT ROUND((COUNT(*) * 100.0 / NULLIF((SELECT COUNT(*) FROM panel_data), 0)), 4)
                    FROM panel_data pd
                    WHERE (
                        SELECT COUNT(*)
                        FROM JSON_TABLE(
                            sc.combination_values,
                            '$[*]' COLUMNS(value VARCHAR(255) PATH '$')
                        ) jt
                        JOIN statistic_attributes sa ON sa.statistic_id = s.id
                        WHERE JSON_UNQUOTE(
                            JSON_EXTRACT(pd.attribute_values, CONCAT('$.', sa.attribute_id))
                        ) = jt.value
                    ) = JSON_LENGTH(sc.combination_values)
                ) as actual_percentage
            FROM statistics s
            JOIN statistic_combinations sc ON s.id = sc.statistic_id
        ");

        while ($row = $query->fetch_assoc()) {
            $targetPct = floatval($row['target_percentage']);
            $actualPct = floatval($row['actual_percentage'] ?? 0);
            
            // Calculate relative deviation as a percentage of the target
            $deviation = abs($actualPct - $targetPct);
            
            $totalDeviation += $deviation;
            $combinationCount++;
        }

        // Calculate average deviation
        $averageDeviation = $combinationCount > 0 ? ($totalDeviation / $combinationCount) : 100;
        
        // Ensure deviation is capped at 100%
        $averageDeviation = min(100, $averageDeviation);
        
        return [
            'success' => true,
            'score' => round($averageDeviation, 2),
            'message' => 'Score calculated successfully'
        ];
    } catch (Exception $e) {
        error_log("Alignment score calculation error: " . $e->getMessage());
        return [
            'success' => false,
            'message' => $e->getMessage()
        ];
    }
}

    public function calculateOptimalCount() {
        try {
            $denominators = [];
            
            foreach ($this->statistics as $stat) {
                $combos = $this->db->query("
                    SELECT percentage 
                    FROM statistic_combinations 
                    WHERE statistic_id = {$stat['id']}
                ");
                
                while ($combo = $combos->fetch_assoc()) {
                    $percentage = floatval($combo['percentage']);
                    if ($percentage > 0 && $percentage < 100) {
                        $decimal = $percentage - floor($percentage);
                        if ($decimal > 0) {
                            $denominators[] = 1 / $decimal;
                        }
                    }
                }
            }

            if (empty($denominators)) {
                return ['success' => false, 'message' => 'No valid statistics found'];
            }

            $optimal = $this->calculateLCM($denominators);
            
            if ($this->existingCount > 0) {
                $nextMultiple = ceil($this->existingCount / $optimal) * $optimal;
                $optimal = $nextMultiple - $this->existingCount;
            }

            $additional_attributes = [];
            if ($this->existingCount > 0) {
                $attr_query = $this->db->query("
                    SELECT * FROM attributes 
                    WHERE created_at > (
                        SELECT MAX(created_at) FROM panel_data
                    )
                ");
                while ($attr = $attr_query->fetch_assoc()) {
                    $additional_attributes[] = $attr;
                }
            }

            return [
                'success' => true,
                'optimal_count' => $optimal,
                'additional_attributes' => $additional_attributes
            ];

        } catch (Exception $e) {
            error_log("Optimal count calculation error: " . $e->getMessage());
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    private function calculateLCM($numbers) {
        $lcm = 1;
        foreach ($numbers as $number) {
            $lcm = $this->lcm($lcm, ceil($number));
        }
        return $lcm;
    }

    private function lcm($a, $b) {
        return ($a * $b) / $this->gcd($a, $b);
    }

    private function gcd($a, $b) {
        while ($b != 0) {
            $t = $b;
            $b = $a % $b;
            $a = $t;
        }
        return $a;
    }

    public function generatePanelData($count) {
        try {
            $_SESSION['panel_generation_progress'] = 0;
            $_SESSION['panel_generation_status'] = 'Initializing...';

            $result = $this->db->query("SELECT MAX(CAST(panelist_id AS UNSIGNED)) as max_id FROM panel_data");
            $maxId = $result->fetch_assoc()['max_id'] ?? 0;
            $nextId = $maxId + 1;

            $batchSize = min(100, $count);
            $totalBatches = ceil($count / $batchSize);
            $processedCount = 0;

            for ($batch = 0; $batch < $totalBatches; $batch++) {
                $currentBatchSize = min($batchSize, $count - $processedCount);
                $this->generateBatch($currentBatchSize, $nextId + $processedCount);
                $processedCount += $currentBatchSize;

                $progress = ($processedCount / $count) * 100;
                $_SESSION['panel_generation_progress'] = $progress;
                $_SESSION['panel_generation_status'] = "Generating panel data... " . round($progress) . "%";
            }

            $_SESSION['panel_generation_progress'] = 100;
            $_SESSION['panel_generation_status'] = 'Complete!';
            
            $alignmentScore = $this->calculateAlignmentScore();
            
            return [
                'success' => true,
                'alignment_score' => $alignmentScore['score'] ?? null
            ];

        } catch (Exception $e) {
            error_log("Panel generation error: " . $e->getMessage());
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }

    private function generateBatch($size, $startId) {
        $this->db->query("START TRANSACTION");

        try {
            for ($i = 0; $i < $size; $i++) {
                $panelistId = str_pad($startId + $i, 6, '0', STR_PAD_LEFT);
                $attributeValues = $this->generateAttributeValues();

                $sql = "INSERT INTO panel_data (panelist_id, attribute_values, created_by) VALUES (
                    '" . $this->db->escape($panelistId) . "',
                    '" . $this->db->escape(json_encode($attributeValues)) . "',
                    " . $_SESSION['user_id'] . "
                )";

                if (!$this->db->query($sql)) {
                    throw new Exception("Failed to insert panelist data: " . $this->db->getLastError());
                }
            }

            $this->db->query("COMMIT");
            return true;

        } catch (Exception $e) {
            $this->db->query("ROLLBACK");
            error_log("Generate batch error: " . $e->getMessage());
            throw $e;
        }
    }

    private function generateAttributeValues() {
        $values = [];
        foreach ($this->attributes as $attr) {
            $choices = json_decode($attr['choices'], true);
            $values[$attr['id']] = $choices[array_rand($choices)];
        }
        return $values;
    }

    public function getProgress() {
        $alignmentScore = null;
        $progress = $_SESSION['panel_generation_progress'] ?? 0;
        
        if ($progress >= 100) {
            $alignmentResult = $this->calculateAlignmentScore();
            if ($alignmentResult['success']) {
                $alignmentScore = $alignmentResult['score'];
            }
        }
        
        return [
            'success' => true,
            'progress' => $progress,
            'status' => $_SESSION['panel_generation_status'] ?? 'Initializing...',
            'alignment_score' => $alignmentScore
        ];
    }

    public function deletePanelist($panelistId) {
        try {
            $sql = "DELETE FROM panel_data WHERE panelist_id = '" . $this->db->escape($panelistId) . "'";
            if ($this->db->query($sql)) {
                $alignmentScore = $this->calculateAlignmentScore();
                return [
                    'success' => true,
                    'alignment_score' => $alignmentScore['score'] ?? null
                ];
            }
            throw new Exception("Failed to delete panelist");
        } catch (Exception $e) {
            error_log("Delete panelist error: " . $e->getMessage());
            return ['success' => false, 'message' => $e->getMessage()];
        }
    }
}

// Handle requests
header('Content-Type: application/json');

$auth = new Auth();
if (!$auth->isLoggedIn()) {
    echo json_encode(['success' => false, 'message' => 'Unauthorized']);
    exit;
}

$handler = new PanelAlignmentHandler();
$action = $_POST['action'] ?? '';
$response = ['success' => false, 'message' => 'Invalid action'];

switch ($action) {
    case 'calculate_optimal':
        $response = $handler->calculateOptimalCount();
        break;

    case 'generate_panel':  
        $count = intval($_POST['count'] ?? 0);
        if ($count > 0) {
            $response = $handler->generatePanelData($count);
        } else {
            $response = ['success' => false, 'message' => 'Invalid count'];
        }
        break;

    case 'get_progress':
        $response = $handler->getProgress();
        break;

    case 'delete_panelist':
        $panelistId = $_POST['panelist_id'] ?? '';
        if ($panelistId) {
            $response = $handler->deletePanelist($panelistId);
        } else {
            $response = ['success' => false, 'message' => 'Invalid panelist ID'];
        }
        break;
        
    case 'get_alignment_score':
        $response = $handler->calculateAlignmentScore();
        break;
        
    case 'delete_panel':
        $response = $handler->deletePanelData();
        break;
        case 'analyze_panel':
    $response = $handler->analyzePanelData();
    break;
}

echo json_encode($response);