<?php

declare(strict_types=1);

namespace SuperAdmin\Controllers;

/**
 * Super Admin Certificate & Marksheet Mapping Controller
 *
 * Phase 5 redesign: Per-course field position mapping for Certificate and
 * Marksheet templates. The Super Admin maps WHERE each field should appear
 * on the JPG background image (x, y, font, size, color) for each course.
 *
 * SIMPLIFIED FIELD SET (14 fields only — user-requested):
 *   1.  student_name    — Student's full name
 *   2.  father_name     — Father's name
 *   3.  enrollment_no   — Enrollment number
 *   4.  certificate_no  — Document number (auto-generated)
 *   5.  percentage      — e.g. "85.5%"
 *   6.  grade           — A+, A, B+, B, C, D, F
 *   7.  branch_name     — Branch name
 *   8.  course          — Course name
 *   9.  duration        — Course duration (e.g. "1 Year")
 *   10. date_of_issue   — e.g. "15/06/2026"
 *   11. photo           — Student photo (rendered as image, not text)
 *   12. qr_code         — Verification QR code
 *   13. from_date       — e.g. "JAN 2025" (session start month-year)
 *   14. to_date         — e.g. "DEC 2025" (session end month-year)
 *   + subject_marks     — Subject-wise marks (table format in marksheet only)
 *
 * Each course gets ONE Certificate mapping + ONE Marksheet mapping.
 * The branch admin only enters student data + marks; the system fills
 * the mapped positions automatically when generating the PDF.
 */
class SuperAdminCertMarksheetMappingController extends SuperAdminBaseController
{
    /** The 15 standardized field keys (14 text/image + 1 subject_marks table) */
    private const FIELD_KEYS = [
        'student_name'   => ['label' => 'Student Name',    'type' => 'text',  'default_font' => 'Arial', 'default_size' => 14],
        'father_name'    => ['label' => "Father's Name",   'type' => 'text',  'default_font' => 'Arial', 'default_size' => 12],
        'enrollment_no'  => ['label' => 'Enrollment No',   'type' => 'text',  'default_font' => 'Courier', 'default_size' => 12],
        'certificate_no' => ['label' => 'Certificate No',  'type' => 'text',  'default_font' => 'Courier', 'default_size' => 12],
        'percentage'     => ['label' => 'Percentage',      'type' => 'text',  'default_font' => 'Arial', 'default_size' => 14],
        'grade'          => ['label' => 'Grade',           'type' => 'text',  'default_font' => 'Arial', 'default_size' => 14],
        'branch_name'    => ['label' => 'Branch Name',     'type' => 'text',  'default_font' => 'Arial', 'default_size' => 12],
        'course'         => ['label' => 'Course',          'type' => 'text',  'default_font' => 'Arial', 'default_size' => 12],
        'duration'       => ['label' => 'Duration',        'type' => 'text',  'default_font' => 'Arial', 'default_size' => 12],
        'date_of_issue'  => ['label' => 'Date of Issue',   'type' => 'text',  'default_font' => 'Arial', 'default_size' => 12],
        'photo'          => ['label' => 'Student Photo',   'type' => 'image', 'default_font' => 'Arial', 'default_size' => 100],
        'qr_code'        => ['label' => 'QR Code',         'type' => 'qrcode', 'default_font' => 'Arial', 'default_size' => 80],
        'from_date'      => ['label' => 'From (Month Year)', 'type' => 'text', 'default_font' => 'Arial', 'default_size' => 12],
        'to_date'        => ['label' => 'To (Month Year)',   'type' => 'text', 'default_font' => 'Arial', 'default_size' => 12],
        'subject_marks'  => ['label' => 'Subject Marks Table', 'type' => 'table', 'default_font' => 'Arial', 'default_size' => 11],
    ];

