const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
    user: process.env.DB_USER,
    host: process.env.DB_HOST,
    database: process.env.DB_NAME,
    password: process.env.DB_PASSWORD,
    port: process.env.DB_PORT,
});

async function migrate() {
    try {
        console.log("Migrating cars table...");
        
        // Ensure car_mulkiya_id is unique and remove duplicates (by setting them to null or unique strings)
        // For safety, let's just make sure there are no existing duplicates blocking the constraint.
        // As a quick fix for existing data in this environment, we'll append the ID to duplicates.
        await pool.query(`
            UPDATE cars c1
            SET car_mulkiya_id = car_mulkiya_id || '-' || id
            WHERE c1.car_mulkiya_id IS NOT NULL AND c1.id NOT IN (
                SELECT MIN(id) FROM cars c2 GROUP BY c2.car_mulkiya_id
            );
        `);

        // Alter table
        await pool.query(`
            ALTER TABLE cars ADD COLUMN IF NOT EXISTS start_date_of_insurance DATE;
            ALTER TABLE cars ADD COLUMN IF NOT EXISTS end_date_of_insurance DATE;
            ALTER TABLE cars ADD COLUMN IF NOT EXISTS start_date_of_mulkiya DATE;
            ALTER TABLE cars ADD COLUMN IF NOT EXISTS end_date_of_mulkiya DATE;
        `);

        // Migrate old data if present
        try {
            await pool.query(`
                UPDATE cars SET end_date_of_insurance = date_of_insurance WHERE date_of_insurance IS NOT NULL AND end_date_of_insurance IS NULL;
                UPDATE cars SET end_date_of_mulkiya = mulkiya_date WHERE mulkiya_date IS NOT NULL AND end_date_of_mulkiya IS NULL;
            `);
        } catch(e) {}

        // Drop old columns
        await pool.query(`
            ALTER TABLE cars DROP COLUMN IF EXISTS date_of_insurance;
            ALTER TABLE cars DROP COLUMN IF EXISTS mulkiya_date;
        `);

        // Add Unique Constraint
        // Drop constraint if exists first (ignoring error)
        try {
            await pool.query(`ALTER TABLE cars DROP CONSTRAINT unique_car_mulkiya_id`);
        } catch (e) {}
        
        await pool.query(`
            ALTER TABLE cars ADD CONSTRAINT unique_car_mulkiya_id UNIQUE (car_mulkiya_id);
        `);

        console.log("Migration completed successfully.");
    } catch (e) {
        console.error("Migration failed:", e);
    } finally {
        pool.end();
    }
}

migrate();
