<?php
/**
 * Database Setup Script
 * Creates all required tables and adds any missing columns to existing tables.
 *
 * Run via CLI:     php setup_db.php
 * Run via browser: https://yourdomain.com/setup_db.php?key=YOUR_SETUP_KEY
 *
 * IMPORTANT: Set SETUP_KEY in your .env, then delete or block this file after first run.
 */

$isCli = (php_sapi_name() === 'cli');

// Browser access guard
if (!$isCli) {
    $config = include __DIR__ . '/config.php';
    $expectedKey = $config['setup_key'] ?? null;
    $providedKey = $_GET['key'] ?? '';
    if (!$expectedKey || $providedKey !== $expectedKey) {
        http_response_code(403);
        die('403 Forbidden — provide ?key=YOUR_SETUP_KEY or run from CLI.');
    }
    echo "<pre style='font-family:monospace;font-size:14px;padding:20px;'>";
}

$config = include __DIR__ . '/config.php';

// ─── Connect ────────────────────────────────────────────────────────────────
try {
    $pdo = new PDO(
        "mysql:host={$config['db_host']};dbname={$config['db_name']};charset=utf8mb4",
        $config['db_user'],
        $config['db_pass'],
        [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]
    );
    ok("Connected to database '{$config['db_name']}'");
} catch (PDOException $e) {
    fail("DB connection failed: " . $e->getMessage());
    exit(1);
}

