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

export const up: Migration['up'] = async (db) => {
  // Publishers/ISVs table
  await db.query(`
    CREATE TABLE IF NOT EXISTS publishers (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) UNIQUE NOT NULL,
      display_name VARCHAR(255) NOT NULL,
      description TEXT,
      website_url VARCHAR(500),
      logo_url VARCHAR(500),
      verified BOOLEAN DEFAULT FALSE,
      metadata JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Products table
  await db.query(`
    CREATE TABLE IF NOT EXISTS products (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) NOT NULL,
      slug VARCHAR(255) UNIQUE NOT NULL,
      category VARCHAR(50) NOT NULL CHECK (category IN (
        'COMPUTE',
        'NETWORK_INFRA',
        'BLOCKCHAIN_STACK',
        'BLOCKCHAIN_TOOLS',
        'FINANCIAL_MESSAGING',
        'INTERNET_REGISTRY',
        'AI_LLM_AGENT'
      )),
      description TEXT,
      short_description VARCHAR(500),
      publisher_id UUID NOT NULL REFERENCES publishers(id) ON DELETE CASCADE,
      status VARCHAR(50) NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'PUBLISHED', 'ARCHIVED', 'DEPRECATED')),
      featured BOOLEAN DEFAULT FALSE,
      icon_url VARCHAR(500),
      documentation_url VARCHAR(500),
      support_url VARCHAR(500),
      metadata JSONB DEFAULT '{}'::jsonb,
      tags TEXT[] DEFAULT '{}',
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Product versions table
  await db.query(`
    CREATE TABLE IF NOT EXISTS product_versions (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
      version VARCHAR(50) NOT NULL,
      changelog TEXT,
      template_id UUID,
      status VARCHAR(50) NOT NULL DEFAULT 'DRAFT' CHECK (status IN ('DRAFT', 'PUBLISHED', 'DEPRECATED')),
      is_latest BOOLEAN DEFAULT FALSE,
      released_at TIMESTAMP WITH TIME ZONE,
      metadata JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(product_id, version)
    )
  `)

  // Pricing models table
  await db.query(`
    CREATE TABLE IF NOT EXISTS pricing_models (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
      product_version_id UUID REFERENCES product_versions(id) ON DELETE CASCADE,
      pricing_type VARCHAR(50) NOT NULL CHECK (pricing_type IN ('FREE', 'ONE_TIME', 'SUBSCRIPTION', 'USAGE_BASED', 'HYBRID')),
      base_price DECIMAL(10, 2),
      currency VARCHAR(3) DEFAULT 'USD',
      billing_period VARCHAR(50) CHECK (billing_period IN ('HOURLY', 'DAILY', 'MONTHLY', 'YEARLY')),
      usage_rates JSONB,
      metadata JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  // Product reviews/ratings table
  await db.query(`
    CREATE TABLE IF NOT EXISTS product_reviews (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      product_id UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
      user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
      title VARCHAR(255),
      review_text TEXT,
      verified_purchase BOOLEAN DEFAULT FALSE,
      helpful_count INTEGER DEFAULT 0,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE(product_id, user_id)
    )
  `)

  // Product categories index
  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_products_category ON products(category)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_products_status ON products(status)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_products_publisher ON products(publisher_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_products_tags ON products USING GIN(tags)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_product_versions_product ON product_versions(product_id)
  `)

  await db.query(`
    CREATE INDEX IF NOT EXISTS idx_product_versions_latest ON product_versions(product_id, is_latest) WHERE is_latest = TRUE
  `)

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

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

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

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

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

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

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TABLE IF EXISTS product_reviews`)
  await db.query(`DROP TABLE IF EXISTS pricing_models`)
  await db.query(`DROP TABLE IF EXISTS product_versions`)
  await db.query(`DROP TABLE IF EXISTS products`)
  await db.query(`DROP TABLE IF EXISTS publishers`)
}

