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

export const up: Migration['up'] = async (db) => {
  // Datacenters table (if not exists)
  await db.query(`
    CREATE TABLE IF NOT EXISTS datacenters (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) NOT NULL,
      type VARCHAR(50) NOT NULL CHECK (type IN ('CORE', 'REGIONAL', 'EDGE')),
      region_id UUID REFERENCES regions(id),
      location JSONB,
      status VARCHAR(50) DEFAULT 'ACTIVE',
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // PoP Mappings table
  await db.query(`
    CREATE TABLE IF NOT EXISTS pop_mappings (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      pop_id VARCHAR(255) UNIQUE NOT NULL,
      pop_location JSONB NOT NULL,
      primary_datacenter_id UUID REFERENCES datacenters(id),
      region_id UUID REFERENCES regions(id),
      tunnel_configuration JSONB DEFAULT '[]'::jsonb,
      routing_rules JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Tunnel Configurations table
  await db.query(`
    CREATE TABLE IF NOT EXISTS tunnel_configurations (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      tunnel_id VARCHAR(255) UNIQUE NOT NULL,
      pop_id VARCHAR(255) REFERENCES pop_mappings(pop_id),
      datacenter_id UUID REFERENCES datacenters(id),
      tunnel_type VARCHAR(50) NOT NULL CHECK (tunnel_type IN ('PRIMARY', 'BACKUP', 'LOAD_BALANCED')),
      health_status VARCHAR(50) NOT NULL DEFAULT 'HEALTHY' CHECK (health_status IN ('HEALTHY', 'DEGRADED', 'DOWN')),
      endpoint VARCHAR(500),
      health_check JSONB DEFAULT '{}'::jsonb,
      configuration JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Sovereignty Zones table
  await db.query(`
    CREATE TABLE IF NOT EXISTS sovereignty_zones (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) NOT NULL,
      country VARCHAR(100) NOT NULL,
      region VARCHAR(100) NOT NULL,
      regulatory_frameworks TEXT[] DEFAULT '{}',
      data_residency_rules JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Federated Stores table
  await db.query(`
    CREATE TABLE IF NOT EXISTS federated_stores (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      zone_id UUID REFERENCES sovereignty_zones(id) ON DELETE CASCADE,
      store_type VARCHAR(50) NOT NULL CHECK (store_type IN ('POSTGRES', 'MONGODB', 'REDIS', 'OBJECT_STORE')),
      connection_string TEXT,
      role VARCHAR(50) NOT NULL CHECK (role IN ('PRIMARY', 'REPLICA', 'METADATA')),
      replication_config JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Data Residency Rules table
  await db.query(`
    CREATE TABLE IF NOT EXISTS data_residency_rules (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      data_type VARCHAR(100) NOT NULL,
      source_region VARCHAR(100) NOT NULL,
      allowed_regions TEXT[] DEFAULT '{}',
      prohibited_regions TEXT[] DEFAULT '{}',
      encryption_required BOOLEAN DEFAULT FALSE,
      retention_policy JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Replication Logs table
  await db.query(`
    CREATE TABLE IF NOT EXISTS replication_logs (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      source_store_id UUID REFERENCES federated_stores(id) ON DELETE SET NULL,
      target_store_id UUID REFERENCES federated_stores(id) ON DELETE SET NULL,
      data_id UUID,
      operation VARCHAR(50) NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
      status VARCHAR(50) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'COMPLETED', 'FAILED')),
      compliance_check JSONB DEFAULT '{}'::jsonb,
      error_message TEXT,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      completed_at TIMESTAMP WITH TIME ZONE
    )
  `)

  // Indexes
  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_pop_mappings_datacenter ON pop_mappings(primary_datacenter_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_pop_mappings_region ON pop_mappings(region_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_tunnel_configurations_pop ON tunnel_configurations(pop_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_tunnel_configurations_datacenter ON tunnel_configurations(datacenter_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_federated_stores_zone ON federated_stores(zone_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_federated_stores_role ON federated_stores(role)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_data_residency_rules_type ON data_residency_rules(data_type, source_region)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_replication_logs_status ON replication_logs(status, created_at)
  `)

  // Update triggers
  await db.query(`
    CREATE TRIGGER update_pop_mappings_updated_at
    BEFORE UPDATE ON pop_mappings
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column()
  `)

  await db.query(`
    CREATE TRIGGER update_tunnel_configurations_updated_at
    BEFORE UPDATE ON tunnel_configurations
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column()
  `)

  await db.query(`
    CREATE TRIGGER update_sovereignty_zones_updated_at
    BEFORE UPDATE ON sovereignty_zones
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column()
  `)

  await db.query(`
    CREATE TRIGGER update_federated_stores_updated_at
    BEFORE UPDATE ON federated_stores
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column()
  `)

  await db.query(`
    CREATE TRIGGER update_data_residency_rules_updated_at
    BEFORE UPDATE ON data_residency_rules
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column()
  `)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TABLE IF EXISTS replication_logs`)
  await db.query(`DROP TABLE IF EXISTS data_residency_rules`)
  await db.query(`DROP TABLE IF EXISTS federated_stores`)
  await db.query(`DROP TABLE IF EXISTS sovereignty_zones`)
  await db.query(`DROP TABLE IF EXISTS tunnel_configurations`)
  await db.query(`DROP TABLE IF EXISTS pop_mappings`)
}

