import { Pool } from 'pg'
import refreshTokenHelper from "../../utils/refreshTokenHelper"

// Dashboard "SameDay Versand" card: every open fulfillment order of a SameDay
// merchant (C_BPartner.isPrime = 'Y' on the partner linked to the order's
// AD_Org) shipped via DHL/DPD that must still be commissioned today.
//
// An order counts when either
//   a) it already has a (non-voided) customer shipment that is NOT commissioned, or
//   b) it has no shipment yet but open item lines that are fully fulfillable
//      from stock (same stock rule as openso-generation-queue.get.ts).
// Due orders without shipment that are NOT fulfillable (missing on-hand qty)
// are returned separately in `unfulfillable` and do not count. That list is
// limited to orders created within the last UNFULFILLABLE_MAX_AGE_DAYS so old
// stock-blocked leftovers do not pile up on the card forever.
// Every order is due at its merchant's business-day cut-off (Europe/Berlin, Mon–Fri):
// the cut-off is C_BPartner.CutOffTime of the org's partner (editable on the partner
// edit page, Fulfillment tab → Kommissionierung), DEFAULT_CUTOFF when unset. Created on
// a weekday until the cut-off → due that day, otherwise the next weekday's cut-off
// (Fri after cut-off / Sat / Sun → Monday).
// Each merchant has its own current deadline (today's cut-off on a weekday, Monday's
// on a weekend); orders created after it belong to the next day and are excluded.
// Rows whose own due time has passed are overdue (`isOverdue`). The card header
// counts down to the NEAREST upcoming deadline across the enabled merchants
// (`cutoffAt` / `cutoff`), or the latest one when all have passed; `cutoffs`
// lists every merchant's cut-off.
//
// iDempiere stores `created` as Berlin wall-clock (timestamp without tz), so the
// cutoff is built as a Berlin wall-clock value too. `isprime` / `cutofftime` are
// read through to_jsonb() so the query keeps working until the columns exist.
const DEFAULT_CUTOFF = '14:00'
const UNFULFILLABLE_MAX_AGE_DAYS = 4

const NOW_BERLIN = `(now() AT TIME ZONE 'Europe/Berlin')`

// Current deadline as Berlin wall-clock for a cut-off `timeExpr` (a time-typed SQL
// expression): today's cut-off on a weekday, the coming Monday's on Saturday
// (+2 days) / Sunday (+1 day).
const CURRENT_DEADLINE_SQL = (timeExpr: string) => `(
  date_trunc('day', ${NOW_BERLIN})
  + (CASE EXTRACT(ISODOW FROM ${NOW_BERLIN})::int WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 0 END) * interval '1 day'
  + ${timeExpr}
)`

// Due time of an order created at `col` (Berlin wall-clock): same day if created
// on a weekday until the cut-off, otherwise the next weekday's cut-off
// (Fri → +3, Sat → +2, Sun/Mon–Thu → +1 day).
const ORDER_DUE_SQL = (col: string, timeExpr: string) => `(
  CASE WHEN EXTRACT(ISODOW FROM ${col})::int <= 5 AND ${col}::time <= ${timeExpr}
       THEN date_trunc('day', ${col}) + ${timeExpr}
       ELSE date_trunc('day', ${col})
            + (CASE EXTRACT(ISODOW FROM ${col})::int WHEN 5 THEN 3 WHEN 6 THEN 2 ELSE 1 END) * interval '1 day'
            + ${timeExpr}
  END
)`

// Merchant cut-off: C_BPartner.CutOffTime (iDempiere Time column = timestamp
// '1970-01-01 HH:MM:SS') of the org's partner, else the default ($2 = 'HH:MM').
const ORG_CUTOFF_SQL = `COALESCE((to_jsonb(bp) ->> 'cutofftime')::timestamp::time, $2::time)`

