<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Model;

final class ConferenceCommitteeModel extends Model
{
    public function byConference(int $conferenceId): array
    {
        $stmt = $this->db->prepare("
            SELECT *
            FROM conference_committees
            WHERE conference_id = :conference_id
            ORDER BY committee_type ASC, sort_order ASC, id ASC
        ");
        $stmt->execute([':conference_id' => $conferenceId]);
        return $stmt->fetchAll();
    }

    public function activeByConference(int $conferenceId, ?string $type = null): array
    {
        $sql = "
            SELECT *
            FROM conference_committees
            WHERE conference_id = :conference_id
              AND is_active = 1
        ";
        $params = [':conference_id' => $conferenceId];

        if ($type !== null && $type !== '') {
            $sql .= " AND committee_type = :committee_type";
            $params[':committee_type'] = $type;
        }

        $sql .= " ORDER BY sort_order ASC, id ASC";

        $stmt = $this->db->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll();
    }

    public function findById(int $id): ?array
    {
        $stmt = $this->db->prepare("SELECT * FROM conference_committees 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_committees (
                conference_id, committee_type, full_name_en, full_name_ar, title_en, title_ar,
                affiliation_en, affiliation_ar, email, image_path, bio_en, bio_ar, sort_order, is_active
            ) VALUES (
                :conference_id, :committee_type, :full_name_en, :full_name_ar, :title_en, :title_ar,
                :affiliation_en, :affiliation_ar, :email, :image_path, :bio_en, :bio_ar, :sort_order, :is_active
            )
        ");
        $stmt->execute([
            ':conference_id' => $data['conference_id'],
            ':committee_type' => $data['committee_type'],
            ':full_name_en' => $data['full_name_en'],
            ':full_name_ar' => $data['full_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,
            ':email' => $data['email'] ?: null,
            ':image_path' => $data['image_path'] ?: null,
            ':bio_en' => $data['bio_en'] ?: null,
            ':bio_ar' => $data['bio_ar'] ?: null,
            ':sort_order' => $data['sort_order'] ?? 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
        return (int) $this->db->lastInsertId();
    }

    public function update(int $id, array $data): void
    {
        $stmt = $this->db->prepare("
            UPDATE conference_committees
            SET conference_id = :conference_id,
                committee_type = :committee_type,
                full_name_en = :full_name_en,
                full_name_ar = :full_name_ar,
                title_en = :title_en,
                title_ar = :title_ar,
                affiliation_en = :affiliation_en,
                affiliation_ar = :affiliation_ar,
                email = :email,
                image_path = :image_path,
                bio_en = :bio_en,
                bio_ar = :bio_ar,
                sort_order = :sort_order,
                is_active = :is_active
            WHERE id = :id
        ");
        $stmt->execute([
            ':id' => $id,
            ':conference_id' => $data['conference_id'],
            ':committee_type' => $data['committee_type'],
            ':full_name_en' => $data['full_name_en'],
            ':full_name_ar' => $data['full_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,
            ':email' => $data['email'] ?: null,
            ':image_path' => $data['image_path'] ?: null,
            ':bio_en' => $data['bio_en'] ?: null,
            ':bio_ar' => $data['bio_ar'] ?: null,
            ':sort_order' => $data['sort_order'] ?? 0,
            ':is_active' => !empty($data['is_active']) ? 1 : 0,
        ]);
    }
}
