-- Procurement Analytics Demo Data Seeder
-- Purpose: Seed realistic data to support Procurement Pulse Board, Supplier Risk Radar,
--          Cost Optimization Lab, Spend Intelligence Observatory, and KPI Cockpit.
-- Notes:
--  - Uses conditional DDL to align schema with analytics service expectations.
--  - Safe to run multiple times (idempotent patterns where feasible).

-- 1) Align schema with analytics service expectations
--    - Add total_amount and rfq_id to purchase_orders if missing
--    - Create minimal rfqs bridge table for request<->rfq mapping used by metrics
DO $$
BEGIN
	-- Add total_amount to purchase_orders if not exists
	IF NOT EXISTS (
		SELECT 1 FROM information_schema.columns
		WHERE table_name = 'purchase_orders' AND column_name = 'total_amount'
	) THEN
		EXECUTE 'ALTER TABLE purchase_orders ADD COLUMN total_amount numeric(14,2)';
	END IF;

	-- Add rfq_id to purchase_orders if not exists
	IF NOT EXISTS (
		SELECT 1 FROM information_schema.columns
		WHERE table_name = 'purchase_orders' AND column_name = 'rfq_id'
	) THEN
		EXECUTE 'ALTER TABLE purchase_orders ADD COLUMN rfq_id uuid NULL REFERENCES rfq_headers(rfq_id) ON DELETE SET NULL';
	END IF;
END
$$;

-- Create rfqs mapping table if not exists (bridge for analytics)
CREATE TABLE IF NOT EXISTS rfqs (
	id uuid PRIMARY KEY,                       -- maps to rfq_headers.rfq_id
	procurement_request_id uuid NULL REFERENCES procurement_requests(procurement_request_id) ON DELETE CASCADE,
	vendor_id uuid NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE,
	sent_at timestamptz NULL
);

-- Ensure columns exist in case rfqs table already exists from prior runs without them
DO $$
BEGIN
	IF NOT EXISTS (
		SELECT 1 FROM information_schema.columns WHERE table_name = 'rfqs' AND column_name = 'vendor_id'
	) THEN
		EXECUTE 'ALTER TABLE rfqs ADD COLUMN vendor_id uuid NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE';
	END IF;
	IF NOT EXISTS (
		SELECT 1 FROM information_schema.columns WHERE table_name = 'rfqs' AND column_name = 'sent_at'
	) THEN
		EXECUTE 'ALTER TABLE rfqs ADD COLUMN sent_at timestamptz NULL';
	END IF;
	-- Relax NOT NULL on procurement_request_id if present
	IF EXISTS (
		SELECT 1 FROM information_schema.columns WHERE table_name = 'rfqs' AND column_name = 'procurement_request_id' AND is_nullable = 'NO'
	) THEN
		EXECUTE 'ALTER TABLE rfqs ALTER COLUMN procurement_request_id DROP NOT NULL';
	END IF;
END $$;

-- 2) Ensure some vendor compliance docs exist (for Supplier Risk compliance metric)
--    Add documents with mixed validity windows
INSERT INTO vendor_documents (vendor_id, filetype, doc_type, file_url, validity, isDeleted, updatedAt)
SELECT v.vendor_id, 'pdf', 'business_license', 'https://example.com/docs/' || replace(v.vendorname, ' ', '_') || '_license.pdf',
			 current_date + (CASE WHEN v.vendorname ILIKE '%Island%' THEN 15 ELSE 180 END), FALSE, now()
FROM vendors v
WHERE v.vendorname IN ('Pacific IT Solutions','Island Supplies Ltd','Coral Tech Traders')
	AND NOT EXISTS (
		SELECT 1 FROM vendor_documents d
		WHERE d.vendor_id = v.vendor_id AND d.doc_type = 'business_license'
	);

INSERT INTO vendor_documents (vendor_id, filetype, doc_type, file_url, validity, isDeleted, updatedAt)
SELECT v.vendor_id, 'pdf', 'insurance_certificate', 'https://example.com/docs/' || replace(v.vendorname, ' ', '_') || '_insurance.pdf',
			 current_date + (CASE WHEN v.vendorname ILIKE '%Pacific%' THEN 365 ELSE -10 END), FALSE, now()
