-- Assets: replace plant_id + warehouse_id with a single location_id (plant OR
-- warehouse). For GRN auto-created assets, location_id comes from the PO
-- shipping_id. Recreates the asset views to use location_id.
-- Idempotent: safe to re-run.

BEGIN;

-- 1. New unified location column
ALTER TABLE assets ADD COLUMN IF NOT EXISTS location_id BIGINT;

-- 2. Backfill from old columns (prefer warehouse, else plant) when present
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name = 'assets' AND column_name = 'plant_id'
  ) THEN
    UPDATE assets
       SET location_id = COALESCE(warehouse_id, plant_id)
     WHERE location_id IS NULL;
  END IF;
END $$;

ALTER TABLE assets ALTER COLUMN location_id SET NOT NULL;

-- 3. Drop dependent views before removing old columns
DROP VIEW IF EXISTS v_assets CASCADE;
DROP VIEW IF EXISTS v_asset_expiry_alerts CASCADE;
DROP VIEW IF EXISTS v_asset_next_service CASCADE;

-- 4. Drop old columns (FK constraints/indexes drop with them)
DROP INDEX IF EXISTS idx_assets_plant_id;
ALTER TABLE assets DROP COLUMN IF EXISTS plant_id;
ALTER TABLE assets DROP COLUMN IF EXISTS warehouse_id;

-- 5. FK + index for the new column
ALTER TABLE assets DROP CONSTRAINT IF EXISTS fk_assets_location;
ALTER TABLE assets
  ADD CONSTRAINT fk_assets_location FOREIGN KEY (location_id) REFERENCES locations(id);
CREATE INDEX IF NOT EXISTS idx_assets_location_id ON assets(location_id);

-- 6. Recreate views using location_id (join locations regardless of type)
CREATE VIEW v_asset_expiry_alerts 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,
    'AMC'::text AS alert_type,
    amc.id AS reference_id,
    amc.contract_no AS reference_no,
    v.vendor_name AS party_name,
    amc.end_date AS expiry_date,
    (amc.end_date - CURRENT_DATE) AS days_remaining,
    CASE
      WHEN (amc.end_date - CURRENT_DATE) <= 0 THEN 'EXPIRED'
      WHEN (amc.end_date - CURRENT_DATE) <= 30 THEN 'CRITICAL'
      WHEN (amc.end_date - CURRENT_DATE) <= 60 THEN 'WARNING'
      WHEN (amc.end_date - CURRENT_DATE) <= 90 THEN 'INFO'
    END AS alert_level
  FROM asset_amc_contracts amc
  JOIN assets a ON a.id = amc.asset_id
  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
  LEFT JOIN vendors v ON v.id = amc.vendor_id
  WHERE amc.is_active = TRUE AND amc.deleted_at IS NULL AND a.deleted_at IS NULL
    AND (amc.end_date - CURRENT_DATE) <= 90

UNION ALL

  SELECT
    a.id, a.asset_code, a.asset_name, ac_cat.name, loc.name, d.name,
    'INSURANCE'::text, ins.id, ins.policy_no, ins.insurer_name,
    ins.policy_end_date, (ins.policy_end_date - CURRENT_DATE),
    CASE
      WHEN (ins.policy_end_date - CURRENT_DATE) <= 0 THEN 'EXPIRED'
      WHEN (ins.policy_end_date - CURRENT_DATE) <= 30 THEN 'CRITICAL'
      WHEN (ins.policy_end_date - CURRENT_DATE) <= 60 THEN 'WARNING'
      WHEN (ins.policy_end_date - CURRENT_DATE) <= 90 THEN 'INFO'
    END
  FROM asset_insurance_policies ins
  JOIN assets a ON a.id = ins.asset_id
  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
  WHERE ins.is_active = TRUE AND ins.deleted_at IS NULL AND a.deleted_at IS NULL
    AND (ins.policy_end_date - CURRENT_DATE) <= 90

UNION ALL

  SELECT
    a.id, a.asset_code, a.asset_name, ac_cat.name, loc.name, d.name,
    'WARRANTY'::text, a.id, a.asset_code, 'Manufacturer Warranty'::varchar,
    a.warranty_expiry_date, (a.warranty_expiry_date - CURRENT_DATE),
    CASE
      WHEN (a.warranty_expiry_date - CURRENT_DATE) <= 0 THEN 'EXPIRED'
      WHEN (a.warranty_expiry_date - CURRENT_DATE) <= 30 THEN 'CRITICAL'
      WHEN (a.warranty_expiry_date - CURRENT_DATE) <= 60 THEN 'WARNING'
      WHEN (a.warranty_expiry_date - CURRENT_DATE) <= 90 THEN 'INFO'
    END
  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
  WHERE a.warranty_expiry_date IS NOT NULL AND a.deleted_at IS NULL
    AND (a.warranty_expiry_date - CURRENT_DATE) <= 90;

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 * 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');

CREATE VIEW v_assets AS
SELECT
  a.id,
  a.asset_code,
  a.asset_name,
  ac.name AS category_name,
  a.brand_model,
  a.serial_number,
  a.status,
  a.condition,
  a.purchase_date,
  a.purchase_cost,
  a.warranty_expiry_date,
  loc.name AS location_name,
  loc.type AS location_type,
  d.name AS department_name,
  a.location_detail,
  u.full_name AS assigned_to,
  v.vendor_name AS supplier,
  a.qr_code_value,
  amc.contract_no AS amc_contract_no,
  amc.end_date AS amc_expiry_date,
  amc.contract_type AS amc_type,
  amcv.vendor_name AS amc_vendor,
  (amc.end_date - CURRENT_DATE) AS amc_days_remaining,
  ins.policy_no AS insurance_policy_no,
  ins.policy_end_date AS insurance_expiry_date,
  ins.insurer_name,
  ins.sum_insured,
  (ins.policy_end_date - CURRENT_DATE) AS insurance_days_remaining,
  lsv.visit_date AS last_service_date,
  lsv.next_service_date,
  (lsv.next_service_date - CURRENT_DATE) AS service_due_in_days,
  a.created_at
FROM assets a
JOIN item_categories ac ON ac.id = a.item_category_id
JOIN locations loc ON loc.id = a.location_id
LEFT JOIN departments d ON d.id = a.department_id
LEFT JOIN users u ON u.id = a.assigned_to_user_id
LEFT JOIN vendors v ON v.id = a.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
LEFT JOIN vendors amcv ON amcv.id = amc.vendor_id
LEFT JOIN asset_insurance_policies ins
  ON ins.asset_id = a.id AND ins.is_active = TRUE AND ins.deleted_at IS NULL
LEFT JOIN LATERAL (
  SELECT visit_date, next_service_date
  FROM asset_service_visits
  WHERE asset_id = a.id AND deleted_at IS NULL
  ORDER BY visit_date DESC LIMIT 1
) lsv ON TRUE
WHERE a.deleted_at IS NULL;

COMMIT;

-- Verify:
-- SELECT column_name FROM information_schema.columns
--  WHERE table_name = 'assets'
--    AND column_name IN ('location_id','plant_id','warehouse_id');