import { getDb } from '../db/index.js'
import { logger } from '../lib/logger.js'
import { Client, Entitlement, ServiceSubscription } from '../types/operating-model.js'

class OperatingModelService {
  private formatClient(row: any): Client {
    return {
      id: row.id,
      name: row.name,
      primaryDomain: row.primary_domain,
      status: row.status,
      metadata: row.metadata || {},
      createdAt: row.created_at,
      updatedAt: row.updated_at,
    }
  }

  private formatSubscription(row: any): ServiceSubscription {
    return {
      id: row.id,
      clientId: row.client_id,
      tenantId: row.tenant_id,
      offerCode: row.offer_code,
      offerName: row.offer_name,
      offerType: row.offer_type,
      commercialModel: row.commercial_model,
      supportOwner: row.support_owner,
      fulfillmentMode: row.fulfillment_mode,
      billingMode: row.billing_mode,
      status: row.status,
      activatedAt: row.activated_at,
      metadata: row.metadata || {},
      createdAt: row.created_at,
      updatedAt: row.updated_at,
    }
  }

  private formatEntitlement(row: any): Entitlement {
    return {
      id: row.id,
      subscriptionId: row.subscription_id,
      tenantId: row.tenant_id,
      entitlementKey: row.entitlement_key,
      status: row.status,
      scope: row.scope || {},
      metadata: row.metadata || {},
      createdAt: row.created_at,
      updatedAt: row.updated_at,
    }
  }