FROM vendors v
WHERE v.vendorname IN ('Pacific IT Solutions','Island Supplies Ltd','Coral Tech Traders')
	AND NOT EXISTS (
		SELECT 1 FROM vendor_documents d
		WHERE d.vendor_id = v.vendor_id AND d.doc_type = 'insurance_certificate'
	);

-- 3) Map a couple of procurement requests to RFQs so approval/fulfilment metrics can resolve
--    Choose logical pairings from demoData/combined.sql
WITH rfq_net AS (
	SELECT rfq_id FROM rfq_headers WHERE title = 'Network Switches Supply' LIMIT 1
), rfq_furn AS (
	SELECT rfq_id FROM rfq_headers WHERE title = 'Office Furniture' LIMIT 1
), req_net AS (
	SELECT procurement_request_id FROM procurement_requests WHERE description = 'Network Security Software Licenses' LIMIT 1
), req_furn AS (
	SELECT procurement_request_id FROM procurement_requests WHERE description = 'Office Furniture - Ergonomic Chairs' LIMIT 1
)
INSERT INTO rfqs (id, procurement_request_id)
SELECT r1.rfq_id, q1.procurement_request_id FROM rfq_net r1 CROSS JOIN req_net q1
WHERE r1.rfq_id IS NOT NULL AND q1.procurement_request_id IS NOT NULL
	AND NOT EXISTS (SELECT 1 FROM rfqs x WHERE x.id = r1.rfq_id)
UNION ALL
SELECT r2.rfq_id, q2.procurement_request_id FROM rfq_furn r2 CROSS JOIN req_furn q2
WHERE r2.rfq_id IS NOT NULL AND q2.procurement_request_id IS NOT NULL
	AND NOT EXISTS (SELECT 1 FROM rfqs x WHERE x.id = r2.rfq_id);

-- Also backfill vendor invitations into rfqs to support Supplier Risk responsiveness
INSERT INTO rfqs (id, vendor_id, sent_at)
SELECT i.rfq_id, i.vendor_id, COALESCE(i.sent_at, now() - interval '20 days')
FROM rfq_invites i
LEFT JOIN rfqs r ON r.id = i.rfq_id AND r.vendor_id = i.vendor_id
WHERE r.id IS NULL;

-- 4) Assign rfq_id on purchase orders by vendor grouping to simulate RFQ-to-Order path
UPDATE purchase_orders po SET rfq_id = (
	SELECT rfq_id FROM rfq_headers WHERE title = 'Network Switches Supply' LIMIT 1
) WHERE po.vendor_id = (SELECT vendor_id FROM vendors WHERE vendorName = 'Coral Tech Traders' LIMIT 1)
	AND rfq_id IS DISTINCT FROM (SELECT rfq_id FROM rfq_headers WHERE title = 'Network Switches Supply' LIMIT 1);

UPDATE purchase_orders po SET rfq_id = (
	SELECT rfq_id FROM rfq_headers WHERE title = 'Office Furniture' LIMIT 1
) WHERE po.vendor_id = (SELECT vendor_id FROM vendors WHERE vendorName = 'Island Supplies Ltd' LIMIT 1)
	AND rfq_id IS DISTINCT FROM (SELECT rfq_id FROM rfq_headers WHERE title = 'Office Furniture' LIMIT 1);

-- Split Pacific IT Solutions orders between both RFQs for variety
UPDATE purchase_orders po SET rfq_id = (
	SELECT rfq_id FROM rfq_headers WHERE title = 'Network Switches Supply' LIMIT 1
)
WHERE po.vendor_id = (SELECT vendor_id FROM vendors WHERE vendorName = 'Pacific IT Solutions' LIMIT 1)
	AND po.order_number IN ('PO-2025-004','PO-2025-010','PO-2025-013')
	AND rfq_id IS DISTINCT FROM (SELECT rfq_id FROM rfq_headers WHERE title = 'Network Switches Supply' LIMIT 1);

