-- 1) GRN: warehouse_id → location_id (from PO shipping_id)
-- 2) Vendor bank details: only one active primary per vendor
-- 3) asset_service_visits: drop visit_number
-- Idempotent: safe to re-run.

BEGIN;

-- ---------------------------------------------------------------------------
-- 1. GRN warehouse_id → location_id
-- ---------------------------------------------------------------------------
DROP VIEW IF EXISTS v_grn CASCADE;

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'grn' AND column_name = 'warehouse_id'
  ) AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = 'public' AND table_name = 'grn' AND column_name = 'location_id'
  ) THEN
    ALTER TABLE grn RENAME COLUMN warehouse_id TO location_id;
  END IF;
END $$;

ALTER TABLE grn ADD COLUMN IF NOT EXISTS location_id BIGINT;

-- Prefer PO shipping_id; fall back to existing location_id for any orphan rows
UPDATE grn g
   SET location_id = po.shipping_id
  FROM purchase_orders po
 WHERE po.id = g.po_id
   AND po.shipping_id IS NOT NULL;

ALTER TABLE grn ALTER COLUMN location_id SET NOT NULL;

ALTER TABLE grn DROP CONSTRAINT IF EXISTS grn_warehouse_id_fkey;
ALTER TABLE grn DROP CONSTRAINT IF EXISTS grn_location_id_fkey;
ALTER TABLE grn
  ADD CONSTRAINT grn_location_id_fkey FOREIGN KEY (location_id) REFERENCES locations(id);

DROP INDEX IF EXISTS idx_grn_warehouse_id;
CREATE INDEX IF NOT EXISTS idx_grn_location_id ON grn(location_id);

CREATE VIEW v_grn AS
SELECT
  g.*,
  po.po_number,
  v.vendor_code,
  v.vendor_name,
  loc.code AS location_code,
  loc.name AS location_name,
  loc.type AS location_type
FROM grn g
JOIN purchase_orders po ON po.id = g.po_id
JOIN vendors v ON v.id = g.vendor_id
JOIN locations loc ON loc.id = g.location_id
WHERE g.deleted_at IS NULL;

-- ---------------------------------------------------------------------------
-- 2. Vendor bank: one active primary per vendor
-- ---------------------------------------------------------------------------
-- Demote duplicates (keep lowest id as primary)
WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER (PARTITION BY vendor_id ORDER BY id ASC) AS rn
  FROM vendor_bank_details
  WHERE is_primary = TRUE AND is_active = TRUE
)
UPDATE vendor_bank_details vbd
   SET is_primary = FALSE
  FROM ranked r
 WHERE vbd.id = r.id
   AND r.rn > 1;

DROP INDEX IF EXISTS uq_vendor_bank_one_primary;
CREATE UNIQUE INDEX uq_vendor_bank_one_primary
  ON vendor_bank_details (vendor_id)
  WHERE is_primary = TRUE AND is_active = TRUE;

-- ---------------------------------------------------------------------------
-- 3. Drop visit_number from asset_service_visits
-- ---------------------------------------------------------------------------
DROP VIEW IF EXISTS v_asset_next_service CASCADE;

ALTER TABLE asset_service_visits DROP COLUMN IF EXISTS visit_number;

CREATE VIEW v_asset_next_service AS
SELECT
  a.id AS asset_id,
  a.asset_code,
  a.asset_name,
  ac_cat.name AS category_name,
  loc.name AS location_name,
  d.name AS department_name,
  sv.id AS last_visit_id,
  sv.visit_date AS last_service_date,
  sv.visit_type AS last_visit_type,
  sv.next_service_date,
  (sv.next_service_date - CURRENT_DATE) AS days_to_next_service,
  CASE
    WHEN sv.next_service_date < CURRENT_DATE THEN 'OVERDUE'
    WHEN (sv.next_service_date - CURRENT_DATE) <= 7 THEN 'DUE_THIS_WEEK'
    WHEN (sv.next_service_date - CURRENT_DATE) <= 30 THEN 'DUE_THIS_MONTH'
    ELSE 'UPCOMING'
  END AS service_status,
  v.vendor_name AS service_vendor,
  amc.contract_no AS amc_contract_no,
  amc.end_date AS amc_end_date
FROM assets a
JOIN item_categories ac_cat ON ac_cat.id = a.item_category_id
JOIN locations loc ON loc.id = a.location_id
LEFT JOIN departments d ON d.id = a.department_id
JOIN LATERAL (
  SELECT id, visit_date, visit_type, next_service_date, vendor_id
  FROM asset_service_visits
  WHERE asset_id = a.id AND deleted_at IS NULL AND next_service_date IS NOT NULL
  ORDER BY visit_date DESC
  LIMIT 1
) sv ON TRUE
LEFT JOIN vendors v ON v.id = sv.vendor_id
LEFT JOIN asset_amc_contracts amc
  ON amc.asset_id = a.id AND amc.is_active = TRUE AND amc.deleted_at IS NULL
WHERE a.deleted_at IS NULL AND a.status NOT IN ('DISPOSED','SCRAPPED');

COMMIT;