const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
// Try loading .env from current directory (Production) first
let dotEnvPath = path.resolve(__dirname, '.env');
if (!fs.existsSync(dotEnvPath)) {
    // Fallback to dev path
    dotEnvPath = path.resolve(__dirname, '../../.env');
}
require('dotenv').config({ path: dotEnvPath });

let schemaPath = path.resolve(__dirname, 'clean_schema.sql');
if (!fs.existsSync(schemaPath)) {
    schemaPath = path.resolve(__dirname, '../../clean_schema.sql');
}
const SCHEMA_FILE = schemaPath;

async function upgradeDatabase() {
    console.log('🚀 Starting Database Upgrade Script...');

    // 1. Check for .env file
    if (!process.env.DB_HOST || !process.env.DB_USER || !process.env.DB_NAME) {
        console.error(`❌ ERROR: Missing database credentials in .env file (looked at: ${dotEnvPath}).`);
        console.error('   Please ensure a valid .env file exists with DB_HOST, DB_USER, DB_NAME.');
        process.exit(1);
    }

    let connection;
    try {
        console.log(`🔌 Connecting to database: ${process.env.DB_NAME}...`);
        connection = await mysql.createConnection({
            host: process.env.DB_HOST,
            user: process.env.DB_USER,
            password: process.env.DB_PASSWORD,
            database: process.env.DB_NAME,
            multipleStatements: true // Required for running SQL scripts
        });
        console.log('✅ Connected successfully.');

        // 2. Check if tables exist
        const [tables] = await connection.query("SHOW TABLES LIKE 'users'");

        if (tables.length === 0) {
            console.log('✨ Data empty (users table missing). Initializing fresh schema...');

            if (!fs.existsSync(SCHEMA_FILE)) {
                console.error(`❌ ERROR: Schema file not found at ${SCHEMA_FILE}`);
                process.exit(1);
            }

            const sql = fs.readFileSync(SCHEMA_FILE, 'utf8');
            await connection.query(sql);
            console.log('✅ Fresh schema imported successfully.');

        } else {
            console.log('📦 Database exists. Checking for updates/migrations...');

            // --- MIGRATION LOGIC (Add future migrations here) ---

            // Example: Ensure 'badges' column exists in 'users' table (Common update)
            const [columns] = await connection.query("SHOW COLUMNS FROM users LIKE 'badges'");
            if (columns.length === 0) {
                console.log('🔸 Missing column "badges" in users. Adding...');
                await connection.query("ALTER TABLE users ADD COLUMN badges longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`badges`))");
                console.log('✅ Column added.');
            }

            // Ensure 'current_mission' column exists
            const [missionCol] = await connection.query("SHOW COLUMNS FROM users LIKE 'current_mission'");
            if (missionCol.length === 0) {
                console.log('🔸 Missing column "current_mission" in users. Adding...');
                await connection.query("ALTER TABLE users ADD COLUMN current_mission INT DEFAULT 1");
                console.log('✅ Column added.');
            }

            // Ensure 'training_completed' column exists
            const [trainingCol] = await connection.query("SHOW COLUMNS FROM users LIKE 'training_completed'");
            if (trainingCol.length === 0) {
                console.log('🔸 Missing column "training_completed" in users. Adding...');
                await connection.query("ALTER TABLE users ADD COLUMN training_completed BOOLEAN DEFAULT FALSE");
                console.log('✅ Column added.');
            }

            // Example: Ensure 'subscription_end_date' column exists
            const [subEnd] = await connection.query("SHOW COLUMNS FROM users LIKE 'subscription_end_date'");
            if (subEnd.length === 0) {
                console.log('🔸 Missing column "subscription_end_date" in users. Adding...');
                await connection.query("ALTER TABLE users ADD COLUMN subscription_end_date datetime DEFAULT NULL");
                console.log('✅ Column added.');
            }

            // Example: Ensure 'alert_grades' table exists (if added later)
            const [gradeTable] = await connection.query("SHOW TABLES LIKE 'alert_grades'");
            if (gradeTable.length === 0) {
                console.log('🔸 Missing table "alert_grades". Creating...');
                await connection.query(`
                    CREATE TABLE alert_grades (
                      id int(11) NOT NULL AUTO_INCREMENT,
                      alert_id int(11) NOT NULL,
                      user_id int(11) NOT NULL,
                      grade int(11) DEFAULT NULL,
                      feedback text DEFAULT NULL,
                      graded_by int(11) DEFAULT NULL,
                      created_at timestamp NOT NULL DEFAULT current_timestamp(),
                      updated_at timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
                      PRIMARY KEY (id)
                    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;
                `);
                console.log('✅ Table created.');
            }

            // Ensure 'feedbacks' table exists
            const [feedbackTable] = await connection.query("SHOW TABLES LIKE 'feedbacks'");
            if (feedbackTable.length === 0) {
                console.log('🔸 Missing table "feedbacks". Creating...');
                await connection.query(`
                    CREATE TABLE IF NOT EXISTS feedbacks (
                      id INT AUTO_INCREMENT PRIMARY KEY,
                      user_id INT,
                      grade INT,
                      feedback TEXT,
                      created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                      updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                    );
                `);
                console.log('✅ Table created.');
            }

            // Ensure 'difficulty' column exists in alerts table
            const [difficultyCol] = await connection.query("SHOW COLUMNS FROM alerts LIKE 'difficulty'");
            if (difficultyCol.length === 0) {
                console.log('🔸 Missing column "difficulty" in alerts. Adding...');
                await connection.query("ALTER TABLE alerts ADD COLUMN difficulty VARCHAR(50) DEFAULT 'Beginner'");
                console.log('✅ Column added.');
            }

            // Ensure 'session_token' column exists in users table (for single-device login)
            const [sessionCol] = await connection.query("SHOW COLUMNS FROM users LIKE 'session_token'");
            if (sessionCol.length === 0) {
                console.log('🔸 Missing column "session_token" in users. Adding...');
                await connection.query("ALTER TABLE users ADD COLUMN session_token VARCHAR(255) DEFAULT NULL");
                console.log('✅ Column added.');
            }

            // Ensure 'is_archived' column exists in alerts table
            const [archivedCol] = await connection.query("SHOW COLUMNS FROM alerts LIKE 'is_archived'");
            if (archivedCol.length === 0) {
                console.log('🔸 Missing column "is_archived" in alerts. Adding...');
                await connection.query("ALTER TABLE alerts ADD COLUMN is_archived BOOLEAN DEFAULT 0");
                console.log('✅ Column added.');
            }

            // Ensure 'verification_reminder_sent' column exists in users table
            const [reminderCol] = await connection.query("SHOW COLUMNS FROM users LIKE 'verification_reminder_sent'");
            if (reminderCol.length === 0) {
                console.log('🔸 Missing column "verification_reminder_sent" in users. Adding...');
                await connection.query("ALTER TABLE users ADD COLUMN verification_reminder_sent BOOLEAN DEFAULT 0");
                console.log('✅ Column added.');
            }

            // Ensure 'is_subscribed_newsletter' column exists in users table
            const [newsletterCol] = await connection.query("SHOW COLUMNS FROM users LIKE 'is_subscribed_newsletter'");
            if (newsletterCol.length === 0) {
                console.log('🔸 Missing column "is_subscribed_newsletter" in users. Adding...');
                await connection.query("ALTER TABLE users ADD COLUMN is_subscribed_newsletter BOOLEAN DEFAULT 1");
                console.log('✅ Column added.');
            }

            // Sync System Settings (Upsert defaults)
            console.log('🔄 Syncing system settings...');
            // We can read specific keys from the schema or defined defaults here
            // For now, we assume settings exist or admin adds them. 
            // Better approach: Check specific keys if critical.

            // Ensure 'prerequisite_path_id' column exists in learning_paths table
            const [prereqCol] = await connection.query("SHOW COLUMNS FROM learning_paths LIKE 'prerequisite_path_id'");
            if (prereqCol.length === 0) {
                console.log('🔸 Missing column "prerequisite_path_id" in learning_paths. Adding...');
                await connection.query("ALTER TABLE learning_paths ADD COLUMN prerequisite_path_id INT DEFAULT NULL");
                console.log('✅ Column added.');
            }

            console.log('✅ Database is up to date.');
        }

        await connection.end();
        console.log('🎉 Upgrade process completed.');
        process.exit(0);

    } catch (error) {
        console.error('❌ FATAL ERROR during upgrade:', error);
        if (connection) await connection.end();
        process.exit(1);
    }
}

upgradeDatabase();
