<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

final class ConferenceSessionItemModel extends Model
{
    public function bySession(int $sessionId): array
    {
        $stmt = $this->db->prepare("
            SELECT i.*, s.submission_no
            FROM conference_session_items i
            LEFT JOIN submissions s ON s.id = i.submission_id
            WHERE i.session_id = :session_id
            ORDER BY i.start_time ASC, i.sort_order ASC, i.id ASC
        ");
        $stmt->execute([':session_id' => $sessionId]);
        return $stmt->fetchAll();
    }

    public function byConference(int $conferenceId): array
    {
        $stmt = $this->db->prepare("
            SELECT i.*, cs.title_en AS session_title_en, cs.title_ar AS session_title_ar
            FROM conference_session_items i
            INNER JOIN conference_sessions cs ON cs.id = i.session_id
            WHERE cs.conference_id = :conference_id
            ORDER BY cs.id DESC, i.sort_order ASC, i.id ASC
        ");
        $stmt->execute([':conference_id' => $conferenceId]);
        return $stmt->fetchAll();
    }

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

    public function create(array $data): int
    {
        $stmt = $this->db->prepare("
            INSERT INTO conference_session_items (
                session_id, submission_id, title_en, title_ar, presenter_name_en, presenter_name_ar,
                presenter_affiliation_en, presenter_affiliation_ar, start_time, end_time, sort_order
            ) VALUES (
                :session_id, :submission_id, :title_en, :title_ar, :presenter_name_en, :presenter_name_ar,
                :presenter_affiliation_en, :presenter_affiliation_ar, :start_time, :end_time, :sort_order
            )
        ");
        $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_affiliation_en' => $data['presenter_affiliation_en'] ?: null,
            ':presenter_affiliation_ar' => $data['presenter_affiliation_ar'] ?: null,
            ':start_time' => $data['start_time'] ?: null,
            ':end_time' => $data['end_time'] ?: null,
            ':sort_order' => $data['sort_order'] ?? 0,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $stmt = $this->db->prepare("
            UPDATE conference_session_items
            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_affiliation_en = :presenter_affiliation_en,
                presenter_affiliation_ar = :presenter_affiliation_ar,
                start_time = :start_time,
                end_time = :end_time,
                sort_order = :sort_order
            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_affiliation_en' => $data['presenter_affiliation_en'] ?: null,
            ':presenter_affiliation_ar' => $data['presenter_affiliation_ar'] ?: null,
            ':start_time' => $data['start_time'] ?: null,
            ':end_time' => $data['end_time'] ?: null,
            ':sort_order' => $data['sort_order'] ?? 0,
        ]);
    }
}
