import { eq, and } from 'drizzle-orm'

import { db, sqlite } from './db-instance'
import { products } from './schema/products'
import { productCompositions } from './schema/productCompositions'
import { drugInfo } from './schema/drugInfo'
import { units } from './schema/units'
import { distributors } from './schema/distributors'

export type CompositionItem = {
  id: number
  drug: { id: number; name: string }
  quantity: string
  unit: { id: number; name: string }
}

export type CompositionInput = {
  drug_name: string
  quantity: string
  unit_name: string
}

interface ProductFields {
  name: string
  brand: string
  category: string
  procured_price: number
  mrp: number
  selling_price: number
  size: number
  reorder_level: number
  units_per_pack: number | null
  loose_sale_allowed: boolean
  hide_product_from_public: boolean
  hide_price_from_public: boolean
}

export type Product = ProductFields & {
  id: number
  distributor: { id: number; name: string } | null
  unit: { id: number; name: string }
  compositions: CompositionItem[]
}

type ProductOptionalKeys = 'units_per_pack' | 'loose_sale_allowed' | 'hide_product_from_public' | 'hide_price_from_public'
type ProductRequiredKeys = Exclude<keyof ProductFields, ProductOptionalKeys>

export type CreateProductInput =
  Pick<ProductFields, ProductRequiredKeys> &
  Partial<Pick<ProductFields, ProductOptionalKeys>> & {
    distributor_id?: number | null
    unit_name: string
    compositions: CompositionInput[]
  }

export type UpdateProductPatch = Partial<CreateProductInput>

export type ProductsRepo = {
  getProducts: (enterpriseId: number) => Promise<Product[]>
  getProductById: (id: number, enterpriseId: number) => Promise<Product | null>
  createProduct: (input: CreateProductInput, enterpriseId: number) => Promise<Product>
  updateProduct: (id: number, patch: UpdateProductPatch, enterpriseId: number) => Promise<Product | null>
  deleteProduct: (id: number, enterpriseId: number) => Promise<boolean>
}

function getOrCreateDrug(name: string): number {
  const existing = db.select().from(drugInfo).where(eq(drugInfo.name, name)).get()
  if (existing) return existing.id
  const created = db.insert(drugInfo).values({ name }).returning().get()
  return created.id
}

function getOrCreateUnit(name: string): number {
  const existing = db.select().from(units).where(eq(units.name, name)).get()
  if (existing) return existing.id
  const created = db.insert(units).values({ name }).returning().get()
  return created.id
}

function toProduct(
  row: typeof products.$inferSelect,
  distributor: { id: number; name: string } | null,
  unit: { id: number; name: string },
): Omit<Product, 'compositions'> {
  return {
    id: row.id,
    name: row.name,
    brand: row.brand,
    category: row.category,
    distributor,
    unit,
    procured_price: row.procuredPrice,
    mrp: row.mrp,
    selling_price: row.sellingPrice,
    size: row.size,
    reorder_level: row.reorderLevel,
    units_per_pack: row.unitsPerPack,
    loose_sale_allowed: row.looseSaleAllowed,
    hide_product_from_public: row.hideProductFromPublic,
    hide_price_from_public: row.hidePriceFromPublic,
  }
}

function fetchDistributor(distributorId: number | null): { id: number; name: string } | null {
  if (!distributorId) return null
  const d = db.select().from(distributors).where(eq(distributors.id, distributorId)).get()
  return d ? { id: d.id, name: d.agency } : null
}

function fetchUnit(unitId: number): { id: number; name: string } {
  const u = db.select().from(units).where(eq(units.id, unitId)).get()
  return u ? { id: u.id, name: u.name } : { id: unitId, name: String(unitId) }
}

function getCompositionsForProduct(productId: number): CompositionItem[] {
  const rows = db
    .select({
      id: productCompositions.id,
      quantity: productCompositions.quantity,
      drugId: drugInfo.id,
      drugName: drugInfo.name,
      unitId: units.id,
      unitName: units.name,
    })
    .from(productCompositions)
    .innerJoin(drugInfo, eq(productCompositions.drugInfoId, drugInfo.id))
    .innerJoin(units, eq(productCompositions.unitId, units.id))
    .where(eq(productCompositions.productId, productId))
    .all()

  return rows.map((r) => ({
    id: r.id,
    drug: { id: r.drugId, name: r.drugName },
    quantity: r.quantity,
    unit: { id: r.unitId, name: r.unitName },
  }))
}

function setCompositions(productId: number, comps: CompositionInput[]) {
  db.delete(productCompositions).where(eq(productCompositions.productId, productId)).run()
  for (const c of comps) {
    const drugId = getOrCreateDrug(c.drug_name)
    const unitId = getOrCreateUnit(c.unit_name)
    db.insert(productCompositions).values({
      productId: productId,
      drugInfoId: drugId,
      quantity: c.quantity,
      unitId,
    }).run()
  }
}