  async bootstrapClientForTenant(tenantId: string): Promise<{
    client: Client
    subscription: ServiceSubscription
    entitlement: Entitlement
  }> {
    const db = getDb()
    const tenantResult = await db.query(`SELECT * FROM tenants WHERE id = $1`, [tenantId])
    if (tenantResult.rows.length === 0) {
      throw new Error(`Tenant ${tenantId} not found`)
    }

    const tenant = tenantResult.rows[0]
    let clientRow: any

    if (tenant.client_id) {
      const clientResult = await db.query(`SELECT * FROM clients WHERE id = $1`, [tenant.client_id])
      clientRow = clientResult.rows[0]
    } else {
      const createdClient = await db.query(
        `INSERT INTO clients (name, primary_domain, status, metadata)
         VALUES ($1, $2, $3, $4)
         RETURNING *`,
        [
          tenant.name,
          tenant.domain,
          tenant.status === 'ACTIVE' ? 'ACTIVE' : tenant.status === 'SUSPENDED' ? 'SUSPENDED' : 'PENDING',
          JSON.stringify({
            source: 'bootstrapClientForTenant',
            tenantId: tenant.id,
          }),
        ]
      )
      clientRow = createdClient.rows[0]

      await db.query(`UPDATE tenants SET client_id = $1, updated_at = NOW() WHERE id = $2`, [
        clientRow.id,
        tenant.id,
      ])

      await db.query(
        `INSERT INTO client_users (client_id, user_id, role, permissions)
         SELECT
           $1,
           user_id,
           CASE 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(permissions, '{}'::jsonb)
         FROM tenant_users
         WHERE tenant_id = $2
         ON CONFLICT (client_id, user_id) DO NOTHING`,
        [clientRow.id, tenant.id]
      )
    }

    let subscriptionRow: any
    const existingSubscription = await db.query(
      `SELECT * FROM service_subscriptions WHERE tenant_id = $1 AND offer_code = 'tenant-workspace' ORDER BY created_at ASC LIMIT 1`,
      [tenant.id]
    )

    if (existingSubscription.rows.length > 0) {
      subscriptionRow = existingSubscription.rows[0]
    } else {
      const createdSubscription = 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
         )
         VALUES ($1, $2, 'tenant-workspace', 'Tenant Workspace', 'native', 'custom', 'sankofa', 'operator_provisioned', 'subscription', $3, $4, $5)
         RETURNING *`,
        [
          clientRow.id,
          tenant.id,
          tenant.status === 'ACTIVE' ? 'ACTIVE' : tenant.status === 'SUSPENDED' ? 'SUSPENDED' : 'PENDING',
          tenant.status === 'ACTIVE' ? new Date() : null,
          JSON.stringify({
            tier: tenant.tier,
            source: 'bootstrapClientForTenant',
          }),
        ]
      )
      subscriptionRow = createdSubscription.rows[0]
    }

    let entitlementRow: any
    const existingEntitlement = await db.query(
      `SELECT * FROM entitlements WHERE subscription_id = $1 AND entitlement_key = 'tenant.workspace' LIMIT 1`,
      [subscriptionRow.id]
    )
    if (existingEntitlement.rows.length > 0) {
      entitlementRow = existingEntitlement.rows[0]
    } else {
      const createdEntitlement = await db.query(
        `INSERT INTO entitlements (subscription_id, tenant_id, entitlement_key, status, scope, metadata)
         VALUES ($1, $2, 'tenant.workspace', $3, $4, $5)
         RETURNING *`,
        [
          subscriptionRow.id,
          tenant.id,
          subscriptionRow.status === 'ACTIVE' ? 'ACTIVE' : subscriptionRow.status === 'SUSPENDED' ? 'SUSPENDED' : 'PENDING',
          JSON.stringify({
            offerCode: subscriptionRow.offer_code,
            commercialModel: subscriptionRow.commercial_model,
          }),
          JSON.stringify({ source: 'bootstrapClientForTenant' }),
        ]
      )
      entitlementRow = createdEntitlement.rows[0]
    }

    await db.query(
      `UPDATE billing_accounts
       SET client_id = $1, subscription_id = $2, updated_at = NOW()
       WHERE tenant_id = $3`,
      [clientRow.id, subscriptionRow.id, tenant.id]
    )

    logger.info('Bootstrapped Phoenix operating model for tenant', {
      tenantId: tenant.id,
      clientId: clientRow.id,
      subscriptionId: subscriptionRow.id,
      entitlementId: entitlementRow.id,
    })

    return {
      client: this.formatClient(clientRow),
      subscription: this.formatSubscription(subscriptionRow),
      entitlement: this.formatEntitlement(entitlementRow),
    }
  }

  async getClient(clientId: string): Promise<Client> {
    const db = getDb()
    const result = await db.query(`SELECT * FROM clients WHERE id = $1`, [clientId])
    if (result.rows.length === 0) {
      throw new Error(`Client ${clientId} not found`)
    }
    return this.formatClient(result.rows[0])
  }

  async listClients(): Promise<Client[]> {
    const db = getDb()
    const result = await db.query(`SELECT * FROM clients ORDER BY created_at DESC`)
    return result.rows.map((row) => this.formatClient(row))
  }

  async getClientByTenantId(tenantId: string): Promise<Client | null> {
    const db = getDb()
    const result = await db.query(
      `SELECT c.*
       FROM tenants t
       JOIN clients c ON c.id = t.client_id
       WHERE t.id = $1`,
      [tenantId]
    )
    return result.rows.length > 0 ? this.formatClient(result.rows[0]) : null
  }

  async listSubscriptions(filter?: { clientId?: string; tenantId?: string }): Promise<ServiceSubscription[]> {
    const db = getDb()
    const conditions: string[] = []
    const params: unknown[] = []

    if (filter?.clientId) {
      params.push(filter.clientId)
      conditions.push(`client_id = $${params.length}`)
    }
    if (filter?.tenantId) {
      params.push(filter.tenantId)
      conditions.push(`tenant_id = $${params.length}`)
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''
    const result = await db.query(
      `SELECT * FROM service_subscriptions ${whereClause} ORDER BY created_at DESC`,
      params
    )
    return result.rows.map((row) => this.formatSubscription(row))
  }

  async getActiveSubscriptionForTenant(tenantId: string): Promise<ServiceSubscription | null> {
    const db = getDb()
    const result = await db.query(
      `SELECT * FROM service_subscriptions
       WHERE tenant_id = $1
         AND status IN ('ACTIVE', 'PENDING', 'REQUEST_ONLY')
       ORDER BY created_at ASC
       LIMIT 1`,
      [tenantId]
    )
    return result.rows.length > 0 ? this.formatSubscription(result.rows[0]) : null
  }

  async listEntitlements(filter?: { tenantId?: string; subscriptionId?: string }): Promise<Entitlement[]> {
    const db = getDb()
    const conditions: string[] = []
    const params: unknown[] = []

    if (filter?.tenantId) {
      params.push(filter.tenantId)
      conditions.push(`tenant_id = $${params.length}`)
    }
    if (filter?.subscriptionId) {
      params.push(filter.subscriptionId)
      conditions.push(`subscription_id = $${params.length}`)
    }

    const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : ''
    const result = await db.query(
      `SELECT * FROM entitlements ${whereClause} ORDER BY created_at DESC`,
      params
    )
    return result.rows.map((row) => this.formatEntitlement(row))
  }
}

export const operatingModelService = new OperatingModelService()
