import {
  createTenancy,
  type CreateTenantInput,
  type Membership,
  type Tenant,
  type TenancyStore,
} from '@platform-modules/tenancy'
import { createFkIsolation } from '@platform-modules/tenancy/isolation-fk'
import { createTriadRbac, triadSchema } from '@platform-modules/tenancy/rbac-triad'
import { and, eq, sql, type Db, type DbTx } from '@zync/db'
import { customers } from '../../../../../packages/db/src/schema/customers'
import { invoices } from '../../../../../packages/db/src/schema/invoices'
import { kbArticles, kbSpaces } from '../../../../../packages/db/src/schema/kb'
import { leads } from '../../../../../packages/db/src/schema/marketing'
import { ticketCategories } from '../../../../../packages/db/src/schema/support'
import { projects } from '../../../../../packages/db/src/schema/projects'
import { roles, tenantMemberships } from '../../../../../packages/db/src/schema/rbac'
import { taskStatuses, tasks } from '../../../../../packages/db/src/schema/tasks'
import { tenants } from '../../../../../packages/db/src/schema/tenants'

type DbLike = Db | DbTx

type TenantRow = {
  id: string
  slug: string
  name: string
  settings: Record<string, unknown> | null
  createdAt: Date
}

function mapTenantStatus(settings: Record<string, unknown> | null | undefined): Tenant['status'] {
  return settings?.suspended === true ? 'suspended' : 'active'
}

async function findOwnerId(db: DbLike, tenantId: string): Promise<string> {
  const [row] = await db
    .select({ userId: tenantMemberships.userId })
    .from(tenantMemberships)
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .where(
      and(
        eq(tenantMemberships.tenantId, tenantId),
        eq(tenantMemberships.status, 'active'),
        eq(roles.name, 'OWNER'),
      ),
    )
    .limit(1)

  return row?.userId ?? ''
}

async function mapTenant(db: DbLike, row: TenantRow): Promise<Tenant> {
  return {
    id: row.id,
    slug: row.slug,
    name: row.name,
    status: mapTenantStatus(row.settings),
    ownerId: await findOwnerId(db, row.id),
    createdAt: row.createdAt,
  }
}

async function findRoleId(db: DbLike, tenantId: string, roleKey: string): Promise<string> {
  const [row] = await db
    .select({ id: roles.id })
    .from(roles)
    .where(and(eq(roles.tenantId, tenantId), eq(roles.name, roleKey)))
    .limit(1)

  if (!row) {
    throw new Error(`Unknown tenant role "${roleKey}" for tenant ${tenantId}`)
  }

  return row.id
}

async function listMemberships(db: DbLike, tenantId: string): Promise<Membership[]> {
  const rows = await db
    .select({
      tenantId: tenantMemberships.tenantId,
      userId: tenantMemberships.userId,
      roleKey: roles.name,
      status: tenantMemberships.status,
    })
    .from(tenantMemberships)
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .where(eq(tenantMemberships.tenantId, tenantId))

  return rows.map((row) => ({
    tenantId: row.tenantId,
    userId: row.userId,
    roleKey: row.roleKey,
    status: row.status as Membership['status'],
  }))
}

