<?php
/**
 * One-time installation script
 * Run once to create database, tables, and default admin user.
 * DELETE this folder after installation for security.
 */

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

// Connect without database first
// Use 127.0.0.1 instead of localhost to avoid MySQL socket path issues on macOS
$db_host = '127.0.0.1';
$db_user = 'root';
$db_pass = 'mysql';  // AMPPS default; use '' if your MySQL root has no password

try {
    $pdo = new PDO("mysql:host=$db_host;charset=utf8mb4", $db_user, $db_pass, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    ]);
} catch (PDOException $e) {
    die('Cannot connect to MySQL: ' . $e->getMessage());
}

// Create database
$pdo->exec('CREATE DATABASE IF NOT EXISTS nazareth CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci');
$pdo->exec('USE nazareth');

// Users table
$pdo->exec("
    CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        password VARCHAR(255) NOT NULL,
        full_name VARCHAR(100),
        role VARCHAR(20) DEFAULT 'admin',
        must_change_password TINYINT(1) DEFAULT 0,
        active TINYINT(1) DEFAULT 1,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
");

// Households table (Phase 3 - before members for FK order)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS households (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(200) NOT NULL,
        address TEXT,
        head_member_id INT NULL,
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
");

// Members table (Phase 2)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS members (
        id INT AUTO_INCREMENT PRIMARY KEY,
        membership_number VARCHAR(50) NULL UNIQUE,
        title VARCHAR(20),
        first_name VARCHAR(100) NOT NULL,
        middle_name VARCHAR(100),
        last_name VARCHAR(100) NOT NULL,
        gender VARCHAR(20),
        date_of_birth DATE,
        phone VARCHAR(50),
        email VARCHAR(100),
        marital_status VARCHAR(30),
        occupation VARCHAR(100),
        status VARCHAR(50) DEFAULT 'communicant',
        join_date DATE,
        baptism_date DATE,
        address TEXT,
        ghana_post_address VARCHAR(255) NULL,
        map_link VARCHAR(500) NULL,
        emergency_contact VARCHAR(100),
        emergency_phone VARCHAR(50),
        notes TEXT,
        is_aged TINYINT(1) DEFAULT 0,
        is_invalid TINYINT(1) DEFAULT 0,
        household_id INT NULL,
        photo VARCHAR(255) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    )
");

// Membership number prefixes (e.g. YMC = Youth Male Communicant)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS membership_number_prefixes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        code VARCHAR(20) NOT NULL UNIQUE,
        description VARCHAR(255) NULL,
        display_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
");

// Funds table (Phase 4 + Phase 5 targets; parent_fund_id for roll-up e.g. day offerings → Harvest)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS funds (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        is_personal TINYINT(1) DEFAULT 0,
        target_amount DECIMAL(12,2) NULL,
        target_year SMALLINT NULL,
        parent_fund_id INT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (parent_fund_id) REFERENCES funds(id) ON DELETE SET NULL
    )
");

// Transactions table (for Phase 4)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS transactions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        fund_id INT NOT NULL,
        member_id INT NULL,
        amount DECIMAL(12,2) NOT NULL,
        transaction_date DATE NOT NULL,
        note TEXT,
        created_by INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (fund_id) REFERENCES funds(id),
        FOREIGN KEY (member_id) REFERENCES members(id),
        FOREIGN KEY (created_by) REFERENCES users(id)
    )
");

// Pledges table (member/fund/year)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS pledges (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id INT NOT NULL,
        fund_id INT NOT NULL,
        amount DECIMAL(12,2) NOT NULL,
        pledge_year SMALLINT NOT NULL,
        notes TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
        FOREIGN KEY (fund_id) REFERENCES funds(id),
        INDEX idx_pledge_year (pledge_year),
        INDEX idx_pledge_member (member_id)
    )
");

