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 a business-day cutoff (14:03 Europe/Berlin, Mon–Fri):
// created on a weekday until 14:03 → that day 14:03, otherwise the next
// weekday's 14:03 (Fri after 14:03 / Sat / Sun → Monday 14:03).
// The card targets the current deadline (`cutoffAt`): today 14:03 on a weekday
// (orders created after it belong to tomorrow and are excluded), Monday 14:03
// on a weekend (weekend orders count down to Monday). Rows whose own due time
// has passed are overdue (`isOverdue`, e.g. Friday leftovers on a weekend).
//
// iDempiere stores `created` as Berlin wall-clock (timestamp without tz), so the
// cutoff is built as a Berlin wall-clock value too. `isprime` is read through
// to_jsonb() so the query keeps working (returns nothing) until the column
// exists in iDempiere.
const CUTOFF = '14:03'
const UNFULFILLABLE_MAX_AGE_DAYS = 4

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

// Current deadline as Berlin wall-clock: today's cutoff on a weekday, the
// coming Monday's on Saturday (+2 days) / Sunday (+1 day).
const CURRENT_DEADLINE_SQL = (timeParam: 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'
  + ${timeParam}::time
)`

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

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 clock AS (
        SELECT ${CURRENT_DEADLINE_SQL('$2')} AS deadline
      ),
      prime_orgs AS (
        SELECT org.ad_org_id, org.name
        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,
               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
        CROSS JOIN clock c
        WHERE o.issotrx = 'Y'
          AND o.docstatus IN ('CO','CL')
          AND o.ad_client_id = 1000000
          AND o.isfulfillmentorder = 'Y'
          AND o.created <= c.deadline
          AND ${ORDER_DUE_SQL('o.created', '$2')} <= c.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', '$2')}, 'YYYY-MM-DD"T"HH24:MI:SS') AS due_at,
             ${NOW_BERLIN} > ${ORDER_DUE_SQL('cd.created', '$2')} AS is_overdue,
             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
    `
    const clockSql = `
      SELECT to_char(now() AT TIME ZONE 'Europe/Berlin', 'YYYY-MM-DD"T"HH24:MI:SS') AS now_local,
             to_char(${CURRENT_DEADLINE_SQL('$1')}, 'YYYY-MM-DD"T"HH24:MI:SS') AS cutoff_at,
             ${NOW_BERLIN} > ${CURRENT_DEADLINE_SQL('$1')} AS past_cutoff
    `

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

    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,
      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, number> = {}
    records.forEach(r => { byOrgMap[r.orgName] = (byOrgMap[r.orgName] || 0) + 1 })
    const byOrg = Object.entries(byOrgMap)
      .map(([org, count]) => ({ org, count }))
      .sort((a, b) => b.count - a.count)

    return {
      cutoff: CUTOFF,
      nowLocal: clock.rows[0]?.now_local || null,
      cutoffAt: clock.rows[0]?.cutoff_at || null,
      isPastCutoff: !!clock.rows[0]?.past_cutoff,
      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
    }
  }
})