    /**
     * List all courses with their Certificate + Marksheet mapping status.
     */
    public function index(): void
    {
        requireSuperAdmin();

        // Fetch all active courses
        $stmt = $this->db->query("SELECT id, name, duration, subjects FROM courses WHERE active = 1 ORDER BY name");
        $courses = $stmt->fetchAll() ?: [];

        // For each course, check if cert + marksheet mappings exist
        foreach ($courses as &$c) {
            $stmt = $this->db->prepare(
                "SELECT COUNT(*) AS c FROM certificate_templates
                 WHERE course_id = :cid AND type = 'certificate' AND is_active = 1"
            );
            $stmt->execute([':cid' => $c['id']]);
            $c['has_certificate'] = (int)$stmt->fetch()['c'] > 0;

            $stmt = $this->db->prepare(
                "SELECT COUNT(*) AS c FROM certificate_templates
                 WHERE course_id = :cid AND type = 'marksheet' AND is_active = 1"
            );
            $stmt->execute([':cid' => $c['id']]);
            $c['has_marksheet'] = (int)$stmt->fetch()['c'] > 0;

            // Get the template IDs (if exist)
            $stmt = $this->db->prepare(
                "SELECT id, name FROM certificate_templates
                 WHERE course_id = :cid AND type IN ('certificate','marksheet') AND is_active = 1"
            );
            $stmt->execute([':cid' => $c['id']]);
            $c['templates'] = $stmt->fetchAll() ?: [];
        }
        unset($c);

        $this->superAdminView('cert_marksheet_mapping/index', [
            'title'       => 'Certificate & Marksheet Mapping',
            'currentPage' => 'cert_marksheet_mapping',
            'courses'     => $courses,
            'fieldKeys'   => self::FIELD_KEYS,
        ]);
    }

    /**
     * Show the mapping editor for a specific course + document type.
     *
     * @param int $courseId
     * @param string $type  'certificate' or 'marksheet'
     */
    public function edit(int $courseId, string $type): void
    {
        requireSuperAdmin();

        if (!in_array($type, ['certificate', 'marksheet'], true)) {
            flash('error', 'Invalid document type.');
            redirect(BASE_PATH . '/super-admin/cert-marksheet-mapping');
        }

        // Fetch course
        $stmt = $this->db->prepare("SELECT * FROM courses WHERE id = :cid LIMIT 1");
        $stmt->execute([':cid' => $courseId]);
        $course = $stmt->fetch() ?: null;
        if (!$course) {
            flash('error', 'Course not found.');
            redirect(BASE_PATH . '/super-admin/cert-marksheet-mapping');
        }

        // Find or create the template for this course + type
        $stmt = $this->db->prepare(
            "SELECT * FROM certificate_templates
             WHERE course_id = :cid AND type = :type AND is_active = 1
             LIMIT 1"
        );
        $stmt->execute([':cid' => $courseId, ':type' => $type]);
        $template = $stmt->fetch() ?: null;

        if (!$template) {
            // Auto-create a blank template for this course + type — A4 PORTRAIT
            $this->db->prepare(
                "INSERT INTO certificate_templates (name, type, course_id, background_image, template_width, template_height, is_active, description, created_at, updated_at)
                 VALUES (:name, :type, :cid, '', 595, 842, 1, :desc, NOW(), NOW())"
            )->execute([
                ':name'  => $course['name'] . ' ' . ucfirst($type),
                ':type'  => $type,
                ':cid'   => $courseId,
                ':desc'  => 'Auto-created mapping for ' . $course['name'],
            ]);
            $templateId = (int)$this->db->lastInsertId();
            // Re-fetch
            $stmt = $this->db->prepare("SELECT * FROM certificate_templates WHERE id = :tid LIMIT 1");
            $stmt->execute([':tid' => $templateId]);
            $template = $stmt->fetch();
        }

        // Fetch existing field mappings for this template
        $stmt = $this->db->prepare(
            "SELECT * FROM certificate_template_fields
             WHERE template_id = :tid ORDER BY sort_order ASC"
        );
        $stmt->execute([':tid' => $template['id']]);
        $existingFields = $stmt->fetchAll() ?: [];

        // Index by field_key for easy lookup
        $fieldsByKey = [];
        foreach ($existingFields as $f) {
            $fieldsByKey[$f['field_key']] = $f;
        }

        $this->superAdminView('cert_marksheet_mapping/edit', [
            'title'       => ucfirst($type) . ' Mapping: ' . $course['name'],
            'currentPage' => 'cert_marksheet_mapping',
            'course'      => $course,
            'template'    => $template,
            'type'        => $type,
            'fieldKeys'   => self::FIELD_KEYS,
            'fieldsByKey' => $fieldsByKey,
        ]);
    }

