import { Migration } from '../migrate.js'

export const up: Migration['up'] = async (db) => {
  // Enable UUID extension
  await db.query(`CREATE EXTENSION IF NOT EXISTS "uuid-ossp"`)

  // Update timestamp trigger function
  await db.query(`
    CREATE OR REPLACE FUNCTION update_updated_at_column()
    RETURNS TRIGGER AS $$
    BEGIN
      NEW.updated_at = NOW();
      RETURN NEW;
    END;
    $$ language 'plpgsql';
  `)

  // Users table
  await db.query(`
    CREATE TABLE IF NOT EXISTS users (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(255) NOT NULL,
      password_hash VARCHAR(255) NOT NULL,
      role VARCHAR(50) NOT NULL DEFAULT 'USER' CHECK (role IN ('ADMIN', 'USER', 'VIEWER')),
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Sites table
  await db.query(`
    CREATE TABLE IF NOT EXISTS sites (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) NOT NULL,
      region VARCHAR(255) NOT NULL,
      status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'INACTIVE', 'MAINTENANCE')),
      metadata JSONB,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Resources table
  await db.query(`
    CREATE TABLE IF NOT EXISTS resources (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) NOT NULL,
      type VARCHAR(50) NOT NULL CHECK (type IN ('VM', 'CONTAINER', 'STORAGE', 'NETWORK')),
      status VARCHAR(50) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'PROVISIONING', 'RUNNING', 'STOPPED', 'ERROR', 'DELETING')),
      site_id UUID NOT NULL REFERENCES sites(id) ON DELETE CASCADE,
      metadata JSONB,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Indexes
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resources_site_id ON resources(site_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resources_type ON resources(type)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_resources_status ON resources(status)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_users_email ON users(email)`)

  // Triggers
  await db.query(`
    CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
  await db.query(`
    CREATE TRIGGER update_sites_updated_at BEFORE UPDATE ON sites
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
  await db.query(`
    CREATE TRIGGER update_resources_updated_at BEFORE UPDATE ON resources
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TRIGGER IF EXISTS update_resources_updated_at ON resources`)
  await db.query(`DROP TRIGGER IF EXISTS update_sites_updated_at ON sites`)
  await db.query(`DROP TRIGGER IF EXISTS update_users_updated_at ON users`)
  await db.query(`DROP INDEX IF EXISTS idx_users_email`)
  await db.query(`DROP INDEX IF EXISTS idx_resources_status`)
  await db.query(`DROP INDEX IF EXISTS idx_resources_type`)
  await db.query(`DROP INDEX IF EXISTS idx_resources_site_id`)
  await db.query(`DROP TABLE IF EXISTS resources`)
  await db.query(`DROP TABLE IF EXISTS sites`)
  await db.query(`DROP TABLE IF EXISTS users`)
  await db.query(`DROP FUNCTION IF EXISTS update_updated_at_column()`)
}

