import { pool } from "./db";

export async function createCategoriesTable(): Promise<void> {
  try {
    await pool.query(`
      CREATE TABLE IF NOT EXISTS categories (
        id SERIAL PRIMARY KEY,
        name VARCHAR(255) NOT NULL UNIQUE,
        is_archived BOOLEAN DEFAULT false,
        created_at TIMESTAMP DEFAULT NOW(),
        updated_at TIMESTAMP DEFAULT NOW()
      )
    `);
    console.log("Categories table created successfully");

    // Check if categories exist and seed them
    const result = await pool.query("SELECT COUNT(*) FROM categories");
    const count = parseInt(result.rows[0].count);
    
    if (count === 0) {
      await pool.query(`
        INSERT INTO categories (name, is_archived) VALUES
        ('Telkom', false),
        ('MTN Mobile GSM', false),
        ('MTN Fixed', false),
        ('Axxess', false),
        ('Telkom Smartphone Uncapped', false)
      `);
      console.log("Categories seeded successfully");
    } else {
      // Check if 'Telkom Smartphone Uncapped' exists, if not add it
      const telkomSmartphoneResult = await pool.query(
        "SELECT COUNT(*) FROM categories WHERE name = 'Telkom Smartphone Uncapped'"
      );
      const telkomSmartphoneCount = parseInt(telkomSmartphoneResult.rows[0].count);
      
      if (telkomSmartphoneCount === 0) {
        await pool.query(`
          INSERT INTO categories (name, is_archived) VALUES
          ('Telkom Smartphone Uncapped', false)
        `);
        console.log("Added 'Telkom Smartphone Uncapped' category");
      }
      
      // Check if 'Axxess (Telkom)' exists, if not add it
      const axxessTelkomResult = await pool.query(
        "SELECT COUNT(*) FROM categories WHERE name = 'Axxess (Telkom)'"
      );
      const axxessTelkomCount = parseInt(axxessTelkomResult.rows[0].count);
      
      if (axxessTelkomCount === 0) {
        await pool.query(`
          INSERT INTO categories (name, is_archived) VALUES
          ('Axxess (Telkom)', false)
        `);
        console.log("Added 'Axxess (Telkom)' category");
      }

      // Check if 'Axxess (Fibre Lines)' exists, if not add it
      const axxessFibreResult = await pool.query(
        "SELECT COUNT(*) FROM categories WHERE name = 'Axxess (Fibre Lines)'"
      );
      const axxessFibreCount = parseInt(axxessFibreResult.rows[0].count);
      
      if (axxessFibreCount === 0) {
        await pool.query(`
          INSERT INTO categories (name, is_archived) VALUES
          ('Axxess (Fibre Lines)', false)
        `);
        console.log("Added 'Axxess (Fibre Lines)' category");
      }
      
      // Check if 'OpenServe (Fibre Lines)' exists, if not add it
      const openServeFibreResult = await pool.query(
        "SELECT COUNT(*) FROM categories WHERE name = 'OpenServe (Fibre Lines)'"
      );
      const openServeFibreCount = parseInt(openServeFibreResult.rows[0].count);
      
      if (openServeFibreCount === 0) {
        await pool.query(`
          INSERT INTO categories (name, is_archived) VALUES
          ('OpenServe (Fibre Lines)', false)
        `);
        console.log("Added 'OpenServe (Fibre Lines)' category");
      }
    }
  } catch (error) {
    console.error("Error creating categories table:", error);
    throw error;
  }
}