    /**
     * Save the mapping (called via AJAX or form POST).
     * Receives: course_id, type, background_image (file upload),
     * and field positions as arrays: field_key[], x_pos[], y_pos[], font_size[], etc.
     */
    public function save(): void
    {
        requireSuperAdmin();
        $this->validateCsrf();

        $courseId   = (int)($_POST['course_id'] ?? 0);
        $type       = trim($_POST['type'] ?? 'certificate');
        $templateId = (int)($_POST['template_id'] ?? 0);

        if (!in_array($type, ['certificate', 'marksheet'], true)) {
            flash('error', 'Invalid document type.');
            redirect(BASE_PATH . '/super-admin/cert-marksheet-mapping');
        }

        // Fetch template
        $stmt = $this->db->prepare("SELECT * FROM certificate_templates WHERE id = :tid LIMIT 1");
        $stmt->execute([':tid' => $templateId]);
        $template = $stmt->fetch() ?: null;
        if (!$template) {
            flash('error', 'Template not found.');
            redirect(BASE_PATH . '/super-admin/cert-marksheet-mapping');
        }

        // Handle background image upload
        $bgPath = $template['background_image'];
        if (!empty($_FILES['background_image']['name']) && $_FILES['background_image']['error'] === UPLOAD_ERR_OK) {
            $file = $_FILES['background_image'];
            $ext = strtolower(pathinfo($file['name'], PATHINFO_EXTENSION));
            if (!in_array($ext, ['jpg', 'jpeg', 'png'], true)) {
                flash('error', 'Background must be a JPG or PNG image.');
                redirect(BASE_PATH . '/super-admin/cert-marksheet-mapping/' . $courseId . '/' . $type);
            }
            $filename = 'cert_template_' . $templateId . '_' . time() . '.' . $ext;
            $uploadDir = PUBLIC_ROOT . '/uploads/certificate_templates/';
            if (!is_dir($uploadDir)) @mkdir($uploadDir, 0777, true);
            if (move_uploaded_file($file['tmp_name'], $uploadDir . $filename)) {
                $bgPath = 'uploads/certificate_templates/' . $filename;
            }
        }

        // Update template dimensions + background
        $templateWidth  = (int)($_POST['template_width']  ?? 842);
        $templateHeight = (int)($_POST['template_height'] ?? 595);
        $this->db->prepare(
            "UPDATE certificate_templates
             SET background_image = :bg, template_width = :w, template_height = :h, updated_at = NOW()
             WHERE id = :tid"
        )->execute([
            ':bg'  => $bgPath,
            ':w'   => $templateWidth,
            ':h'   => $templateHeight,
            ':tid' => $templateId,
        ]);

        // Delete existing field mappings for this template
        $this->db->prepare("DELETE FROM certificate_template_fields WHERE template_id = :tid")
            ->execute([':tid' => $templateId]);

        // ─── Auto-migrate: ensure certificate_template_fields has all needed columns ──
        // The user's table might be missing 'label', 'width', 'is_qrcode', 'qr_size', etc.
        $this->ensureTemplateFieldColumns();

        // Detect which columns actually exist in the table (for dynamic INSERT)
        $availableColumns = $this->getTableColumns('certificate_template_fields');

        // Insert new field mappings
        $fieldKeys = $_POST['field_key'] ?? [];
        $xPos      = $_POST['x_pos'] ?? [];
        $yPos      = $_POST['y_pos'] ?? [];
        $fontSize  = $_POST['font_size'] ?? [];
        $fontFamily = $_POST['font_family'] ?? [];
        $fontStyle = $_POST['font_style'] ?? [];
        $fontColor = $_POST['font_color'] ?? [];
        $align     = $_POST['align'] ?? [];
        $fieldWidth = $_POST['field_width'] ?? [];
        $isEnabled = $_POST['is_enabled'] ?? [];

        $sort = 0;
        foreach ($fieldKeys as $i => $fkey) {
            if (empty($isEnabled[$i])) continue; // skip disabled fields
            if (!isset(self::FIELD_KEYS[$fkey])) continue;

            $fieldDef = self::FIELD_KEYS[$fkey];
            $isQr = ($fkey === 'qr_code') ? 1 : 0;

            // Build dynamic INSERT — only include columns that exist in the table
            $insertData = [
                'template_id'  => $templateId,
                'field_key'    => $fkey,
                'x_pos'        => (int)($xPos[$i] ?? 0),
                'y_pos'        => (int)($yPos[$i] ?? 0),
                'font_size'    => (int)($fontSize[$i] ?? $fieldDef['default_size']),
                'font_family'  => $fontFamily[$i] ?? $fieldDef['default_font'],
                'font_style'   => $fontStyle[$i] ?? 'regular',
                'font_color'   => $fontColor[$i] ?? '#000000',
                'align'        => $align[$i] ?? 'left',
                'sort_order'   => $sort,
            ];

            // Only add these columns if they exist in the table
            if (in_array('label', $availableColumns, true)) {
                $insertData['label'] = $fieldDef['label'];
            }
            if (in_array('width', $availableColumns, true)) {
                $insertData['width'] = (int)($fieldWidth[$i] ?? 0) ?: null;
            }
            if (in_array('is_qrcode', $availableColumns, true)) {
                $insertData['is_qrcode'] = $isQr;
            }
            if (in_array('qr_size', $availableColumns, true)) {
                $insertData['qr_size'] = $isQr ? (int)($fontSize[$i] ?? 80) : null;
            }
            if (in_array('created_at', $availableColumns, true)) {
                $insertData['created_at'] = date('Y-m-d H:i:s');
            }

            // Build the INSERT SQL dynamically
            $cols = array_keys($insertData);
            $colList = implode(', ', array_map(fn($c) => "`{$c}`", $cols));
            $phList  = implode(', ', array_map(fn($c) => ":{$c}", $cols));
            $sql = "INSERT INTO certificate_template_fields ({$colList}) VALUES ({$phList})";

            $params = [];
            foreach ($insertData as $k => $v) {
                $params[":{$k}"] = $v;
            }

            $this->db->prepare($sql)->execute($params);
            $sort++;
        }

        $this->auditLog('cert_marksheet_mapping_save', 'certificate_templates', $templateId,
            "Saved {$type} mapping for course #{$courseId} ({$sort} fields)");

        flash('success', "✅ " . ucfirst($type) . " mapping saved for course. {$sort} field positions stored. Branch admins can now generate {$type}s for this course.");
        redirect(BASE_PATH . '/super-admin/cert-marksheet-mapping');
    }

