<?php
/**
 * Upgrade: create service_types and attendance_records tables.
 */
require_once __DIR__ . '/../config/database.php';

// Drop old member-based tables if they exist
$pdo->exec("DROP TABLE IF EXISTS service_attendance");
$pdo->exec("DROP TABLE IF EXISTS service_sessions");

$sqls = [
    'service_types' => "CREATE TABLE IF NOT EXISTS service_types (
        id          INT AUTO_INCREMENT PRIMARY KEY,
        name        VARCHAR(100) NOT NULL UNIQUE,
        is_active   TINYINT(1) DEFAULT 1,
        created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )",
    'attendance_records' => "CREATE TABLE IF NOT EXISTS attendance_records (
        id              INT AUTO_INCREMENT PRIMARY KEY,
        service_type_id INT NOT NULL,
        date            DATE NOT NULL,
        male_count      INT NOT NULL DEFAULT 0,
        female_count    INT NOT NULL DEFAULT 0,
        children_count  INT NOT NULL DEFAULT 0,
        visitors_count  INT NOT NULL DEFAULT 0,
        notes           TEXT NULL,
        created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uq_service_date (service_type_id, date),
        FOREIGN KEY (service_type_id) REFERENCES service_types(id) ON DELETE CASCADE,
        INDEX (date)
    )",
];

foreach ($sqls as $table => $sql) {
    try {
        $pdo->exec($sql);
        echo "Created: $table\n";
    } catch (PDOException $e) {
        echo "Error ($table): " . $e->getMessage() . "\n";
    }
}

// Seed default service types
$defaults = ['Sunday Service', 'Mid-Week Service', 'Prayer Meeting', 'Special Service'];
$ins = $pdo->prepare("INSERT IGNORE INTO service_types (name) VALUES (?)");
foreach ($defaults as $name) {
    $ins->execute([$name]);
    echo "Seeded: $name\n";
}

echo "\nAttendance upgrade complete.";
