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

export const up: Migration['up'] = async (db) => {
  await db.query(`
    CREATE TABLE IF NOT EXISTS clients (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      name VARCHAR(255) UNIQUE NOT NULL,
      primary_domain VARCHAR(255),
      status VARCHAR(50) NOT NULL DEFAULT 'PENDING'
        CHECK (status IN ('ACTIVE', 'PENDING', 'SUSPENDED', 'DELETED')),
      metadata JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  await db.query(`
    CREATE TABLE IF NOT EXISTS client_users (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
      user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      role VARCHAR(50) NOT NULL DEFAULT 'CLIENT_USER'
        CHECK (role IN ('CLIENT_OWNER', 'CLIENT_ADMIN', 'CLIENT_BILLING_ADMIN', 'CLIENT_USER', 'CLIENT_VIEWER')),
      permissions JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      UNIQUE (client_id, user_id)
    )
  `)

  await db.query(`
    ALTER TABLE tenants
    ADD COLUMN IF NOT EXISTS client_id UUID REFERENCES clients(id) ON DELETE SET NULL
  `)

  await db.query(`
    CREATE TABLE IF NOT EXISTS service_subscriptions (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      client_id UUID NOT NULL REFERENCES clients(id) ON DELETE CASCADE,
      tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
      offer_code VARCHAR(255) NOT NULL,
      offer_name VARCHAR(255) NOT NULL,
      offer_type VARCHAR(50) NOT NULL
        CHECK (offer_type IN ('native', 'partner')),
      commercial_model VARCHAR(50) NOT NULL
        CHECK (commercial_model IN ('IRU', 'SaaS', 'managed_service', 'reserved_capacity', 'custom')),
      support_owner VARCHAR(50) NOT NULL
        CHECK (support_owner IN ('sankofa', 'partner', 'shared')),
      fulfillment_mode VARCHAR(50) NOT NULL
        CHECK (fulfillment_mode IN ('self_service', 'request_only', 'operator_provisioned')),
      billing_mode VARCHAR(50) NOT NULL
        CHECK (billing_mode IN ('subscription', 'contract', 'quote')),
      status VARCHAR(50) NOT NULL DEFAULT 'PENDING'
        CHECK (status IN ('PENDING', 'ACTIVE', 'SUSPENDED', 'CANCELLED', 'PREVIEW', 'REQUEST_ONLY')),
      activated_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()
    )
  `)

  await db.query(`
    CREATE TABLE IF NOT EXISTS entitlements (
      id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
      subscription_id UUID NOT NULL REFERENCES service_subscriptions(id) ON DELETE CASCADE,
      tenant_id UUID REFERENCES tenants(id) ON DELETE SET NULL,
      entitlement_key VARCHAR(255) NOT NULL,
      status VARCHAR(50) NOT NULL DEFAULT 'PENDING'
        CHECK (status IN ('PENDING', 'ACTIVE', 'SUSPENDED', 'REVOKED')),
      scope JSONB DEFAULT '{}'::jsonb,
      metadata JSONB DEFAULT '{}'::jsonb,
      created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
      updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
    )
  `)

  await db.query(`
    ALTER TABLE billing_accounts
    ADD COLUMN IF NOT EXISTS client_id UUID REFERENCES clients(id) ON DELETE SET NULL
  `)
  await db.query(`
    ALTER TABLE billing_accounts
    ADD COLUMN IF NOT EXISTS subscription_id UUID REFERENCES service_subscriptions(id) ON DELETE SET NULL
  `)

  await db.query(`
    ALTER TABLE usage_records
    ADD COLUMN IF NOT EXISTS client_id UUID REFERENCES clients(id) ON DELETE SET NULL
  `)
  await db.query(`
    ALTER TABLE usage_records
    ADD COLUMN IF NOT EXISTS subscription_id UUID REFERENCES service_subscriptions(id) ON DELETE SET NULL
  `)

  await db.query(`
    ALTER TABLE cost_allocations
    ADD COLUMN IF NOT EXISTS client_id UUID REFERENCES clients(id) ON DELETE SET NULL
  `)
  await db.query(`
    ALTER TABLE cost_allocations
    ADD COLUMN IF NOT EXISTS subscription_id UUID REFERENCES service_subscriptions(id) ON DELETE SET NULL
  `)

  await db.query(`
    ALTER TABLE invoices
    ADD COLUMN IF NOT EXISTS client_id UUID REFERENCES clients(id) ON DELETE SET NULL
  `)
  await db.query(`
    ALTER TABLE invoices
    ADD COLUMN IF NOT EXISTS subscription_id UUID REFERENCES service_subscriptions(id) ON DELETE SET NULL
  `)

  await db.query(`
    ALTER TABLE payments
    ADD COLUMN IF NOT EXISTS client_id UUID REFERENCES clients(id) ON DELETE SET NULL
  `)
  await db.query(`
    ALTER TABLE payments
    ADD COLUMN IF NOT EXISTS subscription_id UUID REFERENCES service_subscriptions(id) ON DELETE SET NULL
  `)

  await db.query(`
    ALTER TABLE billing_alerts
    ADD COLUMN IF NOT EXISTS client_id UUID REFERENCES clients(id) ON DELETE SET NULL
  `)
  await db.query(`
    ALTER TABLE billing_alerts
    ADD COLUMN IF NOT EXISTS subscription_id UUID REFERENCES service_subscriptions(id) ON DELETE SET NULL
  `)

  await db.query(`
    ALTER TABLE budgets
    ADD COLUMN IF NOT EXISTS client_id UUID REFERENCES clients(id) ON DELETE SET NULL
  `)
  await db.query(`
    ALTER TABLE budgets
    ADD COLUMN IF NOT EXISTS subscription_id UUID REFERENCES service_subscriptions(id) ON DELETE SET NULL
  `)

  await db.query(`
    INSERT INTO clients (name, primary_domain, status, metadata)
    SELECT
      t.name,
      t.domain,
      CASE
        WHEN t.status = 'ACTIVE' THEN 'ACTIVE'
        WHEN t.status = 'SUSPENDED' THEN 'SUSPENDED'
        WHEN t.status = 'DELETED' THEN 'DELETED'
        ELSE 'PENDING'
      END,
      jsonb_build_object(
        'backfilledFromTenantId', t.id,
        'source', '027_client_subscription_entitlements'
      )
    FROM tenants t
    LEFT JOIN clients c ON c.name = t.name
    WHERE c.id IS NULL
  `)

  await db.query(`
    UPDATE tenants t
    SET client_id = c.id
    FROM clients c
    WHERE t.client_id IS NULL
      AND c.name = t.name
  `)

  await db.query(`
    INSERT INTO client_users (client_id, user_id, role, permissions)
    SELECT
      t.client_id,
      tu.user_id,
      CASE tu.role
        WHEN 'TENANT_OWNER' THEN 'CLIENT_OWNER'
        WHEN 'TENANT_ADMIN' THEN 'CLIENT_ADMIN'
        WHEN 'TENANT_BILLING_ADMIN' THEN 'CLIENT_BILLING_ADMIN'
        WHEN 'TENANT_VIEWER' THEN 'CLIENT_VIEWER'
        ELSE 'CLIENT_USER'
      END,
      COALESCE(tu.permissions, '{}'::jsonb)
    FROM tenant_users tu
    JOIN tenants t ON t.id = tu.tenant_id
    LEFT JOIN client_users cu ON cu.client_id = t.client_id AND cu.user_id = tu.user_id
    WHERE t.client_id IS NOT NULL
      AND cu.id IS NULL
  `)

  await db.query(`
    INSERT INTO service_subscriptions (
      client_id,
      tenant_id,
      offer_code,
      offer_name,
      offer_type,
      commercial_model,
      support_owner,
      fulfillment_mode,
      billing_mode,
      status,
      activated_at,
      metadata
    )
    SELECT
      t.client_id,
      t.id,
      'tenant-workspace',
      'Tenant Workspace',
      'native',
      'custom',
      'sankofa',
      'operator_provisioned',
      'subscription',
      CASE
        WHEN t.status = 'ACTIVE' THEN 'ACTIVE'
        WHEN t.status = 'SUSPENDED' THEN 'SUSPENDED'
        ELSE 'PENDING'
      END,
      CASE WHEN t.status = 'ACTIVE' THEN NOW() ELSE NULL END,
      jsonb_build_object(
        'tier', t.tier,
        'backfilledFromTenantId', t.id,
        'source', '027_client_subscription_entitlements'
      )
    FROM tenants t
    LEFT JOIN service_subscriptions s
      ON s.tenant_id = t.id
      AND s.offer_code = 'tenant-workspace'
    WHERE t.client_id IS NOT NULL
      AND s.id IS NULL
  `)

  await db.query(`
    INSERT INTO entitlements (
      subscription_id,
      tenant_id,
      entitlement_key,
      status,
      scope,
      metadata
    )
    SELECT
      s.id,
      s.tenant_id,
      'tenant.workspace',
      CASE
        WHEN s.status = 'ACTIVE' THEN 'ACTIVE'
        WHEN s.status = 'SUSPENDED' THEN 'SUSPENDED'
        ELSE 'PENDING'
      END,
      jsonb_build_object(
        'offerCode', s.offer_code,
        'offerType', s.offer_type,
        'commercialModel', s.commercial_model
      ),
      jsonb_build_object(
        'source', '027_client_subscription_entitlements'
      )
    FROM service_subscriptions s
    LEFT JOIN entitlements e
      ON e.subscription_id = s.id
      AND e.entitlement_key = 'tenant.workspace'
    WHERE s.offer_code = 'tenant-workspace'
      AND e.id IS NULL
  `)

  await db.query(`
    UPDATE billing_accounts ba
    SET
      client_id = t.client_id,
      subscription_id = s.id
    FROM tenants t
    LEFT JOIN service_subscriptions s
      ON s.tenant_id = t.id
      AND s.offer_code = 'tenant-workspace'
    WHERE ba.tenant_id = t.id
      AND (ba.client_id IS NULL OR ba.subscription_id IS NULL)
  `)

  await db.query(`
    UPDATE usage_records ur
    SET
      client_id = t.client_id,
      subscription_id = s.id
    FROM tenants t
    LEFT JOIN service_subscriptions s
      ON s.tenant_id = t.id
      AND s.offer_code = 'tenant-workspace'
    WHERE ur.tenant_id = t.id
      AND (ur.client_id IS NULL OR ur.subscription_id IS NULL)
  `)

  await db.query(`
    UPDATE cost_allocations ca
    SET
      client_id = t.client_id,
      subscription_id = s.id
    FROM tenants t
    LEFT JOIN service_subscriptions s
      ON s.tenant_id = t.id
      AND s.offer_code = 'tenant-workspace'
    WHERE ca.tenant_id = t.id
      AND (ca.client_id IS NULL OR ca.subscription_id IS NULL)
  `)

  await db.query(`
    UPDATE invoices i
    SET
      client_id = t.client_id,
      subscription_id = s.id
    FROM tenants t
    LEFT JOIN service_subscriptions s
      ON s.tenant_id = t.id
      AND s.offer_code = 'tenant-workspace'
    WHERE i.tenant_id = t.id
      AND (i.client_id IS NULL OR i.subscription_id IS NULL)
  `)

  await db.query(`
    UPDATE payments p
    SET
      client_id = t.client_id,
      subscription_id = s.id
    FROM tenants t
    LEFT JOIN service_subscriptions s
      ON s.tenant_id = t.id
      AND s.offer_code = 'tenant-workspace'
    WHERE p.tenant_id = t.id
      AND (p.client_id IS NULL OR p.subscription_id IS NULL)
  `)

  await db.query(`
    UPDATE billing_alerts ba
    SET
      client_id = t.client_id,
      subscription_id = s.id
    FROM tenants t
    LEFT JOIN service_subscriptions s
      ON s.tenant_id = t.id
      AND s.offer_code = 'tenant-workspace'
    WHERE ba.tenant_id = t.id
      AND (ba.client_id IS NULL OR ba.subscription_id IS NULL)
  `)

  await db.query(`
    UPDATE budgets b
    SET
      client_id = t.client_id,
      subscription_id = s.id
    FROM tenants t
    LEFT JOIN service_subscriptions s
      ON s.tenant_id = t.id
      AND s.offer_code = 'tenant-workspace'
    WHERE b.tenant_id = t.id
      AND (b.client_id IS NULL OR b.subscription_id IS NULL)
  `)

  await db.query(`CREATE INDEX IF NOT EXISTS idx_tenants_client_id ON tenants(client_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_clients_primary_domain ON clients(primary_domain) WHERE primary_domain IS NOT NULL`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_client_users_client_id ON client_users(client_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_client_users_user_id ON client_users(user_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_service_subscriptions_client_id ON service_subscriptions(client_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_service_subscriptions_tenant_id ON service_subscriptions(tenant_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_service_subscriptions_status ON service_subscriptions(status)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_entitlements_subscription_id ON entitlements(subscription_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_entitlements_tenant_id ON entitlements(tenant_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_usage_records_client_id ON usage_records(client_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_invoices_client_id ON invoices(client_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_budgets_client_id ON budgets(client_id)`)
  await db.query(`CREATE INDEX IF NOT EXISTS idx_billing_alerts_client_id ON billing_alerts(client_id)`)

  await db.query(`
    CREATE TRIGGER update_clients_updated_at BEFORE UPDATE ON clients
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
  await db.query(`
    CREATE TRIGGER update_client_users_updated_at BEFORE UPDATE ON client_users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
  await db.query(`
    CREATE TRIGGER update_service_subscriptions_updated_at BEFORE UPDATE ON service_subscriptions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
  await db.query(`
    CREATE TRIGGER update_entitlements_updated_at BEFORE UPDATE ON entitlements
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column()
  `)
}

export const down: Migration['down'] = async (db) => {
  await db.query(`DROP TRIGGER IF EXISTS update_entitlements_updated_at ON entitlements`)
  await db.query(`DROP TRIGGER IF EXISTS update_service_subscriptions_updated_at ON service_subscriptions`)
  await db.query(`DROP TRIGGER IF EXISTS update_client_users_updated_at ON client_users`)
  await db.query(`DROP TRIGGER IF EXISTS update_clients_updated_at ON clients`)

  await db.query(`DROP INDEX IF EXISTS idx_billing_alerts_client_id`)
  await db.query(`DROP INDEX IF EXISTS idx_budgets_client_id`)
  await db.query(`DROP INDEX IF EXISTS idx_invoices_client_id`)
  await db.query(`DROP INDEX IF EXISTS idx_usage_records_client_id`)
  await db.query(`DROP INDEX IF EXISTS idx_entitlements_tenant_id`)
  await db.query(`DROP INDEX IF EXISTS idx_entitlements_subscription_id`)
  await db.query(`DROP INDEX IF EXISTS idx_service_subscriptions_status`)
  await db.query(`DROP INDEX IF EXISTS idx_service_subscriptions_tenant_id`)
  await db.query(`DROP INDEX IF EXISTS idx_service_subscriptions_client_id`)
  await db.query(`DROP INDEX IF EXISTS idx_client_users_user_id`)
  await db.query(`DROP INDEX IF EXISTS idx_client_users_client_id`)
  await db.query(`DROP INDEX IF EXISTS idx_clients_primary_domain`)
  await db.query(`DROP INDEX IF EXISTS idx_tenants_client_id`)

  await db.query(`ALTER TABLE budgets DROP COLUMN IF EXISTS subscription_id`)
  await db.query(`ALTER TABLE budgets DROP COLUMN IF EXISTS client_id`)
  await db.query(`ALTER TABLE billing_alerts DROP COLUMN IF EXISTS subscription_id`)
  await db.query(`ALTER TABLE billing_alerts DROP COLUMN IF EXISTS client_id`)
  await db.query(`ALTER TABLE payments DROP COLUMN IF EXISTS subscription_id`)
  await db.query(`ALTER TABLE payments DROP COLUMN IF EXISTS client_id`)
  await db.query(`ALTER TABLE invoices DROP COLUMN IF EXISTS subscription_id`)
  await db.query(`ALTER TABLE invoices DROP COLUMN IF EXISTS client_id`)
  await db.query(`ALTER TABLE cost_allocations DROP COLUMN IF EXISTS subscription_id`)
  await db.query(`ALTER TABLE cost_allocations DROP COLUMN IF EXISTS client_id`)
  await db.query(`ALTER TABLE usage_records DROP COLUMN IF EXISTS subscription_id`)
  await db.query(`ALTER TABLE usage_records DROP COLUMN IF EXISTS client_id`)
  await db.query(`ALTER TABLE billing_accounts DROP COLUMN IF EXISTS subscription_id`)
  await db.query(`ALTER TABLE billing_accounts DROP COLUMN IF EXISTS client_id`)
  await db.query(`ALTER TABLE tenants DROP COLUMN IF EXISTS client_id`)

  await db.query(`DROP TABLE IF EXISTS entitlements`)
  await db.query(`DROP TABLE IF EXISTS service_subscriptions`)
  await db.query(`DROP TABLE IF EXISTS client_users`)
  await db.query(`DROP TABLE IF EXISTS clients`)
}