export function createProductsRepo(): { repo: ProductsRepo } {
  const repo: ProductsRepo = {
    getProducts(enterpriseId) {
      const rows = db.select().from(products).where(eq(products.enterpriseId, enterpriseId)).all()
      return Promise.resolve(
        rows.map((r) => {
          const distributor = fetchDistributor(r.distributorId)
          const unit = fetchUnit(r.unitId)
          return {
            ...toProduct(r, distributor, unit),
            compositions: getCompositionsForProduct(r.id),
          }
        }),
      )
    },

    getProductById(id, enterpriseId) {
      const row = db.select().from(products).where(and(eq(products.id, id), eq(products.enterpriseId, enterpriseId))).get()
      if (!row) return Promise.resolve(null)
      const distributor = fetchDistributor(row.distributorId)
      const unit = fetchUnit(row.unitId)
      return Promise.resolve({
        ...toProduct(row, distributor, unit),
        compositions: getCompositionsForProduct(row.id),
      })
    },

    createProduct(input, enterpriseId) {
      const result = sqlite.transaction(() => {
        const unitId = getOrCreateUnit(input.unit_name)

        const created = db
          .insert(products)
          .values({
            name: input.name,
            brand: input.brand,
            category: input.category,
            distributorId: input.distributor_id ?? null,
            unitId,
            procuredPrice: input.procured_price,
            mrp: input.mrp,
            sellingPrice: input.selling_price,
            size: input.size,
            reorderLevel: input.reorder_level,
            unitsPerPack: input.units_per_pack ?? null,
            looseSaleAllowed: input.loose_sale_allowed ?? false,
            hideProductFromPublic: input.hide_product_from_public ?? false,
            hidePriceFromPublic: input.hide_price_from_public ?? false,
            enterpriseId,
          })
          .returning()
          .get()

        setCompositions(created.id, input.compositions)

        return created
      })()

      const distributor = fetchDistributor(result.distributorId)
      const unit = fetchUnit(result.unitId)

      return Promise.resolve({
        ...toProduct(result, distributor, unit),
        compositions: getCompositionsForProduct(result.id),
      })
    },

    updateProduct(id, patch, enterpriseId) {
      const existing = db.select().from(products).where(and(eq(products.id, id), eq(products.enterpriseId, enterpriseId))).get()
      if (!existing) return Promise.resolve(null)

      sqlite.transaction(() => {
        const setData: Record<string, unknown> = {}

        if (patch.name !== undefined) setData.name = patch.name
        if (patch.brand !== undefined) setData.brand = patch.brand
        if (patch.category !== undefined) setData.category = patch.category
        if (patch.distributor_id !== undefined) setData.distributorId = patch.distributor_id ?? null
        if (patch.unit_name !== undefined) setData.unitId = getOrCreateUnit(patch.unit_name)
        if (patch.procured_price !== undefined) setData.procuredPrice = patch.procured_price
        if (patch.mrp !== undefined) setData.mrp = patch.mrp
        if (patch.selling_price !== undefined) setData.sellingPrice = patch.selling_price
        if (patch.size !== undefined) setData.size = patch.size
        if (patch.reorder_level !== undefined) setData.reorderLevel = patch.reorder_level
        if (patch.units_per_pack !== undefined) setData.unitsPerPack = patch.units_per_pack ?? null
        if (patch.loose_sale_allowed !== undefined) setData.looseSaleAllowed = patch.loose_sale_allowed
        if (patch.hide_product_from_public !== undefined) setData.hideProductFromPublic = patch.hide_product_from_public
        if (patch.hide_price_from_public !== undefined) setData.hidePriceFromPublic = patch.hide_price_from_public

        if (Object.keys(setData).length > 0) {
          db.update(products).set(setData).where(and(eq(products.id, id), eq(products.enterpriseId, enterpriseId))).run()
        }

        if (patch.compositions) {
          setCompositions(id, patch.compositions)
        }
      })()

      const updated = db.select().from(products).where(and(eq(products.id, id), eq(products.enterpriseId, enterpriseId))).get()!
      const distributor = fetchDistributor(updated.distributorId)
      const unit = fetchUnit(updated.unitId)

      return Promise.resolve({
        ...toProduct(updated, distributor, unit),
        compositions: getCompositionsForProduct(updated.id),
      })
    },

    deleteProduct(id, enterpriseId) {
      db.delete(productCompositions).where(eq(productCompositions.productId, id)).run()
      const deleted = db.delete(products).where(and(eq(products.id, id), eq(products.enterpriseId, enterpriseId))).returning({ id: products.id }).get()
      return Promise.resolve(Boolean(deleted))
    },
  }

  return { repo }
}