export function createPlatformTenancyStore(
  db: DbLike,
  now: () => Date = () => new Date(),
): TenancyStore {
  return {
    async createTenant(input: CreateTenantInput): Promise<Tenant> {
      const [row] = await db
        .insert(tenants)
        .values({
          slug: input.slug,
          name: input.name,
          settings: input.status === 'suspended' ? { suspended: true } : {},
          createdAt: now(),
        })
        .returning({
          id: tenants.id,
          slug: tenants.slug,
          name: tenants.name,
          settings: tenants.settings,
          createdAt: tenants.createdAt,
        })

      if (!row) {
        throw new Error(`Failed to create tenant ${input.slug}`)
      }

      const [ownerRole] = await db
        .select({ id: roles.id })
        .from(roles)
        .where(and(eq(roles.tenantId, row.id), eq(roles.name, 'OWNER')))
        .limit(1)

      if (ownerRole) {
        await db
          .insert(tenantMemberships)
          .values({
            tenantId: row.id,
            userId: input.ownerId,
            roleId: ownerRole.id,
            status: 'active',
          })
          .onConflictDoNothing()
      }

      return {
        id: row.id,
        slug: row.slug,
        name: row.name,
        status: input.status ?? 'active',
        ownerId: input.ownerId,
        createdAt: row.createdAt,
      }
    },

    async getTenant(tenantId: string): Promise<Tenant | null> {
      const [row] = await db
        .select({
          id: tenants.id,
          slug: tenants.slug,
          name: tenants.name,
          settings: tenants.settings,
          createdAt: tenants.createdAt,
        })
        .from(tenants)
        .where(eq(tenants.id, tenantId))
        .limit(1)

      return row ? mapTenant(db, row as TenantRow) : null
    },

    async getTenantBySlug(slug: string): Promise<Tenant | null> {
      const [row] = await db
        .select({
          id: tenants.id,
          slug: tenants.slug,
          name: tenants.name,
          settings: tenants.settings,
          createdAt: tenants.createdAt,
        })
        .from(tenants)
        .where(eq(tenants.slug, slug))
        .limit(1)

      return row ? mapTenant(db, row as TenantRow) : null
    },

    async suspendTenant(tenantId: string): Promise<Tenant> {
      const rows = await db.execute<TenantRow>(sql`
        UPDATE tenants
        SET settings = jsonb_set(
          COALESCE(settings, '{}'::jsonb),
          '{suspended}',
          'true'::jsonb,
          true
        )
        WHERE id = ${tenantId}
        RETURNING id, slug, name, settings, created_at AS "createdAt"
      `)

      const row = rows[0]
      if (!row) {
        throw new Error(`Tenant ${tenantId} not found`)
      }

      return mapTenant(db, row)
    },

    async addMember(membership: Membership): Promise<Membership> {
      const roleId = await findRoleId(db, membership.tenantId, membership.roleKey)
      await db
        .insert(tenantMemberships)
        .values({
          tenantId: membership.tenantId,
          userId: membership.userId,
          roleId,
          status: membership.status,
        })
        .onConflictDoUpdate({
          target: [tenantMemberships.userId, tenantMemberships.tenantId],
          set: {
            roleId,
            status: membership.status,
          },
        })

      return membership
    },

    async removeMember(tenantId: string, userId: string): Promise<void> {
      await db
        .delete(tenantMemberships)
        .where(and(eq(tenantMemberships.tenantId, tenantId), eq(tenantMemberships.userId, userId)))
    },

    async setMemberRole(tenantId: string, userId: string, roleKey: string): Promise<Membership> {
      const roleId = await findRoleId(db, tenantId, roleKey)
      await db
        .update(tenantMemberships)
        .set({ roleId })
        .where(and(eq(tenantMemberships.tenantId, tenantId), eq(tenantMemberships.userId, userId)))

      const membership = await this.getMembership(tenantId, userId)
      if (!membership) {
        throw new Error(`Membership ${tenantId}:${userId} not found`)
      }

      return membership
    },

    async freezeMember(tenantId: string, userId: string): Promise<Membership> {
      await db
        .update(tenantMemberships)
        .set({ status: 'frozen' })
        .where(and(eq(tenantMemberships.tenantId, tenantId), eq(tenantMemberships.userId, userId)))

      const membership = await this.getMembership(tenantId, userId)
      if (!membership) {
        throw new Error(`Membership ${tenantId}:${userId} not found`)
      }

      return membership
    },

    listMembers(tenantId: string): Promise<Membership[]> {
      return listMemberships(db, tenantId)
    },

    async getMembership(tenantId: string, userId: string): Promise<Membership | null> {
      const [row] = await db
        .select({
          tenantId: tenantMemberships.tenantId,
          userId: tenantMemberships.userId,
          roleKey: roles.name,
          status: tenantMemberships.status,
        })
        .from(tenantMemberships)
        .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
        .where(and(eq(tenantMemberships.tenantId, tenantId), eq(tenantMemberships.userId, userId)))
        .limit(1)

      return row
        ? {
            tenantId: row.tenantId,
            userId: row.userId,
            roleKey: row.roleKey,
            status: row.status as Membership['status'],
          }
        : null
    },
  }
}

export async function syncPlatformTenancyRbac(
  db: DbLike,
  tenantId?: string,
): Promise<void> {
  const filter = tenantId ? sql`WHERE r.tenant_id = ${tenantId}` : sql``
  const triadRbac = createTriadRbac(db as never)

  await triadRbac.seedPermissionCatalog()

  await db.execute(sql`
    INSERT INTO tenancy_permissions (key)
    SELECT DISTINCT p.key
    FROM permissions p
    ON CONFLICT (key) DO NOTHING
  `)

  await db.execute(sql`
    INSERT INTO tenancy_roles (tenant_id, name, is_system_role)
    SELECT DISTINCT r.tenant_id, r.name, r.is_system_role
    FROM roles r
    ${filter}
    ON CONFLICT (tenant_id, name) DO UPDATE
    SET is_system_role = EXCLUDED.is_system_role
  `)

  await db.execute(sql`
    INSERT INTO tenancy_role_permissions (role_id, permission_id)
    SELECT DISTINCT tr.id, tp.id
    FROM roles r
    INNER JOIN role_permissions rp ON rp.role_id = r.id
    INNER JOIN permissions p ON p.id = rp.permission_id
    INNER JOIN tenancy_roles tr ON tr.tenant_id = r.tenant_id AND tr.name = r.name
    INNER JOIN tenancy_permissions tp ON tp.key = p.key
    ${filter}
    ON CONFLICT DO NOTHING
  `)
}

export function createPlatformTenancy(db: DbLike) {
  return createTenancy({
    store: createPlatformTenancyStore(db),
    rbac: createTriadRbac(db as never),
    isolation: createFkIsolation({
      tables: [
        customers,
        invoices,
        kbArticles,
        kbSpaces,
        leads,
        projects,
        tasks,
        taskStatuses,
        ticketCategories,
      ],
    }),
  })
}

export const platformTenancySchema = triadSchema