const handleFunc = async (event: any) => {
  const config = useRuntimeConfig()
  const dbConfig = {
    host: config.pgHost,
    port: parseInt(config.pgPort || '5432'),
    database: config.pgDatabase,
    user: config.pgUser,
    password: config.pgPassword,
  }
  if (!dbConfig.host || !dbConfig.database || !dbConfig.user || !dbConfig.password) {
    throw createError({ statusCode: 500, message: 'PostgreSQL credentials not configured. Please set PG_HOST, PG_DATABASE, PG_USER, PG_PASSWORD in .env' })
  }

  // Limited roles (FrontendMenu == 'c') only see their own organization.
  const roleCookie = getCookie(event, 'logship_role')
  let menuType: string = 'Menu'
  try {
    const role = roleCookie ? JSON.parse(roleCookie) : null
    menuType = role?.FrontendMenu?.id || role?.FrontendMenu || 'Menu'
  } catch {}
  const orgIdCookie = getCookie(event, 'logship_organization_id')
  const orgIdNum = orgIdCookie ? parseInt(orgIdCookie) : NaN
  const orgFilter: number | null = (menuType === 'c' && Number.isFinite(orgIdNum)) ? orgIdNum : null

  const pool = new Pool(dbConfig)
  try {
    const sql = `
      WITH prime_orgs AS (
        SELECT org.ad_org_id, org.name,
               ${ORG_CUTOFF_SQL} AS cutoff,
               ${CURRENT_DEADLINE_SQL(ORG_CUTOFF_SQL)} AS deadline
        FROM ad_org org
        JOIN c_bpartner bp ON bp.c_bpartner_id = org.c_bpartner_id
        WHERE org.ad_client_id = 1000000
          AND to_jsonb(bp) ->> 'isprime' = 'Y'
          AND ($1::int IS NULL OR org.ad_org_id = $1)
      ),
      stock AS (
        SELECT s.m_product_id,
               l.m_warehouse_id,
               SUM(s.qtyonhand) AS qtyonhand
        FROM m_storage s
        JOIN m_locator l ON l.m_locator_id = s.m_locator_id
        LEFT JOIN m_locatortype lt ON lt.m_locatortype_id = l.m_locatortype_id
        WHERE lt.m_locatortype_id IS NULL
           OR lt.isavailableforshipping = 'Y'
           OR lt.isavailableforreplenishment = 'Y'
           OR lt.isavailableforreservation = 'Y'
        GROUP BY s.m_product_id, l.m_warehouse_id
      ),
      -- latest uncommissioned customer shipment per order
      open_ship AS (
        SELECT DISTINCT ON (io.c_order_id) io.c_order_id, io.documentno, io.m_inout_id
        FROM m_inout io
        WHERE io.ad_client_id   = 1000000
          AND io.issotrx        = 'Y'
          AND io.movementtype   = 'C-'
          AND io.docstatus NOT IN ('VO','RE')
          AND io.iscommissioned = 'N'
          AND io.ad_org_id IN (SELECT ad_org_id FROM prime_orgs)
        ORDER BY io.c_order_id, io.created DESC
      ),
      candidates AS (
        SELECT o.c_order_id, o.documentno, o.created, o.m_warehouse_id,
               o.ad_org_id, o.c_bpartner_id,
               po.name AS org_name,
               po.cutoff, po.deadline,
               sh.m_shipper_id AS shipper_id,
               sh.name AS shipper_name,
               COALESCE(bp.name, '') AS partner_name,
               os.documentno AS open_shipment_no,
               os.m_inout_id AS open_shipment_id
        FROM c_order o
        JOIN prime_orgs po ON po.ad_org_id = o.ad_org_id
        JOIN m_shipper sh ON sh.m_shipper_id = o.m_shipper_id
        LEFT JOIN c_bpartner bp ON bp.c_bpartner_id = o.c_bpartner_id
        LEFT JOIN open_ship os ON os.c_order_id = o.c_order_id
        WHERE o.issotrx = 'Y'
          AND o.docstatus IN ('CO','CL')
          AND o.ad_client_id = 1000000
          AND o.isfulfillmentorder = 'Y'
          AND o.created <= po.deadline
          AND ${ORDER_DUE_SQL('o.created', 'po.cutoff')} <= po.deadline
          AND sh.name ~* '^(DHL|DPD)'
          AND sh.name !~* 'FBA'
          AND (
                os.c_order_id IS NOT NULL
                OR (
                  -- no shipment yet, but still open item lines
                  NOT EXISTS (
                    SELECT 1 FROM m_inout io
                    WHERE io.c_order_id = o.c_order_id
                      AND io.issotrx = 'Y'
                      AND io.docstatus NOT IN ('VO','RE')
                  )
                  AND EXISTS (
                    SELECT 1
                    FROM c_orderline ol
                    JOIN m_product p ON p.m_product_id = ol.m_product_id
                    WHERE ol.c_order_id = o.c_order_id
                      AND p.producttype = 'I'
                      AND (ol.qtyordered - COALESCE(ol.qtydelivered, 0)) > 0
                  )
                )
              )
      )
      SELECT cd.c_order_id AS id,
             cd.documentno,
             to_char(cd.created, 'YYYY-MM-DD"T"HH24:MI:SS') AS created,
             to_char(${ORDER_DUE_SQL('cd.created', 'cd.cutoff')}, 'YYYY-MM-DD"T"HH24:MI:SS') AS due_at,
             ${NOW_BERLIN} > ${ORDER_DUE_SQL('cd.created', 'cd.cutoff')} AS is_overdue,
             to_char(cd.cutoff, 'HH24:MI') AS cutoff,
             to_char(cd.deadline, 'YYYY-MM-DD"T"HH24:MI:SS') AS deadline_at,
             cd.org_name,
             cd.ad_org_id,
             cd.c_bpartner_id,
             cd.m_warehouse_id,
             cd.partner_name,
             cd.shipper_id,
             cd.shipper_name,
             cd.open_shipment_no,
             cd.open_shipment_id,
             -- orders without shipment must be fully fulfillable from stock;
             -- the others are returned separately as "missing stock"
             (cd.open_shipment_no IS NOT NULL
              OR NOT EXISTS (
              SELECT 1
              FROM c_orderline ol
              JOIN m_product p ON p.m_product_id = ol.m_product_id
              LEFT JOIN stock st
                ON st.m_product_id   = ol.m_product_id
               AND st.m_warehouse_id = cd.m_warehouse_id
              WHERE ol.c_order_id = cd.c_order_id
                AND p.producttype = 'I'
                AND (ol.qtyordered - COALESCE(ol.qtydelivered, 0)) > 0
              GROUP BY ol.m_product_id
              HAVING SUM(ol.qtyordered - COALESCE(ol.qtydelivered, 0)) > COALESCE(MAX(st.qtyonhand), 0)
            )) AS is_fulfillable,
             cd.created >= ${NOW_BERLIN} - ($3::int * interval '1 day') AS is_recent
      FROM candidates cd
      ORDER BY cd.created ASC, cd.c_order_id ASC
    `
    // One row per enabled merchant: its cut-off and current deadline (+ the Berlin clock).
    const clockSql = `
      SELECT org.ad_org_id, org.name AS org_name,
             to_char(${ORG_CUTOFF_SQL}, 'HH24:MI') AS cutoff,
             (to_jsonb(bp) ->> 'cutofftime') IS NOT NULL AS is_custom,
             to_char(${CURRENT_DEADLINE_SQL(ORG_CUTOFF_SQL)}, 'YYYY-MM-DD"T"HH24:MI:SS') AS deadline_at,
             ${NOW_BERLIN} > ${CURRENT_DEADLINE_SQL(ORG_CUTOFF_SQL)} AS past_cutoff,
             to_char(${NOW_BERLIN}, 'YYYY-MM-DD"T"HH24:MI:SS') AS now_local
      FROM ad_org org
      JOIN c_bpartner bp ON bp.c_bpartner_id = org.c_bpartner_id
      WHERE org.ad_client_id = 1000000
        AND to_jsonb(bp) ->> 'isprime' = 'Y'
        AND ($1::int IS NULL OR org.ad_org_id = $1)
      ORDER BY deadline_at ASC, org.name ASC
    `

    const [result, clock] = await Promise.all([
      pool.query(sql, [orgFilter, DEFAULT_CUTOFF, UNFULFILLABLE_MAX_AGE_DAYS]),
      pool.query(clockSql, [orgFilter, DEFAULT_CUTOFF])
    ])

    const cutoffs = clock.rows.map((r: any) => ({
      orgId: Number(r.ad_org_id),
      org: r.org_name || '',
      cutoff: r.cutoff,
      isCustom: !!r.is_custom,
      deadlineAt: r.deadline_at,
      isPast: !!r.past_cutoff
    }))
    // Header target: the nearest deadline still ahead, else the latest one that passed.
    const upcoming = cutoffs.filter(c => !c.isPast)
    const target = upcoming[0] ?? cutoffs[cutoffs.length - 1] ?? null
    // Merchants sharing the targeted deadline (null when it is everyone's deadline)
    const targetOrgs = target ? cutoffs.filter(c => c.deadlineAt === target.deadlineAt).map(c => c.org) : []
    const cutoffOrg = target && targetOrgs.length < cutoffs.length ? targetOrgs.join(', ') : null
    const nowLocal = clock.rows[0]?.now_local || null

    const rows = result.rows.map((r: any) => ({
      id: Number(r.id),
      documentNo: r.documentno,
      created: r.created,
      dueAt: r.due_at,
      isOverdue: !!r.is_overdue,
      cutoff: r.cutoff,
      deadlineAt: r.deadline_at,
      orgName: r.org_name || '',
      // Ids for the card's batch actions (generate per partner + warehouse, print, picklist).
      orgId: r.ad_org_id != null ? Number(r.ad_org_id) : null,
      partnerId: r.c_bpartner_id != null ? Number(r.c_bpartner_id) : null,
      warehouseId: r.m_warehouse_id != null ? Number(r.m_warehouse_id) : null,
      partnerName: r.partner_name || '',
      shipperId: r.shipper_id != null ? Number(r.shipper_id) : null,
      shipperName: r.shipper_name || '',
      shipmentNo: r.open_shipment_no || null,
      shipmentId: r.open_shipment_id != null ? Number(r.open_shipment_id) : null,
      isFulfillable: !!r.is_fulfillable,
      isRecent: !!r.is_recent
    }))
    const records = rows.filter(r => r.isFulfillable)
    // Due today but blocked by missing on-hand stock (no shipment yet) –
    // only orders created within the last UNFULFILLABLE_MAX_AGE_DAYS.
    const unfulfillable = rows.filter(r => !r.isFulfillable && r.isRecent)

    const byOrgMap: Record<string, { count: number; cutoff: string | null }> = {}
    records.forEach(r => {
      const e = byOrgMap[r.orgName] ?? (byOrgMap[r.orgName] = { count: 0, cutoff: r.cutoff ?? null })
      e.count++
    })
    const byOrg = Object.entries(byOrgMap)
      .map(([org, v]) => ({ org, count: v.count, cutoff: v.cutoff }))
      .sort((a, b) => b.count - a.count)

    return {
      defaultCutoff: DEFAULT_CUTOFF,
      cutoff: target?.cutoff ?? DEFAULT_CUTOFF,
      cutoffOrg,
      cutoffs,
      // true when the enabled merchants do not all share one cut-off
      hasMixedCutoffs: new Set(cutoffs.map(c => c.cutoff)).size > 1,
      nowLocal,
      cutoffAt: target?.deadlineAt ?? null,
      isPastCutoff: target ? target.isPast : false,
      count: records.length,
      withShipment: records.filter(r => r.shipmentNo).length,
      overdueCount: records.filter(r => r.isOverdue).length,
      byOrg,
      records,
      unfulfillable
    }
  } catch (err: any) {
    console.error('PostgreSQL query error:', err)
    throw createError({ statusCode: 500, message: `Database error: ${err.message}` })
  } finally {
    await pool.end()
  }
}

export default defineEventHandler(async (event) => {
  try {
    return await handleFunc(event)
  } catch (err: any) {
    try {
      await refreshTokenHelper(event)
      return await handleFunc(event)
    } catch (error: any) {
      console.error('Fatal error in prime-due-today:', error)
      throw error
    }
  }
})