// Groups table (for Phase 5) - backticks: groups is a MySQL reserved word
$pdo->exec("
    CREATE TABLE IF NOT EXISTS `groups` (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
");

// Group members pivot (for Phase 5)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS group_members (
        id INT AUTO_INCREMENT PRIMARY KEY,
        group_id INT NOT NULL,
        member_id INT NOT NULL,
        role VARCHAR(50),
        joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY (group_id, member_id),
        FOREIGN KEY (group_id) REFERENCES `groups`(id),
        FOREIGN KEY (member_id) REFERENCES members(id)
    )
");

// Fellowship managers: which users manage which generational groups (Gen. Grps)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS fellowship_managers (
        user_id INT NOT NULL,
        fellowship_name VARCHAR(100) NOT NULL,
        PRIMARY KEY (user_id, fellowship_name),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
    )
");

// Fellowship subgroups (e.g. cells within YPG) – does not affect main members table
$pdo->exec("
    CREATE TABLE IF NOT EXISTS fellowship_subgroups (
        id INT AUTO_INCREMENT PRIMARY KEY,
        fellowship_name VARCHAR(100) NOT NULL,
        name VARCHAR(100) NOT NULL,
        display_order INT DEFAULT 0,
        UNIQUE KEY (fellowship_name, name)
    )
");

// Which members are in which subgroup (organizational only; members table unchanged)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS fellowship_subgroup_members (
        subgroup_id INT NOT NULL,
        member_id INT NOT NULL,
        PRIMARY KEY (subgroup_id, member_id),
        FOREIGN KEY (subgroup_id) REFERENCES fellowship_subgroups(id) ON DELETE CASCADE,
        FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE
    )
");

// Ministries table
$pdo->exec("
    CREATE TABLE IF NOT EXISTS ministries (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL UNIQUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
");

// Member ministries pivot (many-to-many)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS member_ministries (
        id INT AUTO_INCREMENT PRIMARY KEY,
        member_id INT NOT NULL,
        ministry_id INT NOT NULL,
        UNIQUE KEY (member_id, ministry_id),
        FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE CASCADE,
        FOREIGN KEY (ministry_id) REFERENCES ministries(id) ON DELETE CASCADE
    )
");

// Event types (categories for events)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS event_types (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        color VARCHAR(7) NULL,
        display_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
");

// Events table (Phase 6: reminders)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS events (
        id INT AUTO_INCREMENT PRIMARY KEY,
        title VARCHAR(200) NOT NULL,
        description TEXT,
        event_date DATE NOT NULL,
        event_time TIME,
        location VARCHAR(255),
        event_type_id INT NULL,
        reminder_enabled TINYINT(1) DEFAULT 0,
        reminder_when ENUM('day_before', 'morning_of') DEFAULT 'day_before',
        reminder_recipient_type ENUM('all', 'groups') DEFAULT 'all',
        reminder_group_ids TEXT NULL,
        reminder_sent_at DATETIME NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (event_type_id) REFERENCES event_types(id) ON DELETE SET NULL
    )
");

// Event attendance (for Phase 6)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS event_attendance (
        id INT AUTO_INCREMENT PRIMARY KEY,
        event_id INT NOT NULL,
        member_id INT NOT NULL,
        attended TINYINT(1) DEFAULT 1,
        notes TEXT,
        UNIQUE KEY (event_id, member_id),
        FOREIGN KEY (event_id) REFERENCES events(id),
        FOREIGN KEY (member_id) REFERENCES members(id)
    )
");

// Seed default event types if none exist (fresh install)
$stmt = $pdo->query('SELECT COUNT(*) FROM event_types');
if ($stmt->fetchColumn() == 0) {
    $defaultTypes = [
        ['Worship Service', '#2563eb', 1],
        ['Bible Study', '#059669', 2],
        ['Meeting', '#7c3aed', 3],
        ['Outreach', '#ea580c', 4],
        ['Conference', '#be185d', 5],
        ['Funeral', '#475569', 6],
        ['Wedding', '#db2777', 7],
        ['Youth', '#0d9488', 8],
        ['Children', '#65a30d', 9],
        ['Prayer', '#4f46e5', 10],
        ['Other', '#64748b', 11],
    ];
    $ins = $pdo->prepare('INSERT INTO event_types (name, color, display_order) VALUES (?, ?, ?)');
    foreach ($defaultTypes as $t) {
        $ins->execute([$t[0], $t[1], $t[2]]);
    }
}

