<?php
require_once __DIR__ . '/../config/config.php';
require_once __DIR__ . '/../vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

class QPSpreadsheetExport {
    private $pdo;

    public function __construct($pdo) {
        $this->pdo = $pdo;
    }

    /**
     * Export question paper data to Excel
     */
    public function exportQuestionPaper($assignment_id, $staff_id) {
        try {
            // Get assignment data
            $stmt = $this->pdo->prepare("
                SELECT a.*, e.exam_name, s.first_name, s.last_name, subj.subject_name,
                       subj.subject_code, crs.course_name, col.college_name
                FROM qp_setter_assignments a
                JOIN exams e ON a.exam_id = e.exam_id
                JOIN staff s ON a.staff_id = s.staff_id
                LEFT JOIN exam_subjects es ON a.exam_subject_id = es.exam_subject_id
                LEFT JOIN subjects subj ON es.subject_id = subj.subject_id
                LEFT JOIN courses crs ON subj.course_id = crs.course_id
                LEFT JOIN colleges col ON crs.college_id = col.college_id
                WHERE a.id = ? AND a.staff_id = ?
            ");
            $stmt->execute([$assignment_id, $staff_id]);
            $assignment = $stmt->fetch(PDO::FETCH_ASSOC);

            if (!$assignment) {
                throw new Exception('Assignment not found');
            }

            // Get submission data
            $stmt = $this->pdo->prepare("SELECT * FROM qp_setter_submissions WHERE assignment_id = ? AND staff_id = ? ORDER BY id DESC LIMIT 1");
            $stmt->execute([$assignment_id, $staff_id]);
            $submission = $stmt->fetch(PDO::FETCH_ASSOC);

            // Create spreadsheet
            $spreadsheet = new Spreadsheet();
            $spreadsheet->getProperties()
                ->setCreator('QP Setter System')
                ->setTitle('Question Paper - ' . $assignment['subject_name'])
                ->setSubject('Question Paper Export')
                ->setDescription('Exported question paper data');

            // Create worksheets
            $this->createHeaderSheet($spreadsheet, $assignment);
            $this->createPartASheet($spreadsheet, $submission);
            $this->createPartBSheet($spreadsheet, $submission);
            $this->createWeightageSheet($spreadsheet, $submission);

            // Remove default sheet
            $spreadsheet->removeSheetByIndex(0);

            // Generate filename
            $filename = 'QP_' . $assignment['subject_code'] . '_' . date('Y-m-d_H-i-s') . '.xlsx';

            // Save to output
            $writer = new Xlsx($spreadsheet);
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');
            $writer->save('php://output');
            exit;

        } catch (Exception $e) {
            header('Content-Type: application/json');
            echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
            exit;
        }
    }

    private function createHeaderSheet($spreadsheet, $assignment) {
        $sheet = $spreadsheet->createSheet();
        $sheet->setTitle('Header Info');

        // Header data
        $headerData = [
            ['Field', 'Value'],
            ['College Name', $assignment['college_name'] ?? ''],
            ['Course Name', $assignment['course_name'] ?? ''],
            ['Subject Code', $assignment['subject_code'] ?? ''],
            ['Subject Name', $assignment['subject_name'] ?? ''],
            ['Exam Name', $assignment['exam_name'] ?? ''],
            ['Staff Name', $assignment['first_name'] . ' ' . $assignment['last_name']],
            ['Max Marks', $assignment['max_marks'] ?? ''],
            ['Exam Duration', $assignment['exam_duration'] ?? ''],
            ['Session', $assignment['session'] ?? ''],
            ['Materials Note', $assignment['materials_note'] ?? '']
        ];

        // Write data
        foreach ($headerData as $row => $data) {
            foreach ($data as $col => $value) {
                $sheet->setCellValueByColumnAndRow($col + 1, $row + 1, $value);
            }
        }

        // Style header row
        $headerStyle = [
            'font' => ['bold' => true],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E3F2FD']]
        ];
        $sheet->getStyle('A1:B1')->applyFromArray($headerStyle);

        // Auto-size columns
        foreach (range('A', 'B') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
    }

    private function createPartASheet($spreadsheet, $submission) {
        $sheet = $spreadsheet->createSheet();
        $sheet->setTitle('Part A - Questions');

        // Headers
        $headers = ['Q.No.', 'Question Text', 'CO', 'BTL', 'Unit No.', 'Line No.'];
        foreach ($headers as $col => $header) {
            $sheet->setCellValueByColumnAndRow($col + 1, 1, $header);
        }

        // Style headers
        $headerStyle = [
            'font' => ['bold' => true],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E8F5E8']],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
        ];
        $sheet->getStyle('A1:F1')->applyFromArray($headerStyle);

        if ($submission && $submission['template_json']) {
            $data = json_decode($submission['template_json'], true);
            if ($data && isset($data['part_a'])) {
                $row = 2;
                foreach ($data['part_a'] as $question) {
                    $sheet->setCellValueByColumnAndRow(1, $row, $question['question_no'] ?? '');
                    $sheet->setCellValueByColumnAndRow(2, $row, $question['question_text'] ?? '');
                    $sheet->setCellValueByColumnAndRow(3, $row, $question['co_codes'] ?? '');
                    $sheet->setCellValueByColumnAndRow(4, $row, $question['cognitive_level'] ?? '');
                    $sheet->setCellValueByColumnAndRow(5, $row, $question['unit_no'] ?? '');
                    $sheet->setCellValueByColumnAndRow(6, $row, $question['line_no'] ?? '');
                    $row++;
                }
            }
        }

        // Auto-size columns
        foreach (range('A', 'F') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
    }

    private function createPartBSheet($spreadsheet, $submission) {
        $sheet = $spreadsheet->createSheet();
        $sheet->setTitle('Part B - Questions');

        // Headers
        $headers = ['Q.No.', 'Option', 'Question Text', 'CO', 'BTL', 'Unit No.', 'Line No.'];
        foreach ($headers as $col => $header) {
            $sheet->setCellValueByColumnAndRow($col + 1, 1, $header);
        }

        // Style headers
        $headerStyle = [
            'font' => ['bold' => true],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'FFF3E0']],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
        ];
        $sheet->getStyle('A1:G1')->applyFromArray($headerStyle);

        if ($submission && $submission['template_json']) {
            $data = json_decode($submission['template_json'], true);
            if ($data && isset($data['part_b'])) {
                $row = 2;
                foreach ($data['part_b'] as $question) {
                    if (isset($question['options']) && is_array($question['options'])) {
                        foreach ($question['options'] as $option) {
                            $sheet->setCellValueByColumnAndRow(1, $row, $question['question_no'] ?? '');
                            $sheet->setCellValueByColumnAndRow(2, $row, $option['option'] ?? '');
                            $sheet->setCellValueByColumnAndRow(3, $row, $option['question_text'] ?? '');
                            $sheet->setCellValueByColumnAndRow(4, $row, $option['co_codes'] ?? '');
                            $sheet->setCellValueByColumnAndRow(5, $row, $option['cognitive_level'] ?? '');
                            $sheet->setCellValueByColumnAndRow(6, $row, $option['unit_no'] ?? '');
                            $sheet->setCellValueByColumnAndRow(7, $row, $option['line_no'] ?? '');
                            $row++;
                        }
                    }
                }
            }
        }

        // Auto-size columns
        foreach (range('A', 'G') as $columnID) {
            $sheet->getColumnDimension($columnID)->setAutoSize(true);
        }
    }

    private function createWeightageSheet($spreadsheet, $submission) {
        $sheet = $spreadsheet->createSheet();
        $sheet->setTitle('Weightage Analysis');

        // Get weightage data from submission form data
        $weightageData = [];
        if ($submission && $submission['form']) {
            $formData = json_decode($submission['form'], true);
            if ($formData) {
                // Extract weightage data
                foreach ($formData as $key => $value) {
                    if (strpos($key, 'weightage[') === 0) {
                        $weightageData[$key] = $value;
                    }
                }
            }
        }

        // Default BTL levels
        $btlLevels = ['Remember (L1)', 'Understand (L2)', 'Apply (L3)', 'Analyze (L4)', 'Evaluate (L5)', 'Create (L6)'];
        $coLevels = ['CO1', 'CO2', 'CO3', 'CO4', 'CO5'];

        // Headers
        $sheet->setCellValue('A1', 'BTL Level');
        $col = 'B';
        foreach ($coLevels as $co) {
            $sheet->setCellValue($col . '1', $co);
            $col++;
        }
        $sheet->setCellValue($col . '1', 'Total Marks');
        $col++;
        $sheet->setCellValue($col . '1', 'Total Marks (%)');

        // Style headers
        $headerStyle = [
            'font' => ['bold' => true],
            'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'F3E5F5']],
            'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
        ];
        $sheet->getStyle('A1:' . $col . '1')->applyFromArray($headerStyle);

        // Data rows
        $row = 2;
        $grandTotal = 0;
        foreach ($btlLevels as $btl) {
            $sheet->setCellValue('A' . $row, $btl);

            $rowTotal = 0;
            $col = 'B';
            foreach ($coLevels as $co) {
                $key = "weightage[" . $btl . "][" . strtolower($co) . "]";
                $value = $weightageData[$key] ?? 0;
                $sheet->setCellValue($col . $row, $value);
                $rowTotal += $value;
                $col++;
            }

            $sheet->setCellValue($col . $row, $rowTotal);
            $col++;
            $sheet->setCellValue($col . $row, ''); // Percentage will be calculated later
            $grandTotal += $rowTotal;
            $row++;
        }

        // Total row
        $sheet->setCellValue('A' . $row, 'Total Marks');
        $col = 'B';
        $coTotals = [];
        foreach ($coLevels as $co) {
            $coKey = strtolower($co);
            $coTotal = 0;
            foreach ($btlLevels as $btl) {
                $key = "weightage[" . $btl . "][" . $coKey . "]";
                $coTotal += $weightageData[$key] ?? 0;
            }
            $sheet->setCellValue($col . $row, $coTotal);
            $coTotals[] = $coTotal;
            $col++;
        }
        $sheet->setCellValue($col . $row, $grandTotal);
        $col++;
        $sheet->setCellValue($col . $row, '100%');

        // Calculate percentages
        for ($i = 2; $i < $row; $i++) {
            $percentageCol = $col - 1;
            if ($grandTotal > 0) {
                $rowTotal = $sheet->getCellByColumnAndRow($percentageCol - 1, $i)->getValue();
                $percentage = round(($rowTotal / $grandTotal) * 100) . '%';
                $sheet->setCellValueByColumnAndRow($percentageCol, $i, $percentage);
            }
        }

        // Auto-size columns
        $lastCol = $col;
        for ($i = 1; $i <= $lastCol; $i++) {
            $columnLetter = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::stringFromColumnIndex($i);
            $sheet->getColumnDimension($columnLetter)->setAutoSize(true);
        }
    }

    /**
     * Export assignments list to Excel
     */
    public function exportAssignmentsList($staff_id = null) {
        try {
            $query = "
                SELECT
                    a.id as assignment_id,
                    a.staff_id,
                    s.first_name,
                    s.last_name,
                    s.staff_code,
                    e.exam_name,
                    e.academic_year,
                    e.semester,
                    subj.subject_name,
                    subj.subject_code,
                    crs.course_name,
                    col.college_name,
                    a.max_marks,
                    a.exam_duration,
                    a.session,
                    a.materials_note,
                    a.status as assignment_status,
                    a.created_at,
                    COALESCE(sub.status, 'Not Started') as submission_status,
                    sub.submitted_at
                FROM qp_setter_assignments a
                JOIN staff s ON a.staff_id = s.staff_id
                JOIN exams e ON a.exam_id = e.exam_id
                LEFT JOIN exam_subjects es ON a.exam_subject_id = es.exam_subject_id
                LEFT JOIN subjects subj ON es.subject_id = subj.subject_id
                LEFT JOIN courses crs ON subj.course_id = crs.course_id
                LEFT JOIN colleges col ON crs.college_id = col.college_id
                LEFT JOIN qp_setter_submissions sub ON a.id = sub.assignment_id AND a.staff_id = sub.staff_id
            ";

            $params = [];
            if ($staff_id) {
                $query .= " WHERE a.staff_id = ?";
                $params[] = $staff_id;
            }

            $query .= " ORDER BY a.created_at DESC";

            $stmt = $this->pdo->prepare($query);
            $stmt->execute($params);
            $assignments = $stmt->fetchAll(PDO::FETCH_ASSOC);

            // Create spreadsheet
            $spreadsheet = new Spreadsheet();
            $spreadsheet->getProperties()
                ->setCreator('QP Setter System')
                ->setTitle('Assignments Export')
                ->setSubject('Assignments List Export');

            $sheet = $spreadsheet->getActiveSheet();
            $sheet->setTitle('Assignments');

            // Headers
            $headers = [
                'Assignment ID', 'Staff Code', 'Staff Name', 'College', 'Course',
                'Subject Code', 'Subject Name', 'Exam Name', 'Academic Year',
                'Semester', 'Max Marks', 'Duration', 'Session', 'Materials Note',
                'Assignment Status', 'Submission Status', 'Created Date', 'Submitted Date'
            ];

            foreach ($headers as $col => $header) {
                $sheet->setCellValueByColumnAndRow($col + 1, 1, $header);
            }

            // Style headers
            $headerStyle = [
                'font' => ['bold' => true],
                'fill' => ['fillType' => Fill::FILL_SOLID, 'startColor' => ['rgb' => 'E3F2FD']],
                'borders' => ['allBorders' => ['borderStyle' => Border::BORDER_THIN]]
            ];
            $sheet->getStyle('A1:R1')->applyFromArray($headerStyle);

            // Data rows
            $row = 2;
            foreach ($assignments as $assignment) {
                $sheet->setCellValueByColumnAndRow(1, $row, $assignment['assignment_id']);
                $sheet->setCellValueByColumnAndRow(2, $row, $assignment['staff_code']);
                $sheet->setCellValueByColumnAndRow(3, $row, $assignment['first_name'] . ' ' . $assignment['last_name']);
                $sheet->setCellValueByColumnAndRow(4, $row, $assignment['college_name']);
                $sheet->setCellValueByColumnAndRow(5, $row, $assignment['course_name']);
                $sheet->setCellValueByColumnAndRow(6, $row, $assignment['subject_code']);
                $sheet->setCellValueByColumnAndRow(7, $row, $assignment['subject_name']);
                $sheet->setCellValueByColumnAndRow(8, $row, $assignment['exam_name']);
                $sheet->setCellValueByColumnAndRow(9, $row, $assignment['academic_year']);
                $sheet->setCellValueByColumnAndRow(10, $row, $assignment['semester']);
                $sheet->setCellValueByColumnAndRow(11, $row, $assignment['max_marks']);
                $sheet->setCellValueByColumnAndRow(12, $row, $assignment['exam_duration']);
                $sheet->setCellValueByColumnAndRow(13, $row, $assignment['session']);
                $sheet->setCellValueByColumnAndRow(14, $row, $assignment['materials_note']);
                $sheet->setCellValueByColumnAndRow(15, $row, $assignment['assignment_status']);
                $sheet->setCellValueByColumnAndRow(16, $row, $assignment['submission_status']);
                $sheet->setCellValueByColumnAndRow(17, $row, $assignment['created_at']);
                $sheet->setCellValueByColumnAndRow(18, $row, $assignment['submitted_at']);
                $row++;
            }

            // Auto-size columns
            foreach (range('A', 'R') as $columnID) {
                $sheet->getColumnDimension($columnID)->setAutoSize(true);
            }

            // Generate filename
            $filename = 'Assignments_Export_' . date('Y-m-d_H-i-s') . '.xlsx';

            // Save to output
            $writer = new Xlsx($spreadsheet);
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . $filename . '"');
            header('Cache-Control: max-age=0');
            $writer->save('php://output');
            exit;

        } catch (Exception $e) {
            header('Content-Type: application/json');
            echo json_encode(['status' => 'error', 'message' => $e->getMessage()]);
            exit;
        }
    }
}

// Handle API requests
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
    $action = $_GET['action'] ?? '';
    $staff_id = $_SESSION['qps_staff_id'] ?? 0;
    $assignment_id = $_GET['assignment_id'] ?? 0;

    $exporter = new QPSpreadsheetExport($pdo);

    switch ($action) {
        case 'export_question_paper':
            if ($assignment_id && $staff_id) {
                $exporter->exportQuestionPaper($assignment_id, $staff_id);
            } else {
                header('Content-Type: application/json');
                echo json_encode(['status' => 'error', 'message' => 'Missing assignment_id or staff_id']);
            }
            break;

        case 'export_assignments':
            $exporter->exportAssignmentsList($staff_id ?: null);
            break;

        default:
            header('Content-Type: application/json');
            echo json_encode(['status' => 'error', 'message' => 'Invalid action']);
            break;
    }
}
?>