<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

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

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

    public function conferenceBySlug(string $slug): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM conferences WHERE slug = :slug LIMIT 1");
        $stmt->execute([':slug' => $slug]);
        $row = $stmt->fetch();
        return $row ?: null;
    }

    public function daysByConference(int $conferenceId): array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM conference_days
            WHERE conference_id = :conference_id
            ORDER BY day_date ASC, sort_order ASC, id ASC
        ");
        $stmt->execute([':conference_id' => $conferenceId]);
        return $stmt->fetchAll();
    }

    public function sessionsByConference(int $conferenceId): array
    {
        $stmt = $this->db->prepare("
            SELECT s.*, d.day_date, d.title_en AS day_title_en, d.title_ar AS day_title_ar,
                   t.name_en AS track_name_en, t.name_ar AS track_name_ar
            FROM conference_sessions s
            INNER JOIN conference_days d ON d.id = s.conference_day_id
            LEFT JOIN tracks t ON t.id = s.track_id
            WHERE s.conference_id = :conference_id
            ORDER BY d.day_date ASC, s.start_time ASC, s.sort_order ASC, s.id ASC
        ");
        $stmt->execute([':conference_id' => $conferenceId]);
        return $stmt->fetchAll();
    }

    public function presentationsBySession(int $sessionId): array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM session_presentations
            WHERE session_id = :session_id
            ORDER BY start_time ASC, sort_order ASC, id ASC
        ");
        $stmt->execute([':session_id' => $sessionId]);
        return $stmt->fetchAll();
    }

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

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

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

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

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

    public function acceptedSubmissions(int $conferenceId): array
    {
        $stmt = $this->db->prepare("
            SELECT id, submission_no, title, title_ar
            FROM submissions
            WHERE conference_id = :conference_id
              AND status IN ('accepted','camera_ready')
            ORDER BY id DESC
        ");
        $stmt->execute([':conference_id' => $conferenceId]);
        return $stmt->fetchAll();
    }

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

    public function createDay(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO conference_days (conference_id, day_date, title_en, title_ar, sort_order, is_active)
            VALUES (:conference_id, :day_date, :title_en, :title_ar, :sort_order, :is_active)
        ");
        $stmt->execute([
            ':conference_id' => $data['conference_id'],
            ':day_date' => $data['day_date'],
            ':title_en' => $data['title_en'] ?: null,
            ':title_ar' => $data['title_ar'] ?: null,
            ':sort_order' => $data['sort_order'] ?? 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function updateDay(int $id, array $data): void
    {
        $stmt = $this->db->prepare("
            UPDATE conference_days
            SET conference_id = :conference_id,
                day_date = :day_date,
                title_en = :title_en,
                title_ar = :title_ar,
                sort_order = :sort_order,
                is_active = :is_active
            WHERE id = :id
        ");
        $stmt->execute([
            ':id' => $id,
            ':conference_id' => $data['conference_id'],
            ':day_date' => $data['day_date'],
            ':title_en' => $data['title_en'] ?: null,
            ':title_ar' => $data['title_ar'] ?: null,
            ':sort_order' => $data['sort_order'] ?? 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
    }

    public function createSession(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO conference_sessions (
                conference_id, conference_day_id, track_id, title_en, title_ar,
                hall_en, hall_ar, chair_name_en, chair_name_ar, start_time, end_time,
                session_type, sort_order, is_active
            ) VALUES (
                :conference_id, :conference_day_id, :track_id, :title_en, :title_ar,
                :hall_en, :hall_ar, :chair_name_en, :chair_name_ar, :start_time, :end_time,
                :session_type, :sort_order, :is_active
            )
        ");
        $stmt->execute([
            ':conference_id' => $data['conference_id'],
            ':conference_day_id' => $data['conference_day_id'],
            ':track_id' => $data['track_id'] ?: null,
            ':title_en' => $data['title_en'],
            ':title_ar' => $data['title_ar'] ?: null,
            ':hall_en' => $data['hall_en'] ?: null,
            ':hall_ar' => $data['hall_ar'] ?: null,
            ':chair_name_en' => $data['chair_name_en'] ?: null,
            ':chair_name_ar' => $data['chair_name_ar'] ?: null,
            ':start_time' => $data['start_time'] ?: null,
            ':end_time' => $data['end_time'] ?: null,
            ':session_type' => $data['session_type'] ?: 'technical',
            ':sort_order' => $data['sort_order'] ?? 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function updateSession(int $id, array $data): void
    {
        $stmt = $this->db->prepare("
            UPDATE conference_sessions
            SET conference_id = :conference_id,
                conference_day_id = :conference_day_id,
                track_id = :track_id,
                title_en = :title_en,
                title_ar = :title_ar,
                hall_en = :hall_en,
                hall_ar = :hall_ar,
                chair_name_en = :chair_name_en,
                chair_name_ar = :chair_name_ar,
                start_time = :start_time,
                end_time = :end_time,
                session_type = :session_type,
                sort_order = :sort_order,
                is_active = :is_active
            WHERE id = :id
        ");
        $stmt->execute([
            ':id' => $id,
            ':conference_id' => $data['conference_id'],
            ':conference_day_id' => $data['conference_day_id'],
            ':track_id' => $data['track_id'] ?: null,
            ':title_en' => $data['title_en'],
            ':title_ar' => $data['title_ar'] ?: null,
            ':hall_en' => $data['hall_en'] ?: null,
            ':hall_ar' => $data['hall_ar'] ?: null,
            ':chair_name_en' => $data['chair_name_en'] ?: null,
            ':chair_name_ar' => $data['chair_name_ar'] ?: null,
            ':start_time' => $data['start_time'] ?: null,
            ':end_time' => $data['end_time'] ?: null,
            ':session_type' => $data['session_type'] ?: 'technical',
            ':sort_order' => $data['sort_order'] ?? 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
    }

    public function createPresentation(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO session_presentations (
                session_id, submission_id, title_en, title_ar, presenter_name_en,
                presenter_name_ar, presenter_email, start_time, end_time, sort_order, is_active
            ) VALUES (
                :session_id, :submission_id, :title_en, :title_ar, :presenter_name_en,
                :presenter_name_ar, :presenter_email, :start_time, :end_time, :sort_order, :is_active
            )
        ");
        $stmt->execute([
            ':session_id' => $data['session_id'],
            ':submission_id' => $data['submission_id'] ?: null,
            ':title_en' => $data['title_en'] ?: null,
            ':title_ar' => $data['title_ar'] ?: null,
            ':presenter_name_en' => $data['presenter_name_en'] ?: null,
            ':presenter_name_ar' => $data['presenter_name_ar'] ?: null,
            ':presenter_email' => $data['presenter_email'] ?: null,
            ':start_time' => $data['start_time'] ?: null,
            ':end_time' => $data['end_time'] ?: null,
            ':sort_order' => $data['sort_order'] ?? 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function updatePresentation(int $id, array $data): void
    {
        $stmt = $this->db->prepare("
            UPDATE session_presentations
            SET session_id = :session_id,
                submission_id = :submission_id,
                title_en = :title_en,
                title_ar = :title_ar,
                presenter_name_en = :presenter_name_en,
                presenter_name_ar = :presenter_name_ar,
                presenter_email = :presenter_email,
                start_time = :start_time,
                end_time = :end_time,
                sort_order = :sort_order,
                is_active = :is_active
            WHERE id = :id
        ");
        $stmt->execute([
            ':id' => $id,
            ':session_id' => $data['session_id'],
            ':submission_id' => $data['submission_id'] ?: null,
            ':title_en' => $data['title_en'] ?: null,
            ':title_ar' => $data['title_ar'] ?: null,
            ':presenter_name_en' => $data['presenter_name_en'] ?: null,
            ':presenter_name_ar' => $data['presenter_name_ar'] ?: null,
            ':presenter_email' => $data['presenter_email'] ?: null,
            ':start_time' => $data['start_time'] ?: null,
            ':end_time' => $data['end_time'] ?: null,
            ':sort_order' => $data['sort_order'] ?? 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
    }

    public function createBadge(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO conference_badges (
                conference_id, person_name_en, person_name_ar, title_en, title_ar,
                affiliation_en, affiliation_ar, badge_role, qr_text, is_active
            ) VALUES (
                :conference_id, :person_name_en, :person_name_ar, :title_en, :title_ar,
                :affiliation_en, :affiliation_ar, :badge_role, :qr_text, :is_active
            )
        ");
        $stmt->execute([
            ':conference_id' => $data['conference_id'],
            ':person_name_en' => $data['person_name_en'],
            ':person_name_ar' => $data['person_name_ar'] ?: null,
            ':title_en' => $data['title_en'] ?: null,
            ':title_ar' => $data['title_ar'] ?: null,
            ':affiliation_en' => $data['affiliation_en'] ?: null,
            ':affiliation_ar' => $data['affiliation_ar'] ?: null,
            ':badge_role' => $data['badge_role'] ?: 'attendee',
            ':qr_text' => $data['qr_text'] ?: null,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function updateBadge(int $id, array $data): void
    {
        $stmt = $this->db->prepare("
            UPDATE conference_badges
            SET conference_id = :conference_id,
                person_name_en = :person_name_en,
                person_name_ar = :person_name_ar,
                title_en = :title_en,
                title_ar = :title_ar,
                affiliation_en = :affiliation_en,
                affiliation_ar = :affiliation_ar,
                badge_role = :badge_role,
                qr_text = :qr_text,
                is_active = :is_active
            WHERE id = :id
        ");
        $stmt->execute([
            ':id' => $id,
            ':conference_id' => $data['conference_id'],
            ':person_name_en' => $data['person_name_en'],
            ':person_name_ar' => $data['person_name_ar'] ?: null,
            ':title_en' => $data['title_en'] ?: null,
            ':title_ar' => $data['title_ar'] ?: null,
            ':affiliation_en' => $data['affiliation_en'] ?: null,
            ':affiliation_ar' => $data['affiliation_ar'] ?: null,
            ':badge_role' => $data['badge_role'] ?: 'attendee',
            ':qr_text' => $data['qr_text'] ?: null,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
    }
}