    /**
     * Preview the mapping (shows the JPG background with field positions overlaid).
     */
    public function preview(int $templateId): void
    {
        requireSuperAdmin();

        $stmt = $this->db->prepare(
            "SELECT t.*, c.name AS course_name
             FROM certificate_templates t
             LEFT JOIN courses c ON t.course_id = c.id
             WHERE t.id = :tid LIMIT 1"
        );
        $stmt->execute([':tid' => $templateId]);
        $template = $stmt->fetch() ?: null;
        if (!$template) {
            flash('error', 'Template not found.');
            redirect(BASE_PATH . '/super-admin/cert-marksheet-mapping');
        }

        $stmt = $this->db->prepare(
            "SELECT * FROM certificate_template_fields WHERE template_id = :tid ORDER BY sort_order ASC"
        );
        $stmt->execute([':tid' => $templateId]);
        $fields = $stmt->fetchAll() ?: [];

        $this->superAdminView('cert_marksheet_mapping/preview', [
            'title'       => 'Preview: ' . $template['name'],
            'currentPage' => 'cert_marksheet_mapping',
            'template'    => $template,
            'fields'      => $fields,
        ]);
    }

    // ════════════════════════════════════════════════════════════════════════
    // PRIVATE HELPERS
    // ════════════════════════════════════════════════════════════════════════

    /**
     * Get the list of column names for a table.
     * Returns an array of column names (lowercase).
     */
    private function getTableColumns(string $table): array
    {
        try {
            $stmt = $this->db->prepare(
                "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :table"
            );
            $stmt->execute([':table' => $table]);
            $cols = $stmt->fetchAll(\PDO::FETCH_COLUMN);
            return array_map('strtolower', $cols ?: []);
        } catch (\Throwable $e) {
            return [];
        }
    }

    /**
     * Auto-migrate: ensure certificate_template_fields table has all needed columns.
     * Safety net — adds missing columns using ALTER TABLE if they don't exist.
     */
    private function ensureTemplateFieldColumns(): void
    {
        $existing = $this->getTableColumns('certificate_template_fields');

        $neededColumns = [
            'label'       => "ADD COLUMN `label` VARCHAR(100) NULL DEFAULT NULL",
            'width'       => "ADD COLUMN `width` INT NULL DEFAULT NULL",
            'is_qrcode'   => "ADD COLUMN `is_qrcode` TINYINT(1) NOT NULL DEFAULT 0",
            'qr_size'     => "ADD COLUMN `qr_size` INT NULL DEFAULT NULL",
            'sort_order'  => "ADD COLUMN `sort_order` INT NOT NULL DEFAULT 0",
            'created_at'  => "ADD COLUMN `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP",
        ];

        foreach ($neededColumns as $col => $ddl) {
            if (!in_array($col, $existing, true)) {
                try {
                    $this->db->exec("ALTER TABLE `certificate_template_fields` {$ddl}");
                    error_log("SuperAdminCertMarksheetMapping: Auto-added column `{$col}` to certificate_template_fields.");
                } catch (\Throwable $e) {
                    error_log("SuperAdminCertMarksheetMapping: Failed to add `{$col}`: " . $e->getMessage());
                }
            }
        }
    }
}