// ─── Table definitions ──────────────────────────────────────────────────────
$tables = [

    'users' => "
        CREATE TABLE IF NOT EXISTS users (
            chat_id             BIGINT        NOT NULL,
            first_name          VARCHAR(100)  DEFAULT NULL,
            weight              DECIMAL(5,2)  DEFAULT NULL,
            height              DECIMAL(5,2)  DEFAULT NULL,
            age                 INT           DEFAULT NULL,
            gender              VARCHAR(10)   DEFAULT NULL,
            goal                VARCHAR(50)   DEFAULT NULL,
            diet                VARCHAR(50)   DEFAULT NULL,
            budget              INT           DEFAULT NULL,
            wake_time           VARCHAR(20)   DEFAULT NULL,
            bed_time            VARCHAR(20)   DEFAULT NULL,
            workout             VARCHAR(20)   DEFAULT NULL,
            notes               TEXT          DEFAULT NULL,
            bmi                 DECIMAL(5,2)  DEFAULT NULL,
            bmr                 INT           DEFAULT NULL,
            maintenance_calories INT          DEFAULT NULL,
            credits             INT           NOT NULL DEFAULT 20,
            created_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
            updated_at          TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (chat_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'diet_plans' => "
        CREATE TABLE IF NOT EXISTS diet_plans (
            id         INT       AUTO_INCREMENT PRIMARY KEY,
            chat_id    BIGINT    NOT NULL,
            plan       LONGTEXT  NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_diet_chat (chat_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'bmi_history' => "
        CREATE TABLE IF NOT EXISTS bmi_history (
            id         INT          AUTO_INCREMENT PRIMARY KEY,
            chat_id    BIGINT       NOT NULL,
            weight     DECIMAL(5,2) DEFAULT NULL,
            height     DECIMAL(5,2) DEFAULT NULL,
            bmi        DECIMAL(5,2) DEFAULT NULL,
            bmr        INT          DEFAULT NULL,
            tdee       INT          DEFAULT NULL,
            goal       VARCHAR(50)  DEFAULT NULL,
            created_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_bmi_chat (chat_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'progress' => "
        CREATE TABLE IF NOT EXISTS progress (
            id        INT          AUTO_INCREMENT PRIMARY KEY,
            chat_id   BIGINT       NOT NULL,
            weight    DECIMAL(5,2) NOT NULL,
            note      VARCHAR(255) DEFAULT NULL,
            logged_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_progress_chat (chat_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'workout_plans' => "
        CREATE TABLE IF NOT EXISTS workout_plans (
            id         INT       AUTO_INCREMENT PRIMARY KEY,
            chat_id    BIGINT    NOT NULL,
            plan       LONGTEXT  NOT NULL,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_workout_chat (chat_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'reminder_log' => "
        CREATE TABLE IF NOT EXISTS reminder_log (
            id           INT          AUTO_INCREMENT PRIMARY KEY,
            chat_id      BIGINT       NOT NULL,
            reminder_key VARCHAR(100) NOT NULL,
            sent_at      TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_reminder_chat_sent (chat_id, sent_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'credit_transactions' => "
        CREATE TABLE IF NOT EXISTS credit_transactions (
            id          INT    AUTO_INCREMENT PRIMARY KEY,
            chat_id     BIGINT NOT NULL,
            amount      INT    NOT NULL,
            type        ENUM('signup_bonus','plan_generation','manual_topup','admin_grant') NOT NULL,
            description VARCHAR(255) DEFAULT NULL,
            created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_credit_chat (chat_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'settings' => "
        CREATE TABLE IF NOT EXISTS settings (
            `key`      VARCHAR(100) NOT NULL,
            `value`    VARCHAR(255) NOT NULL,
            updated_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (`key`)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'subscriptions' => "
        CREATE TABLE IF NOT EXISTS subscriptions (
            id         INT    AUTO_INCREMENT PRIMARY KEY,
            chat_id    BIGINT NOT NULL,
            plan_type  ENUM('monthly','3month','6month','12month') NOT NULL,
            started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP NOT NULL,
            status     ENUM('active','expired','cancelled') DEFAULT 'active',
            granted_by ENUM('admin','payment') DEFAULT 'admin',
            INDEX idx_sub_chat (chat_id),
            INDEX idx_sub_expires (expires_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'streaks' => "
        CREATE TABLE IF NOT EXISTS streaks (
            chat_id        BIGINT NOT NULL PRIMARY KEY,
            current_streak INT    NOT NULL DEFAULT 0,
            longest_streak INT    NOT NULL DEFAULT 0,
            last_log_date  DATE   DEFAULT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'referrals' => "
        CREATE TABLE IF NOT EXISTS referrals (
            id               INT    AUTO_INCREMENT PRIMARY KEY,
            referrer_chat_id BIGINT NOT NULL,
            referred_chat_id BIGINT NOT NULL UNIQUE,
            credits_awarded  INT    NOT NULL DEFAULT 0,
            created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_ref_referrer (referrer_chat_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'workout_log' => "
        CREATE TABLE IF NOT EXISTS workout_log (
            id            INT          AUTO_INCREMENT PRIMARY KEY,
            chat_id       BIGINT       NOT NULL,
            day_label     VARCHAR(50)  DEFAULT NULL,
            exercise_name VARCHAR(150) NOT NULL,
            sets_done     INT          DEFAULT NULL,
            reps_done     VARCHAR(50)  DEFAULT NULL,
            weight_kg     DECIMAL(6,2) DEFAULT NULL,
            logged_at     TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_wlog_chat_date (chat_id, logged_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'water_log' => "
        CREATE TABLE IF NOT EXISTS water_log (
            id        INT          AUTO_INCREMENT PRIMARY KEY,
            chat_id   BIGINT       NOT NULL,
            litres    DECIMAL(4,2) NOT NULL,
            logged_at TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_water_chat_date (chat_id, logged_at)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

    'weekly_reports' => "
        CREATE TABLE IF NOT EXISTS weekly_reports (
            id         INT    AUTO_INCREMENT PRIMARY KEY,
            chat_id    BIGINT NOT NULL,
            week_start DATE   NOT NULL,
            sent_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_report_chat (chat_id)
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
    ",

];

// ─── Columns that must exist on already-existing tables ─────────────────────
// Format: [ table, column, ALTER TABLE SQL fragment ]
$requiredColumns = [
    ['users', 'credits',              "ADD COLUMN credits INT NOT NULL DEFAULT 20 AFTER maintenance_calories"],
    ['users', 'created_at',           "ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP"],
    ['users', 'updated_at',           "ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"],
    ['users', 'bmi',                  "ADD COLUMN bmi DECIMAL(5,2) DEFAULT NULL"],
    ['users', 'bmr',                  "ADD COLUMN bmr INT DEFAULT NULL"],
    ['users', 'maintenance_calories', "ADD COLUMN maintenance_calories INT DEFAULT NULL"],
];

// ─── Run table creation ──────────────────────────────────────────────────────
line();
out("=== Creating tables ===");
line();

foreach ($tables as $name => $sql) {
    try {
        $pdo->exec(trim($sql));
        ok("Table '{$name}' — OK");
    } catch (PDOException $e) {
        fail("Table '{$name}' — " . $e->getMessage());
    }
}

// ─── Run column migrations ──────────────────────────────────────────────────
line();
out("=== Checking columns ===");
line();

foreach ($requiredColumns as [$table, $column, $alter]) {
    if (!columnExists($pdo, $table, $column)) {
        try {
            $pdo->exec("ALTER TABLE `{$table}` {$alter}");
            ok("Added column '{$column}' to '{$table}'");
        } catch (PDOException $e) {
            fail("Could not add '{$column}' to '{$table}': " . $e->getMessage());
        }
    } else {
        out("  ✔ Column '{$table}.{$column}' already exists");
    }
}

// ─── Seed default settings ──────────────────────────────────────────────────
line();
out("=== Seeding default settings ===");
line();

$defaults = [
    'diet_plan_cost'      => '10',
    'workout_plan_cost'   => '20',
    'rupee_per_credit'    => '1',
    'signup_credits'      => '20',
    'referral_credits'    => '10',
    'report_cost'         => '20',
    'sub_price_monthly'   => '99',
    'sub_price_3month'    => '249',
    'sub_price_6month'    => '449',
    'sub_price_12month'   => '799',
];

foreach ($defaults as $key => $value) {
    try {
        $pdo->prepare("INSERT INTO settings (`key`, `value`) VALUES (?, ?) ON DUPLICATE KEY UPDATE `key` = `key`")
            ->execute([$key, $value]);
        out("  ✔ Setting '{$key}' = '{$value}' (preserved if already set)");
    } catch (PDOException $e) {
        fail("Setting '{$key}': " . $e->getMessage());
    }
}

// ─── Summary ────────────────────────────────────────────────────────────────
line();
ok("Setup complete. All tables, columns, and default settings are in place.");
out("You can now delete or block setup_db.php from public access.");
line();

if (!$isCli) echo "</pre>";

// ─── Helpers ────────────────────────────────────────────────────────────────
function columnExists(PDO $pdo, string $table, string $column): bool {
    $stmt = $pdo->prepare("
        SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ?
    ");
    $stmt->execute([$table, $column]);
    return (bool)$stmt->fetchColumn();
}

function ok(string $msg)   { out("  ✅ " . $msg); }
function fail(string $msg) { out("  ❌ " . $msg); }
function line()            { out(str_repeat("─", 55)); }
function out(string $msg)  {
    echo (php_sapi_name() === 'cli') ? $msg . "\n" : htmlspecialchars($msg) . "\n";
}
