<?php
// db_schema.php - Database creation schema for Team Mania Pro

function get_schema_queries() {
    return [
        // 1. Users Table
        "CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            password_hash TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            first_name TEXT NOT NULL,
            last_name TEXT NOT NULL,
            role TEXT NOT NULL DEFAULT 'member', -- admin, vorstand, kassenwart, trainer, materialwart, member
            photo_path TEXT,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )",

        // 2. User Permissions Table (for custom overrides)
        "CREATE TABLE IF NOT EXISTS permissions (
            user_id INTEGER PRIMARY KEY,
            can_edit_pages INTEGER DEFAULT 0,
            can_manage_inventory INTEGER DEFAULT 0,
            can_manage_finance INTEGER DEFAULT 0,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )",

        // 3. Chat Rooms
        "CREATE TABLE IF NOT EXISTS chat_rooms (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            created_by INTEGER,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
        )",

        // 3. Chat Messages
        "CREATE TABLE IF NOT EXISTS chat_messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            room_id INTEGER NOT NULL,
            sender_id INTEGER NOT NULL,
            message TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(room_id) REFERENCES chat_rooms(id) ON DELETE CASCADE,
            FOREIGN KEY(sender_id) REFERENCES users(id) ON DELETE CASCADE
        )",

        // 4. Chat Room Invites
        "CREATE TABLE IF NOT EXISTS chat_invites (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            room_id INTEGER NOT NULL,
            token TEXT UNIQUE NOT NULL,
            created_by INTEGER,
            expires_at DATETIME,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(room_id) REFERENCES chat_rooms(id) ON DELETE CASCADE,
            FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
        )",

        // 5. Chat Room Members (for private/invited rooms)
        "CREATE TABLE IF NOT EXISTS chat_room_members (
            room_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            joined_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY(room_id, user_id),
            FOREIGN KEY(room_id) REFERENCES chat_rooms(id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )",

        // 6. Dynamic Pages / News
        "CREATE TABLE IF NOT EXISTS pages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            slug TEXT UNIQUE NOT NULL,
            title TEXT NOT NULL,
            content TEXT NOT NULL,
            image_path TEXT,
            file_path TEXT,
            updated_by INTEGER,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL
        )",

        // 7. Transactions (Kassenwart)
        "CREATE TABLE IF NOT EXISTS transactions (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            type TEXT NOT NULL, -- income, expense
            amount REAL NOT NULL,
            booking_date TEXT NOT NULL, -- YYYY-MM-DD
            description TEXT NOT NULL,
            category TEXT NOT NULL,
            contra_account TEXT, -- e.g. 8400, 4900
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )",

        // 8. Protocols (Vorstand)
        "CREATE TABLE IF NOT EXISTS board_protocols (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            meeting_date TEXT NOT NULL,
            content TEXT NOT NULL,
            author_id INTEGER,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(author_id) REFERENCES users(id) ON DELETE SET NULL
        )",

        // 9. Polls & Votes
        "CREATE TABLE IF NOT EXISTS polls (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            question TEXT NOT NULL,
            options TEXT NOT NULL, -- JSON string
            is_active INTEGER DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )",

        "CREATE TABLE IF NOT EXISTS poll_votes (
            poll_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            selected_option TEXT NOT NULL,
            PRIMARY KEY(poll_id, user_id),
            FOREIGN KEY(poll_id) REFERENCES polls(id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )",

        // 10. Inventory & Clothing
        "CREATE TABLE IF NOT EXISTS inventory (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            category TEXT NOT NULL, -- clothing, equipment
            total_qty INTEGER NOT NULL DEFAULT 0,
            available_qty INTEGER NOT NULL DEFAULT 0,
            sizes TEXT -- Comma separated or JSON sizes
        )",

        // 11. Clothing Requests
        "CREATE TABLE IF NOT EXISTS clothing_requests (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            item_name TEXT NOT NULL,
            size TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'requested', -- requested, ordered, ready, delivered
            payment_status TEXT NOT NULL DEFAULT 'unpaid', -- unpaid, paid, n/a
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )",

        // 12. Notifications
        "CREATE TABLE IF NOT EXISTS notifications (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            message TEXT NOT NULL,
            is_read INTEGER DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )",

        // 13. Private Messages Table (PN)
        "CREATE TABLE IF NOT EXISTS messages (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            sender_id INTEGER,
            receiver_id INTEGER,
            subject TEXT NOT NULL,
            message TEXT NOT NULL,
            is_read INTEGER DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(sender_id) REFERENCES users(id) ON DELETE SET NULL,
            FOREIGN KEY(receiver_id) REFERENCES users(id) ON DELETE SET NULL
        )",

        // 14. Settings Table
        "CREATE TABLE IF NOT EXISTS settings (
            key TEXT PRIMARY KEY,
            value TEXT NOT NULL
        )",

        // 14. Forum Categories (for forum module)
        "CREATE TABLE IF NOT EXISTS forum_categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT
        )",

        // 15. Forum Topics
        "CREATE TABLE IF NOT EXISTS forum_topics (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            category_id INTEGER NOT NULL,
            title TEXT NOT NULL,
            user_id INTEGER NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(category_id) REFERENCES forum_categories(id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )",

        // 16. Forum Posts
        "CREATE TABLE IF NOT EXISTS forum_posts (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            topic_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            message TEXT NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(topic_id) REFERENCES forum_topics(id) ON DELETE CASCADE,
            FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
        )"
    ];
}

function init_database($pdo) {
    $pdo->beginTransaction();
    try {
        $queries = get_schema_queries();
        foreach ($queries as $query) {
            $pdo->exec($query);
        }

        // Insert default settings if they don't exist
        $default_settings = [
            'club_name' => 'SV Musterstadt',
            'logo_path' => '',
            'primary_color' => '#10b981', // Premium emerald green
            'secondary_color' => '#1e293b', // Slate dark background accent
            'forum_license' => '', // Empty means not activated
            'allow_clothing_requests' => '1',
            'menu_visibility' => '{"chat":["admin","vorstand","trainer","materialwart","member"],"forum":["admin","vorstand","trainer","materialwart","member"],"trainer":["admin","trainer"],"kassenwart":["admin","kassenwart"],"vorstand":["admin","vorstand"],"inventory":["admin","vorstand","trainer","materialwart","member"]}',
            'installed' => '0' // Flag to know if installer completed
        ];

        $stmt = $pdo->prepare("INSERT OR IGNORE INTO settings (key, value) VALUES (:key, :value)");
        foreach ($default_settings as $key => $val) {
            $stmt->execute([':key' => $key, ':value' => $val]);
        }

        $pdo->commit();
        return true;
    } catch (Exception $e) {
        $pdo->rollBack();
        throw $e;
    }
}