// Create default admin if no users exist
$stmt = $pdo->query('SELECT COUNT(*) FROM users');
if ($stmt->fetchColumn() == 0) {
    $hash = password_hash('admin123', PASSWORD_DEFAULT);
    $pdo->prepare('INSERT INTO users (username, password, full_name, role) VALUES (?, ?, ?, ?)')
        ->execute(['admin', $hash, 'Administrator', 'admin']);
}

// Create default system admin (if doesn't exist)
$stmt = $pdo->prepare('SELECT id FROM users WHERE username = ?');
$stmt->execute(['sysadmin']);
if (!$stmt->fetch()) {
    $sysHash = password_hash('sysadmin123', PASSWORD_DEFAULT);
    try {
        $pdo->prepare('INSERT INTO users (username, password, full_name, role) VALUES (?, ?, ?, ?)')
            ->execute(['sysadmin', $sysHash, 'System Administrator', 'system_admin']);
    } catch (PDOException $e) {
        // Ignore errors
    }
}

// Create default funds if none exist
$stmt = $pdo->query('SELECT COUNT(*) FROM funds');
if ($stmt->fetchColumn() == 0) {
    $pdo->exec("INSERT INTO funds (name, description) VALUES 
        ('General Offering', 'Regular Sunday offerings'),
        ('Building Fund', 'Building and maintenance'),
        ('Missions', 'Missions and outreach')");
}

// Phase 5: ensure fund target columns exist (for existing installs)
try {
    $pdo->query("SELECT target_amount, target_year FROM funds LIMIT 1");
} catch (PDOException $e) {
    if (strpos($e->getMessage(), 'Unknown column') !== false) {
        $pdo->exec("ALTER TABLE funds ADD COLUMN target_amount DECIMAL(12,2) NULL AFTER is_personal");
        $pdo->exec("ALTER TABLE funds ADD COLUMN target_year SMALLINT NULL AFTER target_amount");
    } else {
        throw $e;
    }
}
// Roll-up: ensure parent_fund_id exists (child funds contribute to parent's target)
try {
    $pdo->query("SELECT parent_fund_id FROM funds LIMIT 1");
} catch (PDOException $e) {
    if (strpos($e->getMessage(), 'Unknown column') !== false) {
        $pdo->exec("ALTER TABLE funds ADD COLUMN parent_fund_id INT NULL AFTER target_year");
        try {
            $pdo->exec("ALTER TABLE funds ADD CONSTRAINT fk_funds_parent FOREIGN KEY (parent_fund_id) REFERENCES funds(id) ON DELETE SET NULL");
        } catch (PDOException $fk) {
            // Ignore if FK fails (e.g. duplicate name)
        }
    } else {
        throw $e;
    }
}

// Phase 6: ensure event reminder columns exist (for existing installs)
try {
    $pdo->query("SELECT reminder_enabled FROM events LIMIT 1");
} catch (PDOException $e) {
    if (strpos($e->getMessage(), 'Unknown column') !== false) {
        $pdo->exec("ALTER TABLE events ADD COLUMN reminder_enabled TINYINT(1) DEFAULT 0 AFTER location");
        $pdo->exec("ALTER TABLE events ADD COLUMN reminder_when ENUM('day_before', 'morning_of') DEFAULT 'day_before' AFTER reminder_enabled");
        $pdo->exec("ALTER TABLE events ADD COLUMN reminder_recipient_type ENUM('all', 'groups') DEFAULT 'all' AFTER reminder_when");
        $pdo->exec("ALTER TABLE events ADD COLUMN reminder_group_ids TEXT NULL AFTER reminder_recipient_type");
        $pdo->exec("ALTER TABLE events ADD COLUMN reminder_sent_at DATETIME NULL AFTER reminder_group_ids");
    } else {
        throw $e;
    }
}

// Phase 7: event types (for existing installs)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS event_types (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        color VARCHAR(7) NULL,
        display_order INT DEFAULT 0,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )
");
try {
    $pdo->query("SELECT event_type_id FROM events LIMIT 1");
} catch (PDOException $e) {
    if (strpos($e->getMessage(), 'Unknown column') !== false) {
        $pdo->exec("ALTER TABLE events ADD COLUMN event_type_id INT NULL AFTER location");
        try {
            $pdo->exec("ALTER TABLE events ADD CONSTRAINT fk_events_type FOREIGN KEY (event_type_id) REFERENCES event_types(id) ON DELETE SET NULL");
        } catch (PDOException $fk) {
            // Ignore if FK fails
        }
    } else {
        throw $e;
    }
}
$stmt = $pdo->query('SELECT COUNT(*) FROM event_types');
if ($stmt->fetchColumn() == 0) {
    $defaultTypes = [
        ['Worship Service', '#2563eb', 1],
        ['Bible Study', '#059669', 2],
        ['Meeting', '#7c3aed', 3],
        ['Outreach', '#ea580c', 4],
        ['Conference', '#be185d', 5],
        ['Funeral', '#475569', 6],
        ['Wedding', '#db2777', 7],
        ['Youth', '#0d9488', 8],
        ['Children', '#65a30d', 9],
        ['Prayer', '#4f46e5', 10],
        ['Other', '#64748b', 11],
    ];
    $ins = $pdo->prepare('INSERT INTO event_types (name, color, display_order) VALUES (?, ?, ?)');
    foreach ($defaultTypes as $t) {
        $ins->execute([$t[0], $t[1], $t[2]]);
    }
}

// Create default ministries if none exist
$stmt = $pdo->query('SELECT COUNT(*) FROM ministries');
if ($stmt->fetchColumn() == 0) {
    $defaultMinistries = ['Church Choir', 'Singing Band', 'Praise Showers', 'Children\'s Ministry', 'Youth Ministry', 'Ushering'];
    $stmt = $pdo->prepare('INSERT INTO ministries (name) VALUES (?)');
    foreach ($defaultMinistries as $m) $stmt->execute([$m]);
}

// Settings table (SMTP, SMS credentials - admin UI)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS settings (
        name VARCHAR(100) PRIMARY KEY,
        value TEXT
    )
");

// Message templates (reusable SMS/email for events, reminders, announcements)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS message_templates (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL,
        type ENUM('sms','email') NOT NULL DEFAULT 'email',
        subject VARCHAR(255) NULL,
        body TEXT NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        UNIQUE KEY (name)
    )
");

// Push subscriptions (member app Web Push – endpoint + keys per device)
$pdo->exec("
    CREATE TABLE IF NOT EXISTS push_subscriptions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        endpoint VARCHAR(512) NOT NULL,
        p256dh VARCHAR(255) NOT NULL,
        auth VARCHAR(255) NOT NULL,
        user_agent VARCHAR(500) NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        UNIQUE KEY uk_push_endpoint (endpoint(500))
    )
");

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Installation Complete - Nazareth</title>
    <style>
        body { font-family: sans-serif; max-width: 500px; margin: 3rem auto; padding: 2rem; }
        .success { color: #276749; }
        .info { background: #ebf8ff; padding: 1rem; border-radius: 4px; margin: 1rem 0; }
        a { color: #2b6cb0; }
    </style>
</head>
<body>
    <h1 class="success">Installation Complete</h1>
    <p>Database and tables have been created.</p>
    <div class="info">
        <strong>Default login:</strong><br>
        Username: <code>admin</code><br>
        Password: <code>admin123</code><br>
        <em>Please change the password after first login.</em>
    </div>
    <p><a href="/nazareth/login">Go to Login</a></p>
    <p style="color: #718096; font-size: 0.9rem;">For security, delete the <code>install/</code> folder after installation.</p>
</body>
</html>