UPDATE purchase_orders po SET rfq_id = (
	SELECT rfq_id FROM rfq_headers WHERE title = 'Office Furniture' LIMIT 1
)
WHERE po.vendor_id = (SELECT vendor_id FROM vendors WHERE vendorName = 'Pacific IT Solutions' LIMIT 1)
	AND po.order_number IN ('PO-2025-001','PO-2025-007')
	AND rfq_id IS DISTINCT FROM (SELECT rfq_id FROM rfq_headers WHERE title = 'Office Furniture' LIMIT 1);

-- 5) Populate total_amount on purchase orders to enable spend charts (values aligned with invoices where present)
UPDATE purchase_orders SET total_amount = 25000.00 WHERE order_number = 'PO-2025-001' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 8000.00  WHERE order_number = 'PO-2025-002' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 15000.00 WHERE order_number = 'PO-2025-003' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 12000.00 WHERE order_number = 'PO-2025-004' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 5200.00  WHERE order_number = 'PO-2025-005' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 3500.00  WHERE order_number = 'PO-2025-006' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 9200.00  WHERE order_number = 'PO-2025-007' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 820.00   WHERE order_number = 'PO-2025-008' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 6650.00  WHERE order_number = 'PO-2025-009' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 34500.00 WHERE order_number = 'PO-2025-010' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 4950.00  WHERE order_number = 'PO-2025-011' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 17500.00 WHERE order_number = 'PO-2025-012' AND (total_amount IS NULL OR total_amount = 0);
UPDATE purchase_orders SET total_amount = 7100.00  WHERE order_number = 'PO-2025-013' AND (total_amount IS NULL OR total_amount = 0);

-- 6) Normalise some procurement request statuses to match analytics ACTIVE/COMPLETED buckets
UPDATE procurement_requests
SET status = 'in progress'
WHERE description IN ('Network Security Software Licenses','Marketing Materials and Brochures')
	AND (status IS NULL OR lower(status) NOT IN ('in progress','ordered','approved','pending','completed','closed','fulfilled'));

UPDATE procurement_requests
SET status = 'ordered'
WHERE description IN ('Office Furniture - Ergonomic Chairs','Laptop Computers for Development Team')
	AND (status IS NULL OR lower(status) NOT IN ('ordered','completed','closed','fulfilled'));

-- 7) Spread updatedAt for timeline variety (older to newer)
UPDATE procurement_requests SET updatedAt = now() - interval '75 days' WHERE description = 'Office Furniture - Ergonomic Chairs' AND updatedAt > now() - interval '60 days';
UPDATE procurement_requests SET updatedAt = now() - interval '55 days' WHERE description = 'Laptop Computers for Development Team' AND updatedAt > now() - interval '40 days';
UPDATE procurement_requests SET updatedAt = now() - interval '35 days' WHERE description = 'Network Security Software Licenses' AND updatedAt > now() - interval '30 days';
UPDATE procurement_requests SET updatedAt = now() - interval '20 days' WHERE description = 'Conference Room AV Equipment' AND updatedAt > now() - interval '10 days';

-- 8) Ensure some goods receipts exist in recent window (already seeded in combined.sql)
--    Optionally add a recent receipt if none found for a PO to boost fulfilment rate
INSERT INTO goods_receipts (purchase_order_id, received_date, quantity_received, notes)
SELECT po.purchase_order_id, current_date - 7, 5, 'Recent partial receipt (demo)'
FROM purchase_orders po
LEFT JOIN goods_receipts gr ON gr.purchase_order_id = po.purchase_order_id
WHERE po.order_number IN ('PO-2025-007')
	AND gr.goods_receipt_id IS NULL;

-- 9) Ensure some invoices are paid to drive payment compliance
UPDATE vendor_invoices SET status = 'paid' WHERE invoice_number IN ('INV-CTT-2025-001','INV-CTT-2025-004') AND lower(status) <> 'paid';

-- End of procurement analytics demo seeding

