import { PrismaClient } from "@prisma/client";
import { PrismaMariaDb } from "@prisma/adapter-mariadb";
import "dotenv/config";

const base = process.env.DATABASE_URL;
const sep = base.includes("?") ? "&" : "?";
const connectionString = `${base}${sep}connection_limit=5&pool_timeout=10`;
const adapter = new PrismaMariaDb(connectionString);
const prisma = new PrismaClient({ adapter });

async function main() {
  try {
    console.log('Starting manual migration...');

    // Step 1: Change level column to VARCHAR
    await prisma.$executeRawUnsafe(
      "ALTER TABLE `users` MODIFY COLUMN `level` VARCHAR(20) NOT NULL DEFAULT 'LV01'"
    );
    console.log('✓ Step 1: level column changed to VARCHAR');

    // Step 2: Map old enum values
    await prisma.$executeRawUnsafe("UPDATE `users` SET `level` = 'LV01' WHERE `level` = 'BRONZE'");
    await prisma.$executeRawUnsafe("UPDATE `users` SET `level` = 'LV03' WHERE `level` = 'SILVER'");
    await prisma.$executeRawUnsafe("UPDATE `users` SET `level` = 'LV06' WHERE `level` = 'GOLD'");
    await prisma.$executeRawUnsafe("UPDATE `users` SET `level` = 'LV10' WHERE `level` = 'PLATINUM'");
    console.log('✓ Step 2: Old enum values mapped to new levels');

    // Step 3: Add bio column (if not exists)
    try {
      await prisma.$executeRawUnsafe("ALTER TABLE `users` ADD COLUMN `bio` TEXT NULL");
      console.log('✓ Step 3: bio column added');
    } catch (e) {
      console.log('✓ Step 3: bio column may already exist, skipping');
    }

    // Step 4: Add parent_id column (if not exists)
    try {
      await prisma.$executeRawUnsafe("ALTER TABLE `forum_categories` ADD COLUMN `parent_id` INT NULL");
      console.log('✓ Step 4: parent_id column added');
    } catch (e) {
      console.log('✓ Step 4: parent_id column may already exist, skipping');
    }

    // Step 5: Add foreign key (if not exists)
    try {
      await prisma.$executeRawUnsafe(
        "ALTER TABLE `forum_categories` ADD CONSTRAINT `forum_categories_parent_id_fkey` " +
        "FOREIGN KEY (`parent_id`) REFERENCES `forum_categories`(`id`) ON DELETE SET NULL ON UPDATE CASCADE"
      );
      console.log('✓ Step 5: Foreign key added');
    } catch (e) {
      console.log('✓ Step 5: Foreign key may already exist, skipping');
    }

    // Step 6: Add points_history table (if not exists)
    try {
      await prisma.$executeRawUnsafe(
        `CREATE TABLE IF NOT EXISTS \`points_history\` (
          \`id\` INT AUTO_INCREMENT PRIMARY KEY,
          \`user_id\` INT NOT NULL,
          \`points\` INT NOT NULL,
          \`reason\` VARCHAR(50) NOT NULL,
          \`detail\` VARCHAR(200) NULL,
          \`ref_type\` VARCHAR(50) NULL,
          \`ref_id\` INT NULL,
          \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
          INDEX \`idx_user_created\` (\`user_id\`, \`created_at\`),
          FOREIGN KEY (\`user_id\`) REFERENCES \`users\`(\`id\`) ON DELETE CASCADE
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4`
      );
      console.log('✓ Step 6: points_history table created');
    } catch (e) {
      console.log('✓ Step 6: points_history table may already exist, skipping');
    }

    // Step 7: Add is_answer column to forum_posts (if not exists)
    try {
      await prisma.$executeRawUnsafe("ALTER TABLE `forum_posts` ADD COLUMN `is_answer` BOOLEAN NOT NULL DEFAULT false");
      console.log('✓ Step 7: is_answer column added');
    } catch (e) {
      console.log('✓ Step 7: is_answer column may already exist, skipping');
    }

    console.log('\n✓ Migration completed successfully!');
  } catch (e) {
    console.error('Error:', e.message);
    process.exit(1);
  } finally {
    await prisma.$disconnect();
  }
}

main();