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

export const up: Migration['up'] = async (db) => {
  // Templates table
  await db.query(`
    CREATE TABLE IF NOT EXISTS templates (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) NOT NULL,
      slug VARCHAR(255) UNIQUE NOT NULL,
      description TEXT,
      template_type VARCHAR(50) NOT NULL CHECK (template_type IN ('TERRAFORM', 'HELM', 'ANSIBLE', 'PTF', 'KUBERNETES')),
      version VARCHAR(50) NOT NULL,
      content TEXT NOT NULL,
      parameters JSONB DEFAULT '{}'::jsonb,
      outputs JSONB DEFAULT '{}'::jsonb,
      metadata JSONB DEFAULT '{}'::jsonb,
      status VARCHAR(50) NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'PUBLISHED', 'DEPRECATED')),
      created_by UUID REFERENCES users(id) ON DELETE SET NULL,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(slug, version)
    )
  `)

  // Template versions table (for versioning)
  await db.query(`
    CREATE TABLE IF NOT EXISTS template_versions (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      template_id UUID NOT NULL REFERENCES templates(id) ON DELETE CASCADE,
      version VARCHAR(50) NOT NULL,
      content TEXT NOT NULL,
      parameters JSONB DEFAULT '{}'::jsonb,
      outputs JSONB DEFAULT '{}'::jsonb,
      changelog TEXT,
      is_latest BOOLEAN DEFAULT FALSE,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(template_id, version)
    )
  `)

  // Template deployments table (links templates to deployments)
  await db.query(`
    CREATE TABLE IF NOT EXISTS template_deployments (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      template_id UUID NOT NULL REFERENCES templates(id) ON DELETE CASCADE,
      template_version_id UUID REFERENCES template_versions(id) ON DELETE SET NULL,
      deployment_id UUID,
      rendered_content TEXT,
      parameters_used JSONB DEFAULT '{}'::jsonb,
      status VARCHAR(50) NOT NULL DEFAULT 'PENDING' CHECK (status IN ('PENDING', 'RENDERING', 'RENDERED', 'FAILED')),
      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_templates_type ON templates(template_type)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_templates_status ON templates(status)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_templates_slug ON templates(slug)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_template_versions_template ON template_versions(template_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_template_versions_latest ON template_versions(template_id, is_latest) WHERE is_latest = TRUE
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_template_deployments_template ON template_deployments(template_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_template_deployments_deployment ON template_deployments(deployment_id)
  `)

  // Full-text search index
  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_templates_search ON templates USING GIN(
      to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, ''))
    )
  `)

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

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

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

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TABLE IF EXISTS template_deployments`)
  await db.query(`DROP TABLE IF EXISTS template_versions`)
  await db.query(`DROP TABLE IF EXISTS templates`)
}

