<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

final class OperationsModel extends Model
{
    public function conferences(): array
    {
        return $this->db->query("SELECT * FROM conferences ORDER BY id DESC")->fetchAll();
    }

    public function attendanceByConference(int $conferenceId): array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM attendance_registrations
            WHERE conference_id = :conference_id
            ORDER BY id DESC
        ");
        $stmt->execute([':conference_id' => $conferenceId]);
        return $stmt->fetchAll();
    }

    public function createAttendance(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO attendance_registrations (
                conference_id, user_id, full_name_en, full_name_ar, email, mobile,
                attendee_type, qr_token, notes
            ) VALUES (
                :conference_id, :user_id, :full_name_en, :full_name_ar, :email, :mobile,
                :attendee_type, :qr_token, :notes
            )
        ");
        $stmt->execute([
            ':conference_id' => $data['conference_id'],
            ':user_id' => $data['user_id'] ?: null,
            ':full_name_en' => $data['full_name_en'],
            ':full_name_ar' => $data['full_name_ar'] ?: null,
            ':email' => $data['email'] ?: null,
            ':mobile' => $data['mobile'] ?: null,
            ':attendee_type' => $data['attendee_type'] ?: 'attendee',
            ':qr_token' => $data['qr_token'],
            ':notes' => $data['notes'] ?: null,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function checkinByQr(string $qrToken, int $userId): void
    {
        $stmt = $this->db->prepare("
            UPDATE attendance_registrations
            SET is_checked_in = 1,
                checked_in_at = NOW(),
                checked_in_by = :checked_in_by
            WHERE qr_token = :qr_token
        ");
        $stmt->execute([
            ':checked_in_by' => $userId,
            ':qr_token' => $qrToken,
        ]);
    }

    public function presenterCheckinByQr(string $qrToken): void
    {
        $stmt = $this->db->prepare("
            UPDATE attendance_registrations
            SET presenter_checked_in = 1,
                presenter_checked_in_at = NOW()
            WHERE qr_token = :qr_token
        ");
        $stmt->execute([
            ':qr_token' => $qrToken,
        ]);
    }

    public function invoicesByConference(int $conferenceId): array
    {
        $stmt = $this->db->prepare("
            SELECT pi.*, s.submission_no
            FROM payment_invoices pi
            LEFT JOIN submissions s ON s.id = pi.submission_id
            WHERE pi.conference_id = :conference_id
            ORDER BY pi.id DESC
        ");
        $stmt->execute([':conference_id' => $conferenceId]);
        return $stmt->fetchAll();
    }

    public function createInvoice(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO payment_invoices (
                conference_id, submission_id, user_id, invoice_no, amount, currency,
                invoice_type, payment_status, issued_at, notes
            ) VALUES (
                :conference_id, :submission_id, :user_id, :invoice_no, :amount, :currency,
                :invoice_type, :payment_status, NOW(), :notes
            )
        ");
        $stmt->execute([
            ':conference_id' => $data['conference_id'],
            ':submission_id' => $data['submission_id'] ?: null,
            ':user_id' => $data['user_id'] ?: null,
            ':invoice_no' => $data['invoice_no'],
            ':amount' => $data['amount'],
            ':currency' => $data['currency'] ?: 'JOD',
            ':invoice_type' => $data['invoice_type'] ?: 'registration',
            ':payment_status' => $data['payment_status'] ?: 'issued',
            ':notes' => $data['notes'] ?: null,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function markInvoicePaid(int $invoiceId, float $amount): void
    {
        $stmt = $this->db->prepare("
            UPDATE payment_invoices
            SET payment_status = 'paid',
                paid_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute([':id' => $invoiceId]);

        $receiptNo = 'RCT-' . date('YmdHis') . '-' . $invoiceId;
        $stmt = $this->db->prepare("
            INSERT INTO payment_receipts (invoice_id, receipt_no, amount, paid_by)
            VALUES (:invoice_id, :receipt_no, :amount, :paid_by)
        ");
        $stmt->execute([
            ':invoice_id' => $invoiceId,
            ':receipt_no' => $receiptNo,
            ':amount' => $amount,
            ':paid_by' => auth_id(),
        ]);
    }

    public function emailTemplates(): array
    {
        return $this->db->query("SELECT * FROM email_templates ORDER BY template_key ASC")->fetchAll();
    }

    public function emailTemplateById(int $id): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM email_templates WHERE id = :id LIMIT 1");
        $stmt->execute([':id' => $id]);
        return $stmt->fetch() ?: null;
    }

    public function updateEmailTemplate(int $id, array $data): void
    {
        $stmt = $this->db->prepare("
            UPDATE email_templates
            SET subject_en = :subject_en,
                subject_ar = :subject_ar,
                body_en = :body_en,
                body_ar = :body_ar,
                is_active = :is_active
            WHERE id = :id
        ");
        $stmt->execute([
            ':id' => $id,
            ':subject_en' => $data['subject_en'] ?: null,
            ':subject_ar' => $data['subject_ar'] ?: null,
            ':body_en' => $data['body_en'] ?: null,
            ':body_ar' => $data['body_ar'] ?: null,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
    }

    public function submissionsAll(): array
    {
        return $this->db->query("
            SELECT s.*, c.title_en AS conference_title_en, c.title_ar AS conference_title_ar
            FROM submissions s
            LEFT JOIN conferences c ON c.id = s.conference_id
            ORDER BY s.id DESC
        ")->fetchAll();
    }

    public function workflowBySubmission(int $submissionId): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM author_workflows WHERE submission_id = :submission_id LIMIT 1");
        $stmt->execute([':submission_id' => $submissionId]);
        return $stmt->fetch() ?: null;
    }

    public function saveWorkflow(array $data): void
    {
        $stmt = $this->db->prepare("
            INSERT INTO author_workflows (
                submission_id, workflow_stage, workflow_status, notes_en, notes_ar, updated_by
            ) VALUES (
                :submission_id, :workflow_stage, :workflow_status, :notes_en, :notes_ar, :updated_by
            )
            ON DUPLICATE KEY UPDATE
                workflow_stage = VALUES(workflow_stage),
                workflow_status = VALUES(workflow_status),
                notes_en = VALUES(notes_en),
                notes_ar = VALUES(notes_ar),
                updated_by = VALUES(updated_by)
        ");
        $stmt->execute([
            ':submission_id' => $data['submission_id'],
            ':workflow_stage' => $data['workflow_stage'],
            ':workflow_status' => $data['workflow_status'],
            ':notes_en' => $data['notes_en'] ?: null,
            ':notes_ar' => $data['notes_ar'] ?: null,
            ':updated_by' => $data['updated_by'] ?: null,
        ]);
    }

    public function roundRows(int $submissionId): array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM submission_review_rounds
            WHERE submission_id = :submission_id
            ORDER BY round_no ASC
        ");
        $stmt->execute([':submission_id' => $submissionId]);
        return $stmt->fetchAll();
    }

    public function openNextRound(int $submissionId, int $userId, string $notesEn = '', string $notesAr = ''): void
    {
        $stmt = $this->db->prepare("SELECT COALESCE(MAX(round_no),0) FROM submission_review_rounds WHERE submission_id = :submission_id");
        $stmt->execute([':submission_id' => $submissionId]);
        $next = ((int) $stmt->fetchColumn()) + 1;

        $stmt = $this->db->prepare("
            INSERT INTO submission_review_rounds (
                submission_id, round_no, round_status, opened_by, notes_en, notes_ar
            ) VALUES (
                :submission_id, :round_no, 'open', :opened_by, :notes_en, :notes_ar
            )
        ");
        $stmt->execute([
            ':submission_id' => $submissionId,
            ':round_no' => $next,
            ':opened_by' => $userId,
            ':notes_en' => $notesEn ?: null,
            ':notes_ar' => $notesAr ?: null,
        ]);
    }

    public function closeRound(int $roundId): void
    {
        $stmt = $this->db->prepare("
            UPDATE submission_review_rounds
            SET round_status = 'closed',
                closed_at = NOW()
            WHERE id = :id
        ");
        $stmt->execute([':id' => $roundId]);
    }

    public function reviewerCertificateEligible(int $reviewerId, int $conferenceId): bool
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*)
            FROM review_assignments ra
            INNER JOIN submissions s ON s.id = ra.submission_id
            WHERE ra.reviewer_id = :reviewer_id
              AND s.conference_id = :conference_id
              AND ra.status = 'completed'
        ");
        $stmt->execute([
            ':reviewer_id' => $reviewerId,
            ':conference_id' => $conferenceId,
        ]);
        return (int) $stmt->fetchColumn() > 0;
    }

    public function attendanceCertificateEligible(int $attendanceId): bool
    {
        $stmt = $this->db->prepare("
            SELECT is_checked_in
            FROM attendance_registrations
            WHERE id = :id
            LIMIT 1
        ");
        $stmt->execute([':id' => $attendanceId]);
        return (int) $stmt->fetchColumn() === 1;
    }

    public function tpcCertificateEligible(string $email, int $conferenceId): bool
    {
        $stmt = $this->db->prepare("
            SELECT COUNT(*)
            FROM conference_committees
            WHERE conference_id = :conference_id
              AND committee_type = 'tpc'
              AND email = :email
              AND is_active = 1
        ");
        $stmt->execute([
            ':conference_id' => $conferenceId,
            ':email' => $email,
        ]);
        return (int) $stmt->fetchColumn() > 0;
    }
}
