-- Default organizations
INSERT INTO orgs (org_id, name, dashboard_name, product_name, custom_domain) VALUES
    ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Cybex PTE LTD', 'Reserve Bank of Fiji Procurement Demo', 'Reserve Bank of Fiji Procurement Demo', 'rbf.cybexpte.com'),
    ('0194a8b2-5678-7123-8123-123456789abd'::uuid, 'Karunaratne Lawyers', 'Karunaratne Lawyers', 'Karunaratne Lawyers', 'mycase.cybexpte.com'),
    ('0194a8b2-9abc-7123-8123-123456789abe'::uuid, 'WAF', 'Water Authority of Fiji OHS Demo', 'Water Authority of Fiji OHS Demo', 'waf.cybexpte.com'),
    ('0194a8b2-def0-7123-8123-123456789abf'::uuid, 'Ministry of Employment', 'Ministry of Employment WORKCOM Demo', 'Ministry of Employment WORKCOM Demo', 'employment.cybexpte.com');

-- Default modules
INSERT INTO modules (module_id, name, description) VALUES
    ('0194a8b2-7777-7123-8123-123456789ac6'::uuid, 'Employees', 'Employee management module'),
    ('0194a8b2-8888-7123-8123-123456789ac7'::uuid, 'Cases', 'Case management module'),
    ('0194a8b2-9999-7123-8123-123456789ac8'::uuid, 'Time Entries & Expenses', 'Time tracking and expense management'),
    ('0194a8b2-aaaa-7123-8123-123456789ac9'::uuid, 'Documents', 'Document management module'),
    ('0194a8b2-bbbb-7123-8123-123456789aca'::uuid, 'Calendar', 'Calendar and scheduling module'),
    ('0194a8b2-cccc-7123-8123-123456789acb'::uuid, 'UserAccessManager', 'User and group access management module'),
    ('0194a8b2-7123-7123-8123-123456789abc'::uuid, 'Time Clocks', 'Time clock management module'),
    ('0194a8b2-7124-7123-8123-123456789abc'::uuid, 'Time Clock Aggregates', 'Time clock aggregates module'),
    ('01940882-1001-7123-8123-123456789001'::uuid, 'Procurement Pulse Board', 'Procurement analytics pulse dashboard module'),
    ('01940882-1002-7123-8123-123456789002'::uuid, 'Supplier Risk Radar', 'Supplier risk analytics dashboard module'),
    ('01940882-1003-7123-8123-123456789003'::uuid, 'Cost Optimization Lab', 'Procurement cost optimisation analytics dashboard'),
    ('01940882-1004-7123-8123-123456789004'::uuid, 'Spend Intelligence Observatory', 'Procurement spend intelligence dashboard'),
    ('01940882-1005-7123-8123-123456789005'::uuid, 'Procurement KPI Cockpit', 'Procurement KPI cockpit dashboard')
ON CONFLICT (module_id) DO NOTHING;

-- Procurement analytics dashboard modules
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description) VALUES
  (
    '01940882-1001-7123-8123-123456789001'::uuid,
    'procurement_pulse_board',
    '{"title":"Procurement Pulse Board","body":[{"type":"html","html":"<div data-controller=\"ProcurementPulseBoard\" class=\"space-y-6\">\n  <div class=\"flex flex-wrap items-center justify-between gap-3\">\n    <div class=\"flex flex-wrap gap-3\">\n      <label class=\"form-control w-40\">\n        <span class=\"label-text text-sm\">Range</span>\n        <select class=\"select select-bordered select-sm\" data-filter=\"range\">\n          <option value=\"30\">Last 30 days</option>\n          <option value=\"60\">Last 60 days</option>\n          <option value=\"90\" selected>Last 90 days</option>\n        </select>\n      </label>\n      <label class=\"form-control w-40\">\n        <span class=\"label-text text-sm\">Status</span>\n        <select class=\"select select-bordered select-sm\" data-filter=\"status\">\n          <option value=\"\">All statuses</option>\n          <option value=\"pending\">Pending</option>\n          <option value=\"approved\">Approved</option>\n          <option value=\"ordered\">Ordered</option>\n          <option value=\"completed\">Completed</option>\n        </select>\n      </label>\n    </div>\n    <button class=\"btn btn-sm btn-primary\" onclick=\"refreshProcurementPulseBoard()\">Refresh</button>\n  </div>\n  <div class=\"hidden text-sm text-error\" data-role=\"error-container\"></div>\n  <div class=\"grid grid-cols-1 md:grid-cols-2 xl:grid-cols-4 gap-4\" data-role=\"metric-cards\"></div>\n  <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n    <div class=\"card-body\">\n      <div class=\"flex justify-between items-center\">\n        <h3 class=\"card-title\">Stage Pipeline</h3>\n        <span class=\"text-xs opacity-70\" data-role=\"funnel-range\"></span>\n      </div>\n      <div class=\"h-56\">\n        <canvas data-role=\"funnel-chart\"></canvas>\n      </div>\n    </div>\n  </div>\n  <div class=\"grid grid-cols-1 lg:grid-cols-2 gap-6\">\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body\">\n        <div class=\"flex justify-between items-center\">\n          <h3 class=\"card-title\">Stage SLA Heatmap</h3>\n          <span class=\"text-xs opacity-70\" data-role=\"heatmap-range\"></span>\n        </div>\n        <div class=\"overflow-x-auto\">\n          <table class=\"table table-sm\" data-role=\"heatmap-table\">\n            <thead></thead>\n            <tbody></tbody>\n          </table>\n        </div>\n      </div>\n    </div>\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body\">\n        <div class=\"flex justify-between items-center\">\n          <h3 class=\"card-title\">Cycle Timeline</h3>\n          <span class=\"text-xs opacity-70\" data-role=\"timeline-range\"></span>\n        </div>\n        <div class=\"h-64\">\n          <canvas data-role=\"timeline-chart\"></canvas>\n        </div>\n      </div>\n    </div>\n  </div>\n</div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'::jsonb,
    NULL,
    NULL,
    'fa-chart-line',
    'Procurement Pulse Board',
    'Procurement Pulse Board'
  ),
  (
    '01940882-1002-7123-8123-123456789002'::uuid,
    'supplier_risk_radar',
    '{"title":"Supplier Risk Radar","body":[{"type":"html","html":"<div data-controller=\"SupplierRiskRadar\" class=\"space-y-6\">\n  <div class=\"flex flex-wrap items-center justify-between gap-3\">\n    <label class=\"form-control w-40\">\n      <span class=\"label-text text-sm\">Range</span>\n      <select class=\"select select-bordered select-sm\" data-filter=\"range\">\n        <option value=\"30\">Last 30 days</option>\n        <option value=\"60\">Last 60 days</option>\n        <option value=\"90\" selected>Last 90 days</option>\n      </select>\n    </label>\n    <button class=\"btn btn-sm btn-primary\" onclick=\"refreshSupplierRiskRadar()\">Refresh</button>\n  </div>\n  <div class=\"hidden text-sm text-error\" data-role=\"error-container\"></div>\n  <div class=\"grid grid-cols-1 lg:grid-cols-3 gap-6\">\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body space-y-2\" data-role=\"risk-summary\"></div>\n    </div>\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50 lg:col-span-2\">\n      <div class=\"card-body\">\n        <h3 class=\"card-title\">Risk Profile</h3>\n        <div class=\"h-72\">\n          <canvas data-role=\"risk-radar\"></canvas>\n        </div>\n      </div>\n    </div>\n  </div>\n  <div class=\"grid grid-cols-1 lg:grid-cols-2 gap-6\">\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body\">\n        <h3 class=\"card-title\">Spend Exposure by Region</h3>\n        <div class=\"h-64\">\n          <canvas data-role=\"risk-exposure\"></canvas>\n        </div>\n      </div>\n    </div>\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body space-y-3\" data-role=\"risk-alerts\"></div>\n    </div>\n  </div>\n</div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'::jsonb,
    NULL,
    NULL,
    'fa-shield-halved',
    'Supplier Risk Radar',
    'Supplier Risk Radar'
  ),
  (
    '01940882-1003-7123-8123-123456789003'::uuid,
    'cost_optimization_lab',
    '{"title":"Cost Optimization Lab","body":[{"type":"html","html":"<div data-controller=\"CostOptimizationLab\" class=\"space-y-6\">\n  <div class=\"flex flex-wrap items-center justify-between gap-3\">\n    <div class=\"flex flex-wrap gap-3\">\n      <label class=\"form-control w-32\">\n        <span class=\"label-text text-sm\">Range</span>\n        <select class=\"select select-bordered select-sm\" data-filter=\"range\">\n          <option value=\"30\">Last 30 days</option>\n          <option value=\"60\">Last 60 days</option>\n          <option value=\"90\" selected>Last 90 days</option>\n        </select>\n      </label>\n      <label class=\"form-control w-36\">\n        <span class=\"label-text text-sm\">Volume lever</span>\n        <input class=\"input input-bordered input-sm\" type=\"number\" step=\"0.01\" min=\"0\" data-filter=\"volumeFactor\" value=\"0.08\" />\n      </label>\n      <label class=\"form-control w-36\">\n        <span class=\"label-text text-sm\">Mix shift</span>\n        <input class=\"input input-bordered input-sm\" type=\"number\" step=\"0.01\" min=\"0\" data-filter=\"mixShift\" value=\"0.12\" />\n      </label>\n      <label class=\"form-control w-36\">\n        <span class=\"label-text text-sm\">Payment term</span>\n        <input class=\"input input-bordered input-sm\" type=\"number\" step=\"0.01\" min=\"0\" data-filter=\"paymentTerm\" value=\"0.015\" />\n      </label>\n    </div>\n    <button class=\"btn btn-sm btn-primary\" onclick=\"refreshCostOptimizationLab()\">Recalculate</button>\n  </div>\n  <div class=\"hidden text-sm text-error\" data-role=\"error-container\"></div>\n  <div class=\"grid grid-cols-1 md:grid-cols-2 xl:grid-cols-4 gap-4\" data-role=\"metric-cards\"></div>\n  <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n    <div class=\"card-body\">\n      <h3 class=\"card-title\">Value Realisation Waterfall</h3>\n      <div class=\"h-72\">\n        <canvas data-role=\"waterfall-chart\"></canvas>\n      </div>\n    </div>\n  </div>\n  <div class=\"grid grid-cols-1 lg:grid-cols-2 gap-6\">\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body space-y-3\" data-role=\"scenario-panel\"></div>\n    </div>\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body\">\n        <h3 class=\"card-title\">Variance by Category</h3>\n        <div class=\"overflow-x-auto\">\n          <table class=\"table table-sm\" data-role=\"variance-table\">\n            <thead>\n              <tr><th>Category</th><th>Budget</th><th>Actual</th><th>Variance</th><th>%</th></tr>\n            </thead>\n            <tbody></tbody>\n          </table>\n        </div>\n      </div>\n    </div>\n  </div>\n</div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'::jsonb,
    NULL,
    NULL,
    'fa-flask',
    'Cost Optimization Lab',
    'Cost Optimization Lab'
  ),
  (
    '01940882-1004-7123-8123-123456789004'::uuid,
    'spend_intelligence_observatory',
    '{"title":"Spend Intelligence Observatory","body":[{"type":"html","html":"<div data-controller=\"SpendIntelligenceObservatory\" class=\"space-y-6\">\n  <div class=\"flex flex-wrap items-center justify-between gap-3\">\n    <label class=\"form-control w-40\">\n      <span class=\"label-text text-sm\">Range</span>\n      <select class=\"select select-bordered select-sm\" data-filter=\"range\">\n        <option value=\"30\">Last 30 days</option>\n        <option value=\"60\">Last 60 days</option>\n        <option value=\"90\" selected>Last 90 days</option>\n      </select>\n    </label>\n    <button class=\"btn btn-sm btn-primary\" onclick=\"refreshSpendIntelligenceObservatory()\">Refresh</button>\n  </div>\n  <div class=\"hidden text-sm text-error\" data-role=\"error-container\"></div>\n  <div class=\"grid grid-cols-1 md:grid-cols-2 xl:grid-cols-4 gap-4\" data-role=\"metric-cards\"></div>\n  <div class=\"grid grid-cols-1 lg:grid-cols-2 gap-6\">\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body\">\n        <h3 class=\"card-title\">Spend Breakdown</h3>\n        <div class=\"h-64\">\n          <canvas data-role=\"breakdown-chart\"></canvas>\n        </div>\n      </div>\n    </div>\n    <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n      <div class=\"card-body\">\n        <h3 class=\"card-title\">Spend & Volume Trend</h3>\n        <div class=\"h-64\">\n          <canvas data-role=\"trend-chart\"></canvas>\n        </div>\n      </div>\n    </div>\n  </div>\n  <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n    <div class=\"card-body space-y-3\">\n      <h3 class=\"card-title\">Upcoming Sourcing Events</h3>\n      <div data-role=\"pipeline-list\" class=\"grid grid-cols-1 gap-3\"></div>\n    </div>\n  </div>\n</div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'::jsonb,
    NULL,
    NULL,
    'fa-chart-pie',
    'Spend Intelligence Observatory',
    'Spend Intelligence Observatory'
  ),
  (
    '01940882-1005-7123-8123-123456789005'::uuid,
    'procurement_kpi_cockpit',
    '{"title":"Procurement KPI Cockpit","body":[{"type":"html","html":"<div data-controller=\"ProcurementKpiCockpit\" class=\"space-y-6\">\n  <div class=\"flex flex-wrap items-center justify-between gap-3\">\n    <label class=\"form-control w-40\">\n      <span class=\"label-text text-sm\">Range</span>\n      <select class=\"select select-bordered select-sm\" data-filter=\"range\">\n        <option value=\"30\">Last 30 days</option>\n        <option value=\"60\">Last 60 days</option>\n        <option value=\"90\" selected>Last 90 days</option>\n      </select>\n    </label>\n    <button class=\"btn btn-sm btn-primary\" onclick=\"refreshProcurementKpiCockpit()\">Refresh</button>\n  </div>\n  <div class=\"hidden text-sm text-error\" data-role=\"error-container\"></div>\n  <div data-role=\"kpi-grid\" class=\"grid grid-cols-1 md:grid-cols-2 xl:grid-cols-3 gap-4\"></div>\n  <div class=\"card bg-base-100 shadow-md shadow-slate-400/50\">\n    <div class=\"card-body space-y-3\" data-role=\"kpi-insights\"></div>\n  </div>\n</div>"}],"footer":[{"type":"button","label":"Close","onClick":"closeModal"}]}'::jsonb,
    NULL,
    NULL,
    'fa-gauge',
    'Procurement KPI Cockpit',
    'Procurement KPI Cockpit'
  )
ON CONFLICT (module_id) DO UPDATE SET
    config = EXCLUDED.config,
    org_id = EXCLUDED.org_id,
    branch_id = EXCLUDED.branch_id,
    icon = EXCLUDED.icon,
    aria_label = EXCLUDED.aria_label,
    description = EXCLUDED.description;

-- Default program
INSERT INTO programs (program_id, name) VALUES
    ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, 'Case Management'),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, 'Dev Program');

-- Program modules
INSERT INTO program_modules (program_id, module_id) VALUES
    ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, '0194a8b2-8888-7123-8123-123456789ac7'::uuid),
    ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, '0194a8b2-aaaa-7123-8123-123456789ac9'::uuid),
    ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, '0194a8b2-bbbb-7123-8123-123456789aca'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-cccc-7123-8123-123456789acb'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '442d22f0-a2c9-4b29-a741-ce90111ccfa4'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-acd0-7123-8123-123456789acd'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '6861955c-f882-44db-ab95-89c1a9b8e307'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, 'c6d16f2c-4c20-42f7-a637-07f683e4e4d1'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-0020-7123-8123-123456789abc'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-0021-7123-8123-123456789abc'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-8888-7123-8123-123456789ac6'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-0030-7123-8123-123456789abc'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-0031-7123-8123-123456789abc'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-0018-7123-8123-123456789abc'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '01940882-1001-7123-8123-123456789001'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '01940882-1002-7123-8123-123456789002'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '01940882-1003-7123-8123-123456789003'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '01940882-1004-7123-8123-123456789004'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '01940882-1005-7123-8123-123456789005'::uuid),
    ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-a1b2-7123-8123-123456789abc'::uuid)
ON CONFLICT DO NOTHING;

-- Ensure Procurement program exists before enrollments below
INSERT INTO programs (program_id, name)
VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, 'Procurement')
ON CONFLICT (program_id) DO NOTHING;


INSERT INTO program_enrollments (program_id, org_id) VALUES
  ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid),  -- Cybex PTE LTD - Case Management
  ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, '0194a8b2-5678-7123-8123-123456789abd'::uuid),  -- Karunaratne Lawyers - Case Management
  ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid)   -- Cybex PTE LTD - Dev Program
ON CONFLICT DO NOTHING;


-- Active enrollment kept for Karunaratne Lawyers only
INSERT INTO program_enrollments (program_id, org_id) VALUES
  ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, '0194a8b2-5678-7123-8123-123456789abd'::uuid)  -- Karunaratne Lawyers - Case Management
ON CONFLICT DO NOTHING;

-- Activate Procurement enrollment for Cybex PTE LTD
INSERT INTO program_enrollments (program_id, org_id, status) VALUES
  ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'active')
ON CONFLICT (program_id, org_id) DO NOTHING;

-- How to re-enable Cybex enrollments (uncomment one of the lines below):
-- Case Management program (disabled)
-- INSERT INTO program_enrollments (program_id, org_id) VALUES ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid) ON CONFLICT DO NOTHING;
-- Dev Program
-- INSERT INTO program_enrollments (program_id, org_id) VALUES ('0194a8b2-dddd-7123-8123-123456789acd'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid) ON CONFLICT DO NOTHING;
-- Recommended: Enroll Cybex into Procurement program (created above)
-- INSERT INTO program_enrollments (program_id, org_id, status) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'active') ON CONFLICT (program_id, org_id) DO NOTHING;

-- Create Managers group for Karunaratne Lawyers in Case Management
INSERT INTO groups (group_id, name, description, org_id) VALUES
    ('0194a8b2-aaaa-7123-8123-123456789ac4'::uuid, 'Managers', 'Case Management Managers Group', '0194a8b2-5678-7123-8123-123456789abd'::uuid)
ON CONFLICT (group_id) DO NOTHING;

-- Create Dev group for Cybex PTE LTD in Dev Program
INSERT INTO groups (group_id, name, description, org_id) VALUES
    ('0194a8b2-eeee-7123-8123-123456789ace'::uuid, 'Dev Team', 'Development Team Group', '0194a8b2-1234-7123-8123-123456789abc'::uuid)
ON CONFLICT (group_id) DO NOTHING;

-- Create Dev group for Cybex PTE LTD in Dev Program
INSERT INTO groups (group_id, name, description, org_id) VALUES
    ('0194a8ba-bcde-7123-8123-123456789ad7'::uuid, 'Cashiers', 'Cashiers Group', '0194a8b2-1234-7123-8123-123456789abc'::uuid)
ON CONFLICT (group_id) DO NOTHING;

-- Move all Case Management modules to Dev Team group (Cybex) and unenroll Cybex from Case Management
-- 1) Grant modules defined for Case Management program to Dev Team group
INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT '0194a8b2-eeee-7123-8123-123456789ace'::uuid AS group_id, pm.module_id, 'allow'
FROM program_modules pm
WHERE pm.program_id = '0194a8b2-5555-7123-8123-123456789ac4'::uuid
ON CONFLICT DO NOTHING;

-- 2) Unenroll Cybex PTE LTD from Case Management (if present)
DELETE FROM program_enrollments
WHERE program_id = '0194a8b2-5555-7123-8123-123456789ac4'::uuid
  AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid;

-- Create POSManagers group for Cybex PTE LTD
INSERT INTO groups (group_id, name, description, org_id) VALUES
  ('0194a8ba-d0d0-7123-8123-123456789ad8'::uuid, 'POSManagers', 'Point of Sale managers group', '0194a8b2-1234-7123-8123-123456789abc'::uuid)
ON CONFLICT (group_id) DO NOTHING;

-- Create ProcurementManagers group for Cybex PTE LTD
INSERT INTO groups (group_id, name, description, org_id) VALUES
  ('0194a8ba-dede-7123-8123-123456789ad9'::uuid, 'ProcurementManagers', 'Procurement managers group (migrated from program)', '0194a8b2-1234-7123-8123-123456789abc'::uuid)
ON CONFLICT (group_id) DO NOTHING;

-- Grant POSManagers access to POS and InventoryManagement modules (using known module UUIDs)
INSERT INTO group_module_permissions (group_id, module_id, action) VALUES
  ('0194a8ba-d0d0-7123-8123-123456789ad8'::uuid, '0194a8b2-0015-7123-8123-123456789abc'::uuid, 'allow'), -- POS
  ('0194a8ba-d0d0-7123-8123-123456789ad8'::uuid, '0194a8b2-0016-7123-8123-123456789abc'::uuid, 'allow')  -- InventoryManagement
ON CONFLICT DO NOTHING;

-- Optionally grant Products and Restaurant Products if those modules exist (safe by name)
INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT '0194a8ba-d0d0-7123-8123-123456789ad8'::uuid, m.module_id, 'allow'
FROM modules m
WHERE m.name IN ('Products', 'Restaurant Products')
ON CONFLICT DO NOTHING;

-- Grant POS Settings module to POSManagers group (safe by name)
INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT '0194a8ba-d0d0-7123-8123-123456789ad8'::uuid, m.module_id, 'allow'
FROM modules m
WHERE m.name = 'POS Settings'
ON CONFLICT DO NOTHING;

-- Grant Cashiers access to POS module
INSERT INTO group_module_permissions (group_id, module_id, action) VALUES
  ('0194a8ba-bcde-7123-8123-123456789ad7'::uuid, '0194a8b2-0015-7123-8123-123456789abc'::uuid, 'allow')
ON CONFLICT DO NOTHING;

-- Grant all Procurement program modules to ProcurementManagers group
INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT '0194a8ba-dede-7123-8123-123456789ad9'::uuid, pm.module_id, 'allow'
FROM program_modules pm
WHERE pm.program_id = '0194a8b2-2222-7123-8123-123456789af0'::uuid
ON CONFLICT DO NOTHING;

-- Unenroll Cybex from Procurement program so modules become group-driven
DELETE FROM program_enrollments
WHERE program_id = '0194a8b2-2222-7123-8123-123456789af0'::uuid
  AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid;

-- Move "Vendor Quote Detail" to Procurement only
-- 1) Remove from all Cybex groups except ProcurementManagers (safety: no-op if not present)
DELETE FROM group_module_permissions gmp
USING groups g, modules m
WHERE gmp.group_id = g.group_id
  AND gmp.module_id = m.module_id
  AND g.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
  AND m.name = 'Vendor Quote Detail'
  AND g.name <> 'ProcurementManagers';

-- 2) Grant to ProcurementManagers group explicitly (in case it's not part of Procurement program modules)
INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT g.group_id, m.module_id, 'allow'
FROM groups g
JOIN modules m ON m.name = 'Vendor Quote Detail'
WHERE g.name = 'ProcurementManagers'
  AND g.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
ON CONFLICT DO NOTHING;

-- Ensure Dev Team seed user inherits every demo module from combined.sql
WITH dev_modules AS (
  SELECT module_id FROM (VALUES
    ('018e3f0a-12d3-7c45-b89a-2f3c4e1d5b6c'::uuid),
    ('01940882-1001-7123-8123-123456789001'::uuid),
    ('01940882-1002-7123-8123-123456789002'::uuid),
    ('01940882-1003-7123-8123-123456789003'::uuid),
    ('01940882-1004-7123-8123-123456789004'::uuid),
    ('01940882-1005-7123-8123-123456789005'::uuid),
    ('0194a8b2-0015-7123-8123-123456789abc'::uuid),
    ('0194a8b2-0016-7123-8123-123456789abc'::uuid),
    ('0194a8b2-0017-7123-8123-123456789abc'::uuid),
    ('0194a8b2-0018-7123-8123-123456789abc'::uuid),
    ('0194a8b2-0020-7123-8123-123456789abc'::uuid),
    ('0194a8b2-0021-7123-8123-123456789abc'::uuid),
    ('0194a8b2-0030-7123-8123-123456789abc'::uuid),
    ('0194a8b2-0031-7123-8123-123456789abc'::uuid),
    ('0194a8b2-0032-7123-8123-123456789abc'::uuid),
    ('0194a8b2-1035-7123-8123-123456789abc'::uuid),
    ('0194a8b2-7123-7123-8123-123456789abc'::uuid),
    ('0194a8b2-7124-7123-8123-123456789abc'::uuid),
    ('0194a8b2-7777-7123-8123-123456789ac6'::uuid),
    ('0194a8b2-8888-7123-8123-123456789ac6'::uuid),
    ('0194a8b2-8888-7123-8123-123456789ac7'::uuid),
    ('0194a8b2-9999-7123-8123-123456789ac8'::uuid),
    ('0194a8b2-aaaa-7123-8123-123456789ac9'::uuid),
    ('0194a8b2-acd0-7123-8123-123456789acd'::uuid),
    ('0194a8b2-bbbb-7123-8123-123456789aca'::uuid),
    ('0194a8b2-cccc-7123-8123-123456789acb'::uuid),
    ('442d22f0-a2c9-4b29-a741-ce90111ccfa4'::uuid),
    ('6861955c-f882-44db-ab95-89c1a9b8e307'::uuid),
    ('c6d16f2c-4c20-42f7-a637-07f683e4e4d1'::uuid)
  ) AS v(module_id)
  UNION
  SELECT m.module_id
  FROM modules m
  WHERE m.name IN ('Products', 'Restaurant Products', 'POS Settings', 'Vendor Quote Detail')
)
INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT '0194a8b2-eeee-7123-8123-123456789ace'::uuid, dm.module_id, 'allow'
FROM dev_modules dm
ON CONFLICT DO NOTHING;

-- Assign Employees module to Managers group
INSERT INTO group_module_permissions (group_id, module_id, action) VALUES
    ('0194a8b2-aaaa-7123-8123-123456789ac4'::uuid, '0194a8b2-7777-7123-8123-123456789ac6'::uuid, 'allow')
ON CONFLICT DO NOTHING;

-- Assign Time Entries & Expenses module to Managers group
INSERT INTO group_module_permissions (group_id, module_id, action) VALUES
    ('0194a8b2-aaaa-7123-8123-123456789ac4'::uuid, '0194a8b2-9999-7123-8123-123456789ac8'::uuid, 'allow')
ON CONFLICT DO NOTHING;

-- Additional Karunaratne Lawyers users
WITH
  super_admin AS (SELECT '0194a8b2-1111-7123-8123-123456789ac0'::uuid AS user_id),
  employment_admin AS (SELECT '0194a8b2-2222-7123-8123-123456789ac1'::uuid AS user_id),
  waf_admin AS (SELECT '0194a8b2-3333-7123-8123-123456789ac2'::uuid AS user_id),
  jagath_user AS (SELECT '0194a8b2-4444-7123-8123-123456789ac3'::uuid AS user_id),
  roma_user AS (SELECT '0194a8b2-4445-7123-8123-123456789ac3'::uuid AS user_id),
  mereani_user AS (SELECT '0194a8b2-4446-7123-8123-123456789ac3'::uuid AS user_id),
  admin_user AS (SELECT '0194a8b2-4447-7123-8123-123456789ac3'::uuid AS user_id),
  victor_user AS (SELECT '0194a8b2-4448-7123-8123-123456789ac3'::uuid AS user_id),
  shiral_user AS (SELECT '0194a8b2-4449-7123-8123-123456789ac3'::uuid AS user_id),
  ins_users AS (
    INSERT INTO users (user_id, full_name, is_superadmin) VALUES
      ((SELECT user_id FROM super_admin), 'Bhaskaran Chetty', TRUE),
      ((SELECT user_id FROM employment_admin), 'EmploymentAdmin', FALSE),
      ((SELECT user_id FROM waf_admin), 'WAFAdmin', FALSE),
      ((SELECT user_id FROM jagath_user), 'Jagath', FALSE),
      ((SELECT user_id FROM roma_user), 'Roma S', FALSE),
      ((SELECT user_id FROM mereani_user), 'Mereani', FALSE),
      ((SELECT user_id FROM admin_user), 'Akansha', FALSE),
      ((SELECT user_id FROM victor_user), 'Victor Adrian', FALSE),
      ((SELECT user_id FROM shiral_user), 'Shiral Kumar', FALSE)
  ),
  ins_passwords AS (
    INSERT INTO user_passwords (user_id, password_hash, algo)
    SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM super_admin
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM employment_admin
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM waf_admin
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM jagath_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM roma_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM mereani_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM admin_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM victor_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM shiral_user
  ),
  ins_emails AS (
    INSERT INTO user_emails (user_id, email)
    SELECT user_id, 'admin@cybexpte.com' FROM super_admin
    UNION ALL SELECT user_id, 'employment@cybexpte.com' FROM employment_admin
    UNION ALL SELECT user_id, 'waf@cybexpte.com' FROM waf_admin
    UNION ALL SELECT user_id, 'jagath@karunaratnelawyers.com.fj' FROM jagath_user
    UNION ALL SELECT user_id, 'Roma@karunaratnelawyers.com.fj' FROM roma_user
    UNION ALL SELECT user_id, 'mereani@karunaratnelawyers.com.fj' FROM mereani_user
    UNION ALL SELECT user_id, 'admin@karunaratnelawyers.com.fj' FROM admin_user
    UNION ALL SELECT user_id, 'victorpatrick.adrian@gmail.com' FROM victor_user
    UNION ALL SELECT user_id, 'shiralkumar05@gmail.com' FROM shiral_user
  ),
  ins_memberships AS (
    INSERT INTO user_org_memberships (user_id, org_id, role)
    SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Admin' FROM super_admin
    UNION ALL SELECT user_id, '0194a8b2-def0-7123-8123-123456789abf'::uuid, 'Admin' FROM employment_admin
    UNION ALL SELECT user_id, '0194a8b2-9abc-7123-8123-123456789abe'::uuid, 'Admin' FROM waf_admin
    UNION ALL SELECT user_id, '0194a8b2-5678-7123-8123-123456789abd'::uuid, 'Admin' FROM jagath_user
    UNION ALL SELECT user_id, '0194a8b2-5678-7123-8123-123456789abd'::uuid, 'Member' FROM roma_user
    UNION ALL SELECT user_id, '0194a8b2-5678-7123-8123-123456789abd'::uuid, 'Member' FROM mereani_user
    UNION ALL SELECT user_id, '0194a8b2-5678-7123-8123-123456789abd'::uuid, 'Admin' FROM admin_user
    UNION ALL SELECT user_id, '0194a8b2-5678-7123-8123-123456789abd'::uuid, 'Member' FROM victor_user
    UNION ALL SELECT user_id, '0194a8b2-5678-7123-8123-123456789abd'::uuid, 'Member' FROM shiral_user
  )
SELECT 1;

-- Add user cashier@cybexpte.com for Cybex PTE LTD with password 'cashier123**'
WITH cashier_user AS (
  SELECT '0194a8b2-4456-7123-8123-123456789ac3'::uuid AS user_id
), ins_user AS (
  INSERT INTO users (user_id, full_name, is_superadmin)
  VALUES ((SELECT user_id FROM cashier_user), 'Cashier', FALSE)
  ON CONFLICT DO NOTHING
), ins_email AS (
  INSERT INTO user_emails (user_id, email)
  SELECT user_id, 'cashier@cybexpte.com' FROM cashier_user
  ON CONFLICT DO NOTHING
), ins_password AS (
  INSERT INTO user_passwords (user_id, password_hash, algo)
  SELECT user_id, '$2y$10$Ru/TGiXG7McdmQCOIkx4E.aUmXKbDbWgiz1uugrAQS36DOvA.4DAO', 'bcrypt' FROM cashier_user
  ON CONFLICT DO NOTHING
), ins_membership AS (
  INSERT INTO user_org_memberships (user_id, org_id, role)
  SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM cashier_user
  ON CONFLICT DO NOTHING
)
SELECT 1;

-- Add user frcs@cybexpte.com for Cybex PTE LTD with password 'frcs123**'
WITH frcs_user AS (
  SELECT '0194a8b2-4457-7123-8123-123456789ac3'::uuid AS user_id
), ins_user AS (
  INSERT INTO users (user_id, full_name, is_superadmin)
  VALUES ((SELECT user_id FROM frcs_user), 'FRCS Guest', FALSE)
  ON CONFLICT DO NOTHING
), ins_email AS (
  INSERT INTO user_emails (user_id, email)
  SELECT user_id, 'frcs@cybexpte.com' FROM frcs_user
  ON CONFLICT DO NOTHING
), ins_password AS (
  INSERT INTO user_passwords (user_id, password_hash, algo)
  SELECT user_id, '$2y$10$T02uy4Dw9WQKg5nSXZ34mOWynR1kBh/sEWB860zu5T71TiNSn/1Gu', 'bcrypt' FROM frcs_user
  ON CONFLICT DO NOTHING
), ins_membership AS (
  INSERT INTO user_org_memberships (user_id, org_id, role)
  SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM frcs_user
  ON CONFLICT DO NOTHING
)
SELECT 1;

-- Add user posmanager@cybexpte.com for Cybex PTE LTD with password 'posmanager123**'
WITH posmgr_user AS (
  SELECT '0194a8b2-4458-7123-8123-123456789ac3'::uuid AS user_id
), ins_user AS (
  INSERT INTO users (user_id, full_name, is_superadmin)
  VALUES ((SELECT user_id FROM posmgr_user), 'POS Manager', FALSE)
  ON CONFLICT DO NOTHING
), ins_email AS (
  INSERT INTO user_emails (user_id, email)
  SELECT user_id, 'posmanager@cybexpte.com' FROM posmgr_user
  ON CONFLICT DO NOTHING
), ins_password AS (
  INSERT INTO user_passwords (user_id, password_hash, algo)
  SELECT user_id, '$2y$10$EzMO.rPxOiVP6bFzvCBsn.1CkvfVpCdr7Jdy1W7xRXAMO2r9jMSFe', 'bcrypt' FROM posmgr_user
  ON CONFLICT DO NOTHING
), ins_membership AS (
  INSERT INTO user_org_memberships (user_id, org_id, role)
  SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM posmgr_user
  ON CONFLICT DO NOTHING
)
SELECT 1;

-- Add admin to Dev Team group (Cybex)
INSERT INTO user_group_memberships (user_id, group_id)
SELECT ue.user_id, g.group_id
FROM user_emails ue
JOIN groups g ON g.name = 'Dev Team' AND g.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
WHERE ue.email = 'admin@cybexpte.com'
ON CONFLICT DO NOTHING;

-- Add Cashier to Cashiers group (Cybex)
INSERT INTO user_group_memberships (user_id, group_id)
SELECT ue.user_id, g.group_id
FROM user_emails ue
JOIN groups g ON g.name = 'Cashiers' AND g.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
WHERE ue.email = 'cashier@cybexpte.com'
ON CONFLICT DO NOTHING;

-- Add POS Manager to POSManagers group (Cybex)
INSERT INTO user_group_memberships (user_id, group_id)
SELECT ue.user_id, g.group_id
FROM user_emails ue
JOIN groups g ON g.name = 'POSManagers' AND g.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
WHERE ue.email = 'posmanager@cybexpte.com'
ON CONFLICT DO NOTHING;

-- Add FRCS to POSManagers group (Cybex)
INSERT INTO user_group_memberships (user_id, group_id)
SELECT ue.user_id, g.group_id
FROM user_emails ue
JOIN groups g ON g.name = 'POSManagers' AND g.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
WHERE ue.email = 'frcs@cybexpte.com'
ON CONFLICT DO NOTHING;

-- Add user rbf@cybexpte.com for Cybex PTE LTD with password 'testadmin'
WITH
  rbf_user AS (
    SELECT '0194a8b2-4455-7123-8123-123456789ac3'::uuid AS user_id
  ),
  ins_user AS (
    INSERT INTO users (user_id, full_name, is_superadmin)
    VALUES ((SELECT user_id FROM rbf_user), 'RBF', FALSE)
  ),
  ins_email AS (
    INSERT INTO user_emails (user_id, email)
    SELECT user_id, 'rbf@cybexpte.com' FROM rbf_user
  ),
  ins_password AS (
    INSERT INTO user_passwords (user_id, password_hash, algo)
    SELECT user_id, '$2y$10$kxxVvASTZlvC/O2AI5XUEubYQrLFHmzJXxuTqWu1JY9zm3z7otmrm', 'bcrypt' FROM rbf_user
  ),
  ins_membership AS (
    INSERT INTO user_org_memberships (user_id, org_id, role)
    SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM rbf_user
  )
SELECT 1;

-- Add jagath to Managers group (after users are created)
INSERT INTO user_group_memberships (user_id, group_id) VALUES
    ('0194a8b2-4444-7123-8123-123456789ac3'::uuid, '0194a8b2-aaaa-7123-8123-123456789ac4'::uuid)
ON CONFLICT DO NOTHING;

-- Add victor to Managers group (after users are created)
INSERT INTO user_group_memberships (user_id, group_id) VALUES
    ('0194a8b2-4448-7123-8123-123456789ac3'::uuid, '0194a8b2-aaaa-7123-8123-123456789ac4'::uuid)
ON CONFLICT DO NOTHING;

-- Default groups for Case Management program
INSERT INTO program_default_groups (program_id, group_name, group_description, is_auto_create) VALUES
    ('0194a8b2-5555-7123-8123-123456789ac4'::uuid, 'Managers', 'Case Management Managers with full access to employee and time tracking modules', true)
ON CONFLICT (program_id, group_name) DO NOTHING;

-- Default permissions for Managers group
INSERT INTO program_default_group_permissions (program_default_group_id, module_id, permission_type, action) VALUES
    ((SELECT program_default_group_id FROM program_default_groups WHERE program_id = '0194a8b2-5555-7123-8123-123456789ac4'::uuid AND group_name = 'Managers' LIMIT 1),
     '0194a8b2-7777-7123-8123-123456789ac6'::uuid, 'module', 'allow') -- Employees module
ON CONFLICT DO NOTHING;

INSERT INTO program_default_group_permissions (program_default_group_id, module_id, permission_type, action) VALUES
    ((SELECT program_default_group_id FROM program_default_groups WHERE program_id = '0194a8b2-5555-7123-8123-123456789ac4'::uuid AND group_name = 'Managers' LIMIT 1),
     '0194a8b2-9999-7123-8123-123456789ac8'::uuid, 'module', 'allow') -- Time Entries & Expenses module
ON CONFLICT DO NOTHING;

INSERT INTO program_default_group_permissions (program_default_group_id, module_id, permission_type, action) VALUES
    ((SELECT program_default_group_id FROM program_default_groups WHERE program_id = '0194a8b2-5555-7123-8123-123456789ac4'::uuid AND group_name = 'Managers' LIMIT 1),
     '0194a8b2-cccc-7123-8123-123456789acb'::uuid, 'module', 'allow') -- UserAccessManager module
ON CONFLICT DO NOTHING;





-- Seed demo locations
INSERT INTO locations (name, latitude, longitude, radius_m, tenant_id, branch_id) VALUES
  ('Suva Headquarters', -18.1397956843015, 178.42656254768372, 50, 1, 1),
  ('Nadi Office', -17.7533, 177.4435, 50, 1, 1),
  ('Lautoka Office', -17.6167, 177.4667, 50, 1, 1);

-- Ensure the sequence for locations continues from the inserted values
SELECT setval('locations_id_seq', (SELECT MAX(id) FROM locations));

INSERT INTO vendors (vendor_id, vendorName) VALUES
    (uuid_generate_v7(), 'Acme Corp'),
    (uuid_generate_v7(), 'Global Supplies'),
    (uuid_generate_v7(), 'Demo Vendor');

-- Dummy data for products table
INSERT INTO products (product_id, name, description, price, org_id, barcode, brand, isDeleted, updatedAt)
VALUES
  (uuid_generate_v7(), 'Apple', 'Fresh red apples', 2.75, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '111', 'FreshFarms', FALSE, now()),
  (uuid_generate_v7(), 'Banana', 'Organic bananas', 5, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '112', 'TropiFruit', FALSE, now()),
  (uuid_generate_v7(), 'Milk', 'Full cream milk 1L', 3.95, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '211', 'DairyPure', FALSE, now()),
  (uuid_generate_v7(), 'Bread', 'Whole wheat bread', 2.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '311', 'BakeHouse', FALSE, now()),
  (uuid_generate_v7(), 'Eggs', 'Free range eggs (dozen)', 13, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '212', 'FarmFresh', FALSE, now()),
  (uuid_generate_v7(), 'Bongo', 'Crunchy bongo snacks', 1.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '401', 'SnackCo', FALSE, now()),
  (uuid_generate_v7(), 'Chips', 'Potato chips', 2.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '402', 'SnackCo', FALSE, now()),
  (uuid_generate_v7(), 'Carrot', 'Fresh carrots', 1.20, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '501', 'VeggieFarm', FALSE, now()),
  (uuid_generate_v7(), 'Lettuce', 'Green lettuce', 1.80, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '502', 'VeggieFarm', FALSE, now()),
  (uuid_generate_v7(), 'Crest Chicken #18', 'Crest Chicken #18', 20.55, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '601', 'MeatMaster', FALSE, now()),
  (uuid_generate_v7(), 'Beef', 'Beef steak', 12.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '602', 'MeatMaster', FALSE, now()),
  (uuid_generate_v7(), 'Salmon', 'Fresh salmon fillet', 15.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '701', 'SeaBest', FALSE, now()),
  (uuid_generate_v7(), 'Shrimp', 'Frozen shrimp', 13.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '702', 'SeaBest', FALSE, now()),
  (uuid_generate_v7(), 'Ice Cream', 'Vanilla ice cream', 6.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '801', 'Frosty', FALSE, now()),
  (uuid_generate_v7(), 'Frozen Pizza', 'Cheese frozen pizza', 7.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '802', 'Frosty', FALSE, now()),
  (uuid_generate_v7(), 'FMF Sungrown Rice 10kg', 'FMF Sungrown Rice 10kg', 12.75, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '901', 'PantryPro', FALSE, now()),
  (uuid_generate_v7(), 'Pasta', 'Spaghetti pasta', 2.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '902', 'PantryPro', FALSE, now()),
  (uuid_generate_v7(), 'Soap', 'Bath soap', 1.80, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '1001', 'CarePlus', FALSE, now()),
  (uuid_generate_v7(), 'Shampoo', 'Hair shampoo', 4.20, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '1002', 'CarePlus', FALSE, now()),
  (uuid_generate_v7(), 'Detergent', 'Laundry detergent', 5.50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '1101', 'HomeClean', FALSE, now()),
  (uuid_generate_v7(), 'Paper Towels', 'Kitchen paper towels', 3.80, '0194a8b2-1234-7123-8123-123456789abc'::uuid, '1102', 'HomeClean', FALSE, now());


-- Dummy data for product_batch_number (expiry date, batch number)
INSERT INTO product_batch_number (product_batch_number_id, product_id, expiry_date, batch_number, isDeleted, updatedAt)
VALUES
    (uuid_generate_v7(), (SELECT product_id FROM products LIMIT 1), '2026-01-01', 'BATCH-001', FALSE, NOW()),
    (uuid_generate_v7(), (SELECT product_id FROM products OFFSET 1 LIMIT 1), '2026-06-01', 'BATCH-002', FALSE, NOW()),
    (uuid_generate_v7(), (SELECT product_id FROM products OFFSET 2 LIMIT 1), '2027-01-01', 'BATCH-003', FALSE, NOW());

-- Dummy data for inventory (link to batch, org, qty)
INSERT INTO inventory (inventory_id, product_batch_number_id, branch_id, warehouse_id, reference_id, reference_table, current_qty, org_id, isDeleted, updatedAt)
VALUES
    (uuid_generate_v7(), (SELECT product_batch_number_id FROM product_batch_number LIMIT 1), NULL, NULL, NULL, NULL, 100, '0194a8b2-1234-7123-8123-123456789abc'::uuid, false, NOW()),
    (uuid_generate_v7(), (SELECT product_batch_number_id FROM product_batch_number OFFSET 1 LIMIT 1), NULL, NULL, NULL, NULL, 50, '0194a8b2-1234-7123-8123-123456789abc'::uuid, false, NOW()),
    (uuid_generate_v7(), (SELECT product_batch_number_id FROM product_batch_number OFFSET 2 LIMIT 1), NULL, NULL, NULL, NULL, 200, '0194a8b2-1234-7123-8123-123456789abc'::uuid, false, NOW());

-- Dummy data for inventory_txn (link to product, org, qty change)
INSERT INTO inventory_txn (inventory_txn_id, product_id, change, branch_id, user_id, reference_id, org_id, reason)
VALUES
    (uuid_generate_v7(), (SELECT product_id FROM products LIMIT 1), 20, NULL, NULL, NULL, (SELECT org_id FROM orgs LIMIT 1), 'Initial stock'),
    (uuid_generate_v7(), (SELECT product_id FROM products OFFSET 1 LIMIT 1), -10, NULL, NULL, NULL, (SELECT org_id FROM orgs OFFSET 1 LIMIT 1), 'Stock adjustment'),
    (uuid_generate_v7(), (SELECT product_id FROM products OFFSET 2 LIMIT 1), 50, NULL, NULL, NULL, (SELECT org_id FROM orgs OFFSET 2 LIMIT 1), 'Restock');

-- Create dev program
INSERT INTO programs (program_id, name, owner_root_org_id, is_private, owner_access) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, 'dev', '0194a8b2-1234-7123-8123-123456789abc'::uuid, true, 'none')
ON CONFLICT (program_id) DO NOTHING;

-- Add Products module to dev program
INSERT INTO program_modules (program_id, module_id, config_json) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, (SELECT module_id FROM modules WHERE name = 'Products' LIMIT 1), NULL)
ON CONFLICT DO NOTHING;

-- Add Restaurant Products module to dev program
INSERT INTO program_modules (program_id, module_id, config_json) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, (SELECT module_id FROM modules WHERE name = 'Restaurant Products' LIMIT 1), NULL)
ON CONFLICT DO NOTHING;

-- Add POS module to dev program
INSERT INTO program_modules (program_id, module_id, config_json) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '0194a8b2-0015-7123-8123-123456789abc'::uuid, NULL)
ON CONFLICT DO NOTHING;

-- Add InventoryManagement module to dev program
INSERT INTO program_modules (program_id, module_id, config_json) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '0194a8b2-0016-7123-8123-123456789abc'::uuid, NULL)
ON CONFLICT DO NOTHING;

-- Add Time Clocks module to dev program
INSERT INTO program_modules (program_id, module_id, config_json) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '0194a8b2-7123-7123-8123-123456789abc'::uuid, NULL)
ON CONFLICT DO NOTHING;

-- Add Time Clock Aggregates module to dev program
INSERT INTO program_modules (program_id, module_id, config_json) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '018e3f0a-12d3-7c45-b89a-2f3c4e1d5b6c'::uuid, NULL)
ON CONFLICT DO NOTHING;

-- Add Vendor Management module to dev program
-- First, create the Vendor Management module if it does not exist
INSERT INTO modules (module_id, name, description) VALUES
    ('0194a8b2-0017-7123-8123-123456789abc'::uuid, 'Vendor Management', 'Vendor management module')
ON CONFLICT (module_id) DO NOTHING;

INSERT INTO program_modules (program_id, module_id, config_json) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '0194a8b2-0017-7123-8123-123456789abc'::uuid, NULL)
ON CONFLICT DO NOTHING;

-- Add Quotations module to dev program
-- First, create the Quotations module if it does not exist
INSERT INTO modules (module_id, name, description) VALUES
    ('0194a8b2-0018-7123-8123-123456789abc'::uuid, 'Quotations', 'Quotations management module')
ON CONFLICT (module_id) DO NOTHING;

INSERT INTO program_modules (program_id, module_id, config_json) VALUES
    ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '0194a8b2-0018-7123-8123-123456789abc'::uuid, NULL)
ON CONFLICT DO NOTHING;

-- Add Procurement Contract Detail module to dev program
INSERT INTO program_modules (program_id, module_id, config_json) VALUES
  ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '0194a8b2-1035-7123-8123-123456789abc'::uuid, NULL)
ON CONFLICT DO NOTHING;

-- Move all modules from Dev Programs to the Dev Team group and remove from programs
-- Grant every module currently in either Dev Program to Dev Team (Cybex)
INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT '0194a8b2-eeee-7123-8123-123456789ace'::uuid AS group_id, pm.module_id, 'allow' AS action
FROM program_modules pm
WHERE pm.program_id IN (
  '0194a8b2-dddd-7123-8123-123456789acd'::uuid, -- Default Dev Program
  '0194a8b2-def0-7123-8123-123456789abc'::uuid  -- Dev (tenant-owned) program
)
ON CONFLICT DO NOTHING;

-- Remove the modules from the Dev Programs to avoid double-scoping
DELETE FROM program_modules pm
WHERE pm.program_id IN (
  '0194a8b2-dddd-7123-8123-123456789acd'::uuid,
  '0194a8b2-def0-7123-8123-123456789abc'::uuid
);

-- Ensure Vendor Quote Detail module exists (used by Open Quote action)
INSERT INTO modules (module_id, name, config, org_id, branch_id, icon, aria_label, description)
SELECT
  '0194a8b2-0032-7123-8123-123456789abc'::uuid,
  'Vendor Quote Detail',
  $json$
  {
    "title": "Vendor Quote Detail",
    "body": [
      { "type":"tabs","tabs":[
        { "label":"Summary","elements":[
          { "type":"detail-view", "tableName":"vendor_quotes", "filterField":"quote_id" },
          { "type":"html", "html":"<div class='divider my-2'>Line Items</div>" },
          { "type":"table", "options": { "tableName":"vendor_quote_items", "filterField":"quote_id", "columnsToShow":["item_description","is_alternate","alt_label","unit_price","tax","lead_time_days","notes"], "addButton": false } }
        ]},
        { "label":"Attachments","elements":[
          { "type":"html", "html":"<div id='quote-attachments'><div data-role='attachments-list' class='space-y-1'></div></div>" }
        ]}
      ]}
    ],
    "footer":[{"type":"button","label":"Close","onClick":"closeModal"}]
  }
  $json$::jsonb,
  NULL, NULL, 'fa-file-circle-check', 'Vendor Quote Detail', 'View-only summary of a vendor quote'
WHERE NOT EXISTS (
  SELECT 1 FROM modules WHERE module_id = '0194a8b2-0032-7123-8123-123456789abc'::uuid OR name = 'Vendor Quote Detail'
);

-- If the Vendor Quote Detail module already exists, ensure the Attachments tab has a container
UPDATE modules
SET config = $json$
{
  "title": "Vendor Quote Detail",
  "body": [
    { "type":"tabs","tabs":[
      { "label":"Summary","elements":[
        { "type":"detail-view", "tableName":"vendor_quotes", "filterField":"quote_id" },
        { "type":"html", "html":"<div class='divider my-2'>Line Items</div>" },
        { "type":"table", "options": { "tableName":"vendor_quote_items", "filterField":"quote_id", "columnsToShow":["item_description","is_alternate","alt_label","unit_price","tax","lead_time_days","notes"], "addButton": false } }
      ]},
      { "label":"Attachments","elements":[
        { "type":"html", "html":"<div id='quote-attachments'><div data-role='attachments-list' class='space-y-1'></div></div>" }
      ]}
    ]}
  ],
  "footer":[{"type":"button","label":"Close","onClick":"closeModal"}]
}
$json$::jsonb
WHERE name = 'Vendor Quote Detail'
  AND (config::text NOT LIKE '%quote-attachments%');

-- Add Vendor Quote Detail module to Dev Program so it is synced to the frontend
INSERT INTO program_modules (program_id, module_id, config_json) VALUES
  ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '0194a8b2-0032-7123-8123-123456789abc'::uuid, NULL)
ON CONFLICT DO NOTHING;

-- Enroll Cybex PTE LTD in dev program (disabled; see Procurement enrollment guidance above)
INSERT INTO program_enrollments (program_id, org_id, status) VALUES
  ('0194a8b2-def0-7123-8123-123456789abc'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'active')
ON CONFLICT (program_id, org_id) DO NOTHING;

-- ============================================================
-- New Program: Procurement (explicit module list from Dev programs)
-- ============================================================

-- Create Procurement program
INSERT INTO programs (program_id, name)
VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, 'Procurement')
ON CONFLICT (program_id) DO NOTHING;

-- Add modules from "Dev Program" (0194a8b2-dddd-7123-8123-123456789acd)
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-acd0-7123-8123-123456789acd'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, 'c6d16f2c-4c20-42f7-a637-07f683e4e4d1'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0020-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0021-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0030-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0031-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0018-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '01940882-1001-7123-8123-123456789001'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '01940882-1002-7123-8123-123456789002'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '01940882-1003-7123-8123-123456789003'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '01940882-1004-7123-8123-123456789004'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '01940882-1005-7123-8123-123456789005'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0032-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-1035-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-8888-7123-8123-123456789ac6'::uuid, NULL) ON CONFLICT DO NOTHING;

-- Add modules from private "dev" program (0194a8b2-def0-7123-8123-123456789abc)
--INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, (SELECT module_id FROM modules WHERE name = 'Products' LIMIT 1), NULL) ON CONFLICT DO NOTHING;
--INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, (SELECT module_id FROM modules WHERE name = 'Restaurant Products' LIMIT 1), NULL) ON CONFLICT DO NOTHING;
--INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0015-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
--INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0016-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
--INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0017-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
-- Note: 0194a8b2-0018-7123-8123-123456789abc (Quotations) already added above from Dev Program
--INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-1035-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;
--INSERT INTO program_modules (program_id, module_id, config_json) VALUES ('0194a8b2-2222-7123-8123-123456789af0'::uuid, '0194a8b2-0032-7123-8123-123456789abc'::uuid, NULL) ON CONFLICT DO NOTHING;

-- Demo customers for Cybex PTE LTD
INSERT INTO customers (
    customer_type, name, email, phone_number, address, tin, 
    risk_rating, compliance_status, balance, org_id, user_id, 
    isDeleted, updatedAt
) VALUES
    ('individual', 'John Smith', 'john.smith@email.com', '+679 123 4567', '123 Main Street, Suva, Fiji', '123456789', 'low', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('individual', 'Mary Johnson', 'mary.johnson@email.com', '+679 234 5678', '456 Queen Street, Suva, Fiji', '234567890', 'low', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('corporate', 'Pacific Traders Ltd', 'info@pacifictraders.com.fj', '+679 345 6789', '789 Kings Road, Suva, Fiji', '345678901', 'medium', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('corporate', 'Fiji Imports Ltd', 'sales@fijiimports.com.fj', '+679 456 7890', '321 Victoria Parade, Suva, Fiji', '456789012', 'medium', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('individual', 'Raj Kumar', 'raj.kumar@email.com', '+679 567 8901', '654 Princes Road, Suva, Fiji', '567890123', 'low', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('government', 'Ministry of Health', 'procurement@health.gov.fj', '+679 678 9012', 'Government Buildings, Suva, Fiji', '678901234', 'low', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('corporate', 'Suva Supermarket Ltd', 'manager@suvamarket.com.fj', '+679 789 0123', '987 Waimanu Road, Suva, Fiji', '789012345', 'medium', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('individual', 'Lisa Wong', 'lisa.wong@email.com', '+679 890 1234', '147 Gordon Street, Suva, Fiji', '890123456', 'low', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('corporate', 'Island Hardware Ltd', 'orders@islandhardware.com.fj', '+679 901 2345', '258 Thomson Street, Suva, Fiji', '901234567', 'medium', 'pending', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW()),
    ('individual', 'David Prasad', 'david.prasad@email.com', '+679 012 3456', '369 Loftus Street, Suva, Fiji', '012345678', 'low', 'compliant', 0.00, '0194a8b2-1234-7123-8123-123456789abc'::uuid, NULL, FALSE, NOW())
ON CONFLICT DO NOTHING;

-- Additional Cybex PTE LTD users
WITH
  kevin_user AS (SELECT '0194a8b2-4450-7123-8123-123456789ac3'::uuid AS user_id),
  bhaskaran_user AS (SELECT '0194a8b2-4451-7123-8123-123456789ac3'::uuid AS user_id),
  mishika_user AS (SELECT '0194a8b2-4452-7123-8123-123456789ac3'::uuid AS user_id),
  mahek_user AS (SELECT '0194a8b2-4453-7123-8123-123456789ac3'::uuid AS user_id),
  marshneel_user AS (SELECT '0194a8b2-4454-7123-8123-123456789ac3'::uuid AS user_id),
  ins_users AS (
    INSERT INTO users (user_id, full_name, is_superadmin) VALUES
      ((SELECT user_id FROM kevin_user), 'Kevin', FALSE),
      ((SELECT user_id FROM bhaskaran_user), 'Bhaskaran', FALSE),
      ((SELECT user_id FROM mishika_user), 'Mishika', FALSE),
      ((SELECT user_id FROM mahek_user), 'Mahek', FALSE),
      ((SELECT user_id FROM marshneel_user), 'Marshneel', FALSE)
  ),
  ins_passwords AS (
    INSERT INTO user_passwords (user_id, password_hash, algo)
    SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM kevin_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM bhaskaran_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM mishika_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM mahek_user
    UNION ALL SELECT user_id, '$2y$10$PXEdpEbxI5c11/tC3UdC6OrZr7V2MsHAatf5C6LRzRV/goxQDPTl.', 'bcrypt' FROM marshneel_user
  ),
  ins_emails AS (
    INSERT INTO user_emails (user_id, email)
    SELECT user_id, 'kevin@cybexpte.com' FROM kevin_user
    UNION ALL SELECT user_id, 'bhaskaran@cybexpte.com' FROM bhaskaran_user
    UNION ALL SELECT user_id, 'mishika@cybexpte.com' FROM mishika_user
    UNION ALL SELECT user_id, 'mahek@cybexpte.com' FROM mahek_user
    UNION ALL SELECT user_id, 'marshneel@cybexpte.com' FROM marshneel_user
  ),
  ins_memberships AS (
    INSERT INTO user_org_memberships (user_id, org_id, role)
    SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM kevin_user
    UNION ALL SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM bhaskaran_user
    UNION ALL SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM mishika_user
    UNION ALL SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM mahek_user
  UNION ALL SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Member' FROM marshneel_user
  )
SELECT 1;

-- ============================================================
-- Demo Vendors, RFQs, Conversations, Messages and Quotes
-- Org: Cybex PTE LTD (0194a8b2-1234-7123-8123-123456789abc)
-- ============================================================

-- Vendors
INSERT INTO vendors (vendor_id, org_id, vendorName, lifecycle_status, isDeleted, updatedAt, num_employees, company_size) VALUES
  ('0194a8b2-1001-7123-8123-123456789a01'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Pacific IT Solutions', 'active', FALSE, now(), 50, 'SME'),
  ('0194a8b2-1002-7123-8123-123456789a02'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Island Supplies Ltd',  'active', FALSE, now(), 20, 'Small'),
  ('0194a8b2-1003-7123-8123-123456789a03'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Coral Tech Traders',  'active', FALSE, now(), 100, 'Large'),
  ('0194a8b2-1004-7123-8123-123456789a04'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Blue Lagoon Engineering', 'active', FALSE, now(), 65, 'SME'),
  ('0194a8b2-1005-7123-8123-123456789a05'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Sunrise Logistics Co', 'active', FALSE, now(), 150, 'Large')
ON CONFLICT (vendor_id) DO NOTHING;

-- RFQs (2 samples) with due dates and optional QA cutoff
INSERT INTO rfq_headers (
  rfq_id, org_id, type, title, category, currency, incoterms, summary_markdown,
  due_at, qa_cutoff_at, status, created_by, created_at, updatedAt, tender_box_opened_at, tender_box_opened_by
) VALUES
  ('0194a8b2-00f1-7123-8123-123456789a10'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'RFQ', 'Network Switches Supply', 'it', 'FJD', 'EXW',
   'Supply and delivery of Layer-3 network switches.', now() + interval '21 days', now() + interval '14 days', 'open', NULL, now(), now(), NULL, NULL),
  ('0194a8b2-00f2-7123-8123-123456789a11'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'RFQ', 'Office Furniture', 'general', 'FJD', 'DDP',
   'Ergonomic office chairs and desks.', now() + interval '28 days', now() + interval '20 days', 'open', NULL, now(), now(), NULL, NULL),
  ('0194a8b2-00f3-7123-8123-123456789a12'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'RFQ', 'Solar Microgrid EPC', 'energy', 'FJD', 'DAP',
   'Design, construction and commissioning of a 1MW solar microgrid with battery storage.', now() - interval '21 days', now() - interval '28 days', 'closed',
   '0194a8b2-4450-7123-8123-123456789ac3'::uuid, now() - interval '75 days', now() - interval '5 days', now() - interval '18 days', '0194a8b2-4450-7123-8123-123456789ac3'::uuid),
  ('0194a8b2-00f4-7123-8123-123456789a13'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'RFQ', 'Fleet Vehicle Leasing Services', 'fleet', 'FJD', 'DDP',
   'Three-year lease of hybrid SUVs with full maintenance coverage.', now() - interval '45 days', now() - interval '52 days', 'awarded',
   '0194a8b2-4450-7123-8123-123456789ac3'::uuid, now() - interval '95 days', now() - interval '2 days', now() - interval '43 days', '0194a8b2-4450-7123-8123-123456789ac3'::uuid)
ON CONFLICT (rfq_id) DO NOTHING;

-- RFQ (Two-Envelope Technical & Financial) created from template
DO $$
DECLARE v_tpl uuid;
DECLARE v_rfq uuid := '0194a8b2-00f5-7123-8123-123456789a14'::uuid;
BEGIN
  -- Locate the Two-Envelope template by code seeded in sql/defaults.sql
  SELECT rfq_template_id INTO v_tpl FROM rfq_templates WHERE code = 'RBF_TECH_FIN_ENVELOPE' LIMIT 1;
  IF v_tpl IS NULL THEN
    RAISE NOTICE 'Two-Envelope template not found; skipping RFQ seed';
    RETURN;
  END IF;

  -- Insert RFQ header (idempotent)
  INSERT INTO rfq_headers (
    rfq_id, org_id, type, title, category, currency, incoterms, summary_markdown,
    due_at, qa_cutoff_at, status, created_at, updatedAt
  ) VALUES (
    v_rfq,
    '0194a8b2-1234-7123-8123-123456789abc'::uuid,
    'RFQ',
    'Digital Banking App RFP (Two-Envelope)',
    'general',
    'FJD',
    'DDP',
    'Two separate submissions required: technical and financial proposals. See sections for details.',
    now() + interval '35 days',
    now() + interval '28 days',
    'draft',
    now(),
    now()
  ) ON CONFLICT (rfq_id) DO NOTHING;

  -- Copy template sections into rfq_sections (idempotent per section_key)
  INSERT INTO rfq_sections (rfq_id, section_key, heading, body_markdown, body_html, sort_order, include_in_print, include_in_email)
  SELECT v_rfq,
         s.section_key,
         s.heading,
         s.body_markdown,
         s.body_html,
         s.sort_order,
         COALESCE(s.include_in_print, true),
         COALESCE(s.include_in_email, true)
  FROM rfq_template_sections s
  WHERE s.rfq_template_id = v_tpl
    AND NOT EXISTS (
      SELECT 1 FROM rfq_sections rs
      WHERE rs.rfq_id = v_rfq AND rs.section_key = s.section_key
    );

  -- Copy template attachments into rfq_attachments (idempotent per label)
  INSERT INTO rfq_attachments (rfq_id, label, filetype, file_url, include_when, sort_order)
  SELECT v_rfq,
         a.label,
         a.filetype,
         a.file_url,
         COALESCE(a.include_when, 'always'),
         COALESCE(a.sort_order, 0)
  FROM rfq_template_attachments a
  WHERE a.rfq_template_id = v_tpl
    AND NOT EXISTS (
      SELECT 1 FROM rfq_attachments ra
      WHERE ra.rfq_id = v_rfq AND ra.label = a.label
    );

  -- Optional: add a couple of sample RFQ items so the RFQ is tangible in UI and comparisons
  INSERT INTO rfq_items (rfq_id, description, quantity, uom, required_specs, is_optional, created_at, updatedAt)
  SELECT v_rfq, 'Mobile banking app design and build', 1, 'lot', '{"platforms":["iOS","Android"],"security":"OWASP MASVS"}'::jsonb, false, now(), now()
  WHERE NOT EXISTS (SELECT 1 FROM rfq_items WHERE rfq_id = v_rfq);

  INSERT INTO rfq_items (rfq_id, description, quantity, uom, required_specs, is_optional, created_at, updatedAt)
  SELECT v_rfq, 'Year-1 support and maintenance', 12, 'mo', '{"SLA":"8x5","responseHours":4}'::jsonb, true, now(), now()
  WHERE NOT EXISTS (SELECT 1 FROM rfq_items WHERE rfq_id = v_rfq AND description = 'Year-1 support and maintenance');

  -- Close the tender: set due dates in the past and mark status closed
  UPDATE rfq_headers
  SET
    due_at = now() - interval '5 days',
    qa_cutoff_at = now() - interval '12 days',
    status = 'closed',
    tender_box_opened_at = COALESCE(tender_box_opened_at, now() - interval '4 days'),
    tender_box_opened_by = COALESCE(tender_box_opened_by, '0194a8b2-4450-7123-8123-123456789ac3'::uuid),
    updatedAt = now()
  WHERE rfq_id = v_rfq;

END $$;

-- RFQ Items (explicit ids for demo convenience)
INSERT INTO rfq_items (id, rfq_id, description, quantity, uom, required_specs, is_optional, created_at, updatedAt) VALUES
  (900001, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'L3 Switch, 48x1G + 4x10G SFP+', 10, 'ea', '{"warranty":"3y","brand":"Any Tier-1"}'::jsonb, false, now(), now()),
  (900002, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'SFP+ 10G Transceiver SR', 20, 'ea', '{"type":"SR"}'::jsonb, true, now(), now()),
  (900011, '0194a8b2-00f2-7123-8123-123456789a11'::uuid, 'Ergonomic Chair', 40, 'ea', '{"color":"black"}'::jsonb, false, now(), now()),
  (900012, '0194a8b2-00f2-7123-8123-123456789a11'::uuid, 'Standing Desk', 15, 'ea', '{"size":"1200mm"}'::jsonb, false, now(), now()),
  (900021, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, 'Tier-1 solar PV panels 540W', 180, 'ea', '{"minEfficiency":"21%","warranty":"10y"}'::jsonb, false, now() - interval '70 days', now() - interval '18 days'),
  (900022, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, 'Lithium battery racks 215kWh', 6, 'set', '{"chemistry":"LFP","warranty":"5y"}'::jsonb, false, now() - interval '70 days', now() - interval '18 days'),
  (900031, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, 'Hybrid SUV (3-year lease)', 12, 'ea', '{"mileageCap":"25000km/yr"}'::jsonb, false, now() - interval '90 days', now() - interval '7 days'),
  (900032, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, 'Maintenance and roadside assistance package', 12, 'ea', '{"coverage":"24/7"}'::jsonb, true, now() - interval '90 days', now() - interval '7 days')
ON CONFLICT (id) DO NOTHING;

-- Invites (shortlist)
INSERT INTO rfq_invites (invite_id, rfq_id, vendor_id, status, sent_at, created_at) VALUES
  ('0194a8b2-20a1-7123-8123-123456789a20'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, '0194a8b2-1001-7123-8123-123456789a01'::uuid, 'invited', now(), now()),
  ('0194a8b2-20a2-7123-8123-123456789a21'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, '0194a8b2-1002-7123-8123-123456789a02'::uuid, 'opened',  now() - interval '1 day', now()),
  ('0194a8b2-20a3-7123-8123-123456789a22'::uuid, '0194a8b2-00f2-7123-8123-123456789a11'::uuid, '0194a8b2-1003-7123-8123-123456789a03'::uuid, 'invited', now(), now()),
  ('0194a8b2-20a4-7123-8123-123456789a23'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, '0194a8b2-1001-7123-8123-123456789a01'::uuid, 'responded', now() - interval '55 days', now() - interval '55 days'),
  ('0194a8b2-20a5-7123-8123-123456789a24'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, '0194a8b2-1004-7123-8123-123456789a04'::uuid, 'responded', now() - interval '55 days', now() - interval '55 days'),
  ('0194a8b2-20a6-7123-8123-123456789a25'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, '0194a8b2-1005-7123-8123-123456789a05'::uuid, 'invited', now() - interval '55 days', now() - interval '55 days'),
  ('0194a8b2-20a7-7123-8123-123456789a26'::uuid, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, '0194a8b2-1002-7123-8123-123456789a02'::uuid, 'responded', now() - interval '80 days', now() - interval '80 days'),
  ('0194a8b2-20a8-7123-8123-123456789a27'::uuid, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, '0194a8b2-1004-7123-8123-123456789a04'::uuid, 'responded', now() - interval '80 days', now() - interval '80 days'),
  ('0194a8b2-20a9-7123-8123-123456789a28'::uuid, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, '0194a8b2-1005-7123-8123-123456789a05'::uuid, 'declined', now() - interval '78 days', now() - interval '78 days')
ON CONFLICT (invite_id) DO NOTHING;

-- Vendor portal setup: Vendors group, vendor users, permissions, and invite linkage
-- Password hash for 'testvendor' (bcrypt): generated at build time
-- $2y$10$zjHY0e0NqEQrLzttwRol2.xGrujqyGcn7MOShokYbAbk4yCszWx82

-- 1) Ensure Vendors group exists for Cybex PTE LTD
INSERT INTO groups (group_id, name, description, org_id)
VALUES (uuid_generate_v7(), 'Vendors', 'Vendor portal users with access to My RFQs', '0194a8b2-1234-7123-8123-123456789abc'::uuid)
ON CONFLICT DO NOTHING;

-- 2) Create vendor user accounts (one per demo vendor), add email and password
WITH vendor_users AS (
  INSERT INTO users (user_id, full_name)
  VALUES
    (uuid_generate_v7(), 'Vendor: Pacific IT Solutions'),
    (uuid_generate_v7(), 'Vendor: Island Supplies Ltd'),
    (uuid_generate_v7(), 'Vendor: Coral Tech Traders'),
    (uuid_generate_v7(), 'Vendor: Blue Lagoon Engineering'),
    (uuid_generate_v7(), 'Vendor: Sunrise Logistics Co')
  ON CONFLICT DO NOTHING
  RETURNING user_id, full_name
), emails AS (
  INSERT INTO user_emails (user_id, email)
  SELECT user_id,
         CASE full_name
           WHEN 'Vendor: Pacific IT Solutions' THEN 'vendor.pacificit@cybexpte.com'
           WHEN 'Vendor: Island Supplies Ltd'   THEN 'vendor.islandsupplies@cybexpte.com'
           WHEN 'Vendor: Coral Tech Traders'    THEN 'vendor.coraltech@cybexpte.com'
           WHEN 'Vendor: Blue Lagoon Engineering' THEN 'vendor.bluelagoon@cybexpte.com'
           WHEN 'Vendor: Sunrise Logistics Co'  THEN 'vendor.sunrise@cybexpte.com'
         END AS email
  FROM vendor_users
  ON CONFLICT DO NOTHING
  RETURNING user_id
), pw AS (
  INSERT INTO user_passwords (user_id, password_hash, algo)
  SELECT user_id, '$2y$10$zjHY0e0NqEQrLzttwRol2.xGrujqyGcn7MOShokYbAbk4yCszWx82', 'bcrypt'
  FROM emails
  ON CONFLICT DO NOTHING
  RETURNING user_id
), org_members AS (
  -- Add to Cybex PTE LTD org
  INSERT INTO user_org_memberships (user_id, org_id, role)
  SELECT user_id, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Vendor' FROM pw
  ON CONFLICT DO NOTHING
  RETURNING user_id
)
SELECT 1;

-- 3) Add vendor users to the Vendors group
INSERT INTO user_group_memberships (user_id, group_id)
SELECT u.user_id, g.group_id
FROM users u
JOIN user_emails ue ON ue.user_id = u.user_id
JOIN groups g ON g.name = 'Vendors' AND g.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
WHERE ue.email IN (
  'vendor.pacificit@cybexpte.com',
  'vendor.islandsupplies@cybexpte.com',
  'vendor.coraltech@cybexpte.com',
  'vendor.bluelagoon@cybexpte.com',
  'vendor.sunrise@cybexpte.com'
)
ON CONFLICT DO NOTHING;

-- 4) Grant Vendors group access to the My RFQs (Vendor RFQs) module
INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT g.group_id, '0194a8b2-00a1-7123-8123-123456789abc'::uuid, 'allow'
FROM groups g
WHERE g.name = 'Vendors' AND g.org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid
ON CONFLICT DO NOTHING;

-- 5) Link rfq_invites to vendor user accounts for scoping (vendor_user_id)
WITH mapping(vendor_name, email) AS (
  VALUES
  ('Pacific IT Solutions',      'vendor.pacificit@cybexpte.com'),
  ('Island Supplies Ltd',       'vendor.islandsupplies@cybexpte.com'),
  ('Coral Tech Traders',        'vendor.coraltech@cybexpte.com'),
  ('Blue Lagoon Engineering',   'vendor.bluelagoon@cybexpte.com'),
  ('Sunrise Logistics Co',      'vendor.sunrise@cybexpte.com')
)
UPDATE rfq_invites ri
SET vendor_user_id = ue.user_id
FROM vendors v
JOIN mapping m ON m.vendor_name = v.vendorName
JOIN user_emails ue ON ue.email = m.email
WHERE ri.vendor_id = v.vendor_id
  AND ri.vendor_user_id IS NULL;

-- Conversations (will auto-create as needed by APIs, but seed visible ones)
INSERT INTO rfq_conversations (conversation_id, rfq_id, audience, subject, created_at) VALUES
  ('0194a8b2-10a1-7123-8123-123456789a30'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'internal', 'Internal notes', now()),
  ('0194a8b2-10a2-7123-8123-123456789a31'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'all_vendors', 'Announcements', now()),
  ('0194a8b2-10a3-7123-8123-123456789a32'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'vendor', 'Q&A: Pacific IT Solutions', now()),
  ('0194a8b2-10a4-7123-8123-123456789a33'::uuid, '0194a8b2-00f2-7123-8123-123456789a11'::uuid, 'all_vendors', 'Announcements', now()),
  ('0194a8b2-10a5-7123-8123-123456789a34'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, 'internal', 'Microgrid evaluation notes', now() - interval '30 days'),
  ('0194a8b2-10a6-7123-8123-123456789a35'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, 'all_vendors', 'Clarifications & addenda', now() - interval '32 days'),
  ('0194a8b2-10a7-7123-8123-123456789a36'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, 'vendor', 'Q&A: Blue Lagoon Engineering', now() - interval '34 days'),
  ('0194a8b2-10a8-7123-8123-123456789a37'::uuid, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, 'all_vendors', 'Fleet tender updates', now() - interval '60 days')
ON CONFLICT (conversation_id) DO NOTHING;

-- Messages (internal, vendor Q&A, broadcasts, and a pending vendor broadcast reply)
INSERT INTO rfq_messages (message_id, conversation_id, rfq_id, author_type, author_user_id, vendor_id, category, status, is_anonymous, body_markdown, created_at) VALUES
  -- Internal note
  ('0194a8b2-30a1-7123-8123-123456789a40'::uuid, '0194a8b2-10a1-7123-8123-123456789a30'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'internal', NULL, NULL, 'note', 'approved', false, 'Kickoff: validate specs before publish.', now()),
  -- Vendor question in vendor conversation (from Pacific IT)
  ('0194a8b2-30a2-7123-8123-123456789a41'::uuid, '0194a8b2-10a3-7123-8123-123456789a32'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'vendor', NULL, '0194a8b2-1001-7123-8123-123456789a01'::uuid, 'question', 'approved', false, 'Q: Do you accept equivalent brand XYZ?', now()),
  -- Requester answer in vendor conversation
  ('0194a8b2-30a3-7123-8123-123456789a42'::uuid, '0194a8b2-10a3-7123-8123-123456789a32'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'internal', NULL, '0194a8b2-1001-7123-8123-123456789a01'::uuid, 'answer', 'approved', false, 'A: Yes, equivalent Tier-1 brands are acceptable.', now()),
  -- Broadcast copy of the answer (anonymous)
  ('0194a8b2-30a4-7123-8123-123456789a43'::uuid, '0194a8b2-10a2-7123-8123-123456789a31'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'internal', NULL, NULL, 'clarification', 'approved', true, 'Clarification: Equivalent Tier-1 brands are acceptable.', now()),
  -- Vendor broadcast reply (pending moderation)
  ('0194a8b2-30a5-7123-8123-123456789a44'::uuid, '0194a8b2-10a2-7123-8123-123456789a31'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, 'vendor', NULL, '0194a8b2-1002-7123-8123-123456789a02'::uuid, 'reply', 'pending', false, 'Vendor reply: Please confirm lead time expectations.', now()),
  -- Microgrid internal evaluation note
  ('0194a8b2-30b1-7123-8123-123456789a45'::uuid, '0194a8b2-10a5-7123-8123-123456789a34'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, 'internal', '0194a8b2-4450-7123-8123-123456789ac3'::uuid, NULL, 'note', 'approved', false, 'Shortlist Blue Lagoon and Pacific IT based on technical scores.', now() - interval '26 days'),
  -- Microgrid vendor question
  ('0194a8b2-30b2-7123-8123-123456789a46'::uuid, '0194a8b2-10a7-7123-8123-123456789a36'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, 'vendor', NULL, '0194a8b2-1004-7123-8123-123456789a04'::uuid, 'question', 'approved', false, 'Q: Can deliveries be split into two milestones?', now() - interval '32 days'),
  -- Microgrid answer broadcast to all vendors
  ('0194a8b2-30b3-7123-8123-123456789a47'::uuid, '0194a8b2-10a6-7123-8123-123456789a35'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, 'internal', '0194a8b2-4450-7123-8123-123456789ac3'::uuid, NULL, 'clarification', 'approved', true, 'Deliveries may be split 70/30 with milestone-based payments.', now() - interval '31 days'),
  -- Fleet tender award notice
  ('0194a8b2-30b4-7123-8123-123456789a48'::uuid, '0194a8b2-10a8-7123-8123-123456789a37'::uuid, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, 'internal', '0194a8b2-4450-7123-8123-123456789ac3'::uuid, NULL, 'announcement', 'approved', false, 'Tender box opened and award letter issued to Blue Lagoon Engineering.', now() - interval '42 days')
ON CONFLICT (message_id) DO NOTHING;

-- Quotes for RFQ1
INSERT INTO vendor_quotes (quote_id, rfq_id, invite_id, vendor_id, currency, total_amount, tax_total, delivery_days, valid_thru, notes, revision_no, status, created_at, updatedAt) VALUES
  ('0194a8b2-40a1-7123-8123-123456789a50'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, '0194a8b2-20a1-7123-8123-123456789a20'::uuid, '0194a8b2-1001-7123-8123-123456789a01'::uuid, 'FJD', 125000.00, 15000.00, 21, (current_date + 30), 'Includes onsite install', 1, 'submitted', now(), now()),
  ('0194a8b2-40a2-7123-8123-123456789a51'::uuid, '0194a8b2-00f1-7123-8123-123456789a10'::uuid, '0194a8b2-20a2-7123-8123-123456789a21'::uuid, '0194a8b2-1002-7123-8123-123456789a02'::uuid, 'FJD', 118000.00, 14160.00, 28, (current_date + 45), 'Alternative brand offered', 1, 'submitted', now(), now()),
  ('0194a8b2-40a3-7123-8123-123456789a52'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, '0194a8b2-20a5-7123-8123-123456789a24'::uuid, '0194a8b2-1004-7123-8123-123456789a04'::uuid, 'FJD', 295000.00, 38400.00, 120, (current_date - 10), 'Includes design optimization and onsite training', 2, 'submitted', now() - interval '45 days', now() - interval '18 days'),
  ('0194a8b2-40a4-7123-8123-123456789a53'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, '0194a8b2-20a4-7123-8123-123456789a23'::uuid, '0194a8b2-1001-7123-8123-123456789a01'::uuid, 'FJD', 312500.00, 40625.00, 135, (current_date - 8), 'Extended warranty requested', 1, 'submitted', now() - interval '46 days', now() - interval '17 days'),
  ('0194a8b2-40a5-7123-8123-123456789a54'::uuid, '0194a8b2-00f3-7123-8123-123456789a12'::uuid, '0194a8b2-20a6-7123-8123-123456789a25'::uuid, '0194a8b2-1005-7123-8123-123456789a05'::uuid, 'FJD', 340000.00, 44200.00, 140, (current_date - 12), 'Pending board approval', 1, 'withdrawn', now() - interval '44 days', now() - interval '30 days'),
  ('0194a8b2-40a6-7123-8123-123456789a55'::uuid, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, '0194a8b2-20a8-7123-8123-123456789a27'::uuid, '0194a8b2-1004-7123-8123-123456789a04'::uuid, 'FJD', 98000.00, 11760.00, 30, (current_date - 3), 'Includes replacement vehicles clause', 3, 'final', now() - interval '85 days', now() - interval '2 days'),
  ('0194a8b2-40a7-7123-8123-123456789a56'::uuid, '0194a8b2-00f4-7123-8123-123456789a13'::uuid, '0194a8b2-20a7-7123-8123-123456789a26'::uuid, '0194a8b2-1002-7123-8123-123456789a02'::uuid, 'FJD', 102500.00, 12812.50, 35, (current_date - 5), 'Recommends quarterly service reviews', 2, 'submitted', now() - interval '86 days', now() - interval '6 days')
ON CONFLICT (quote_id) DO NOTHING;

INSERT INTO vendor_quote_items (quote_item_id, quote_id, rfq_item_id, is_alternate, alt_label, unit_price, tax, lead_time_days, notes) VALUES
  ('0194a8b2-50a1-7123-8123-123456789a60'::uuid, '0194a8b2-40a1-7123-8123-123456789a50'::uuid, 900001, false, NULL, 10500.00, 0.15, 21, 'Main switches'),
  ('0194a8b2-50a2-7123-8123-123456789a61'::uuid, '0194a8b2-40a1-7123-8123-123456789a50'::uuid, 900002, false, NULL, 750.00, 0.15, 21, 'SFP+ SR'),
  ('0194a8b2-50a3-7123-8123-123456789a62'::uuid, '0194a8b2-40a2-7123-8123-123456789a51'::uuid, 900001, false, NULL, 9900.00, 0.12, 30, 'Alt brand'),
  ('0194a8b2-50a4-7123-8123-123456789a63'::uuid, '0194a8b2-40a2-7123-8123-123456789a51'::uuid, 900002, false, NULL, 700.00, 0.12, 30, 'SR transceiver'),
  ('0194a8b2-50a5-7123-8123-123456789a64'::uuid, '0194a8b2-40a3-7123-8123-123456789a52'::uuid, 900021, false, NULL, 1420.00, 0.13, 120, 'Tier-1 panels with 10-year warranty'),
  ('0194a8b2-50a6-7123-8123-123456789a65'::uuid, '0194a8b2-40a3-7123-8123-123456789a52'::uuid, 900022, false, NULL, 28500.00, 0.13, 150, 'Battery racks including BMS integration'),
  ('0194a8b2-50a7-7123-8123-123456789a66'::uuid, '0194a8b2-40a4-7123-8123-123456789a53'::uuid, 900021, false, NULL, 1500.00, 0.15, 140, 'Panels with extended warranty'),
  ('0194a8b2-50a8-7123-8123-123456789a67'::uuid, '0194a8b2-40a4-7123-8123-123456789a53'::uuid, 900022, false, NULL, 31000.00, 0.15, 160, 'Battery racks with onsite commissioning'),
  ('0194a8b2-50a9-7123-8123-123456789a68'::uuid, '0194a8b2-40a5-7123-8123-123456789a54'::uuid, 900021, true, 'Staggered delivery', 1380.00, 0.12, 150, 'Alternate delivery schedule proposed'),
  ('0194a8b2-50b0-7123-8123-123456789a69'::uuid, '0194a8b2-40a6-7123-8123-123456789a55'::uuid, 900031, false, NULL, 6800.00, 0.12, 30, 'Monthly lease per vehicle'),
  ('0194a8b2-50b1-7123-8123-123456789a6a'::uuid, '0194a8b2-40a6-7123-8123-123456789a55'::uuid, 900032, false, NULL, 350.00, 0.12, 30, 'Maintenance per vehicle'),
  ('0194a8b2-50b2-7123-8123-123456789a6b'::uuid, '0194a8b2-40a7-7123-8123-123456789a56'::uuid, 900031, false, NULL, 7050.00, 0.12, 35, 'Monthly lease per vehicle (Island Supplies)'),
  ('0194a8b2-50b3-7123-8123-123456789a6c'::uuid, '0194a8b2-40a7-7123-8123-123456789a56'::uuid, 900032, false, NULL, 375.00, 0.12, 35, 'Maintenance bundle')
ON CONFLICT (quote_item_id) DO NOTHING;

-- ------------------------------------------------------------------
-- Vendor response for Two-Envelope RFQ (technical + financial files)
-- RFQ: 0194a8b2-00f5-7123-8123-123456789a14 (Digital Banking App RFP)
-- Vendor: Pacific IT Solutions
-- ------------------------------------------------------------------
DO $$
DECLARE
  v_rfq         uuid := '0194a8b2-00f5-7123-8123-123456789a14'::uuid;
  v_org         uuid := '0194a8b2-1234-7123-8123-123456789abc'::uuid; -- Cybex PTE LTD
  v_vendor      uuid;
  v_invite      uuid;
  v_quote       uuid;
  v_attachments jsonb;
  tech_md text := $tech$
# Technical Proposal  
**Project:** Mobile Banking App Design, Build, and Year-1 Support  

---

## 1. Executive Summary  
This proposal outlines the design, development, and delivery of a **mobile banking application**, along with **Year-1 support and maintenance services**. The solution aims to provide secure, scalable, and user-friendly banking experiences to meet modern customer expectations while ensuring compliance with industry standards.  

---

## 2. Scope of Work  

### Phase 1: Design & Build (Lot)  
- Requirements gathering & stakeholder workshops  
- UX/UI design (modern, accessible, responsive)  
- Mobile app development (iOS & Android)  
- Core features:  
  - Secure login & authentication (MFA/biometrics)  
  - Account overview & transactions  
  - Funds transfer & bill payments  
  - Notifications & alerts  
- API integrations with core banking system  
- Security hardening & penetration testing  
- User Acceptance Testing (UAT) & deployment  

### Phase 2: Year-1 Support & Maintenance (12 months)  
- 24/7 monitoring & incident response  
- Bug fixing & patch releases  
- Performance optimization  
- Security updates & compliance checks  
- Quarterly feature enhancements (minor improvements)  
- Monthly reporting on app health and usage  

---

## 3. Deliverables  
- Fully functional mobile banking application (iOS/Android)  
- Technical documentation & user guides  
- Deployment pipeline & CI/CD setup  
- Monthly support and maintenance reports  

---

## 4. Project Timeline (Indicative)  
- **Month 1–2:** Requirements & design  
- **Month 3–6:** Development & integration  
- **Month 7:** Testing & security audits  
- **Month 8:** Go-live  
- **Month 9–20:** Year-1 support and maintenance  

---

## 5. Assumptions & Dependencies  
- Timely access to core banking APIs and sandbox environments  
- Client to provide branding assets and compliance requirements  
- Any third-party licenses or integrations outside the agreed scope will be handled separately  

---

## 6. Support Model  
- Helpdesk with defined SLAs (response within 2 hrs for critical issues)  
- Monthly review calls with client’s IT team  
- Ticketing system for issue tracking  

---

## 7. Acceptance Criteria  
- All agreed features are delivered and tested successfully  
- App passes penetration testing and compliance checks  
- Client sign-off after UAT  

---

## 8. Optional Component  
- **Year-1 Support and Maintenance** is optional but highly recommended to ensure app stability, security, and performance.  

---

## 9. Conclusion  
This proposal demonstrates a structured approach to building a secure, modern mobile banking app, followed by dedicated support to ensure continuous improvement.
$tech$;
  fin_md  text := $fin$
# Financial Proposal  

| Item                                | Quantity | Unit   | Rate (USD) | Total (USD) |
|-------------------------------------|----------|--------|------------|-------------|
| Mobile banking app design & build   | 1 lot    | lot    | 75,000     | 75,000      |
| Year-1 support & maintenance        | 12 mo    | month  | 2,500      | 30,000      |
| **Total**                           |          |        |            | **105,000** |

---

### Notes  
- Rates are indicative and for demo purposes only.  
- Taxes, VAT, and third-party licenses (if any) are excluded.  
- Support beyond Year-1 will be renegotiated.
$fin$;
BEGIN
  -- Ensure vendor exists
  SELECT vendor_id INTO v_vendor FROM vendors
  WHERE org_id = v_org AND vendorName = 'Pacific IT Solutions'
  LIMIT 1;
  IF v_vendor IS NULL THEN
    RAISE NOTICE 'Vendor not found; skipping vendor response seed';
    RETURN;
  END IF;

  -- Ensure invite exists (capture id for quote linkage)
  SELECT invite_id INTO v_invite FROM rfq_invites WHERE rfq_id = v_rfq AND vendor_id = v_vendor;
  IF v_invite IS NULL THEN
    INSERT INTO rfq_invites(invite_id, rfq_id, vendor_id, status, sent_at, created_at)
    VALUES (uuid_generate_v7(), v_rfq, v_vendor, 'responded', now() - interval '1 day', now() - interval '1 day')
    RETURNING invite_id INTO v_invite;
  ELSE
    -- Mark as responded for demo visibility
    UPDATE rfq_invites SET status = 'responded', responded_at = now()
    WHERE invite_id = v_invite AND (responded_at IS NULL OR status <> 'responded');
  END IF;

  -- Link invite to vendor portal user if missing
  UPDATE rfq_invites ri
  SET vendor_user_id = ue.user_id
  FROM user_emails ue
  WHERE ri.invite_id = v_invite
    AND ri.vendor_user_id IS NULL
    AND ue.email = 'vendor.pacificit@cybexpte.com';

  -- Build two-envelope attachments JSON
  v_attachments := jsonb_build_array(
    jsonb_build_object(
      'label','Technical Proposal',
      'envelope','technical',
      'filetype','md',
      'filename','mobile_banking_proposal.md',
      'media_type','text/markdown',
      'file_url','/storage/uploads/documents/mobile_banking_proposal.md'
    ),
    jsonb_build_object(
      'label','Financial Proposal',
      'envelope','financial',
      'filetype','md',
      'filename','financial_proposal.md',
      'media_type','text/markdown',
      'file_url','/storage/uploads/documents/financial_proposal.md'
    )
  );

  -- Upsert vendor quote header
  SELECT quote_id INTO v_quote FROM vendor_quotes WHERE rfq_id = v_rfq AND vendor_id = v_vendor ORDER BY created_at ASC LIMIT 1;
  IF v_quote IS NULL THEN
    INSERT INTO vendor_quotes (quote_id, rfq_id, invite_id, vendor_id, currency, total_amount, tax_total, delivery_days, valid_thru, notes, revision_no, status, attachments, created_at, updatedAt)
  VALUES (uuid_generate_v7(), v_rfq, v_invite, v_vendor, 'FJD', 105000.00, 0.00, 120, (current_date + 45), 'Two-envelope submission with technical and financial proposals attached.', 1, 'submitted', v_attachments, now(), now())
    RETURNING quote_id INTO v_quote;
  ELSE
    -- Ensure attachments present and status is submitted
    UPDATE vendor_quotes
    SET attachments = COALESCE(attachments, '[]'::jsonb) || v_attachments,
        status = CASE WHEN status = 'draft' THEN 'submitted' ELSE status END,
        updatedAt = now()
    WHERE quote_id = v_quote
      AND NOT EXISTS (
        SELECT 1 FROM jsonb_array_elements(COALESCE(attachments,'[]'::jsonb)) elem
        WHERE (elem->>'label') IN ('Technical Proposal','Financial Proposal')
      );
  END IF;

  -- Quote line items mapped to RFQ items by description (idempotent)
  -- 1) Mobile banking app design and build -> 75,000 FJD (lot)
  INSERT INTO vendor_quote_items (quote_item_id, quote_id, rfq_item_id, is_alternate, alt_label, unit_price, tax, lead_time_days, notes)
  SELECT uuid_generate_v7(), v_quote, i.id, false, NULL, 75000.00, 0.00, 90, 'Per scope of work'
  FROM rfq_items i
  WHERE i.rfq_id = v_rfq AND i.description = 'Mobile banking app design and build'
    AND NOT EXISTS (
      SELECT 1 FROM vendor_quote_items vqi WHERE vqi.quote_id = v_quote AND vqi.rfq_item_id = i.id
    );

  -- 2) Year-1 support and maintenance -> 2,500 FJD per month (12 months in RFQ item qty)
  INSERT INTO vendor_quote_items (quote_item_id, quote_id, rfq_item_id, is_alternate, alt_label, unit_price, tax, lead_time_days, notes)
  SELECT uuid_generate_v7(), v_quote, i.id, false, NULL, 2500.00, 0.00, 30, 'Monthly support rate'
  FROM rfq_items i
  WHERE i.rfq_id = v_rfq AND i.description = 'Year-1 support and maintenance'
    AND NOT EXISTS (
      SELECT 1 FROM vendor_quote_items vqi WHERE vqi.quote_id = v_quote AND vqi.rfq_item_id = i.id
    );
END $$;

-- Demo salaries for test users
INSERT INTO salaries (salary_id, user_id, org_id, salary_type, rate, currency, effective_date, isDeleted, updatedAt) VALUES
(uuid_generate_v7(), '0194a8b2-4450-7123-8123-123456789ac3'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'hourly', 25.00, 'FJD', '2025-01-01', FALSE, NOW()),
(uuid_generate_v7(), '0194a8b2-4451-7123-8123-123456789ac3'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'hourly', 30.00, 'FJD', '2025-01-01', FALSE, NOW()),
(uuid_generate_v7(), '0194a8b2-4452-7123-8123-123456789ac3'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'hourly', 22.50, 'FJD', '2025-01-01', FALSE, NOW()),
(uuid_generate_v7(), '0194a8b2-4453-7123-8123-123456789ac3'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'hourly', 28.00, 'FJD', '2025-01-01', FALSE, NOW()),
(uuid_generate_v7(), '0194a8b2-4454-7123-8123-123456789ac3'::uuid, '0194a8b2-1234-7123-8123-123456789abc'::uuid, 'hourly', 26.00, 'FJD', '2025-01-01', FALSE, NOW())
ON CONFLICT DO NOTHING;

-- Demo deduction types and rates
INSERT INTO deduction_types (deduction_type_id, name, org_id, isDeleted, updatedAt) VALUES
(uuid_generate_v7(), 'Tax', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW()),
(uuid_generate_v7(), 'FNPF', '0194a8b2-1234-7123-8123-123456789abc'::uuid, FALSE, NOW())
ON CONFLICT DO NOTHING;

-- Tax brackets (progressive rates)
INSERT INTO deduction_rates (deduction_rate_id, deduction_type_id, start_salary, end_salary, rate, isDeleted, updatedAt) VALUES
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 0.00, 30000.00, 0.00, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 30000.01, 50000.00, 0.18, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 50000.01, 270000.00, 0.20, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 270000.01, 300000.00, 0.20, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 300000.01, 350000.00, 0.20, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 350000.01, 400000.00, 0.20, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 400000.01, 450000.00, 0.20, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 450000.01, 500000.00, 0.20, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 500000.01, 1000000.00, 0.20, FALSE, NOW()),
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'Tax' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 1000000.01, 99999999.99, 0.20, FALSE, NOW()),
-- FNPF at 8%
(uuid_generate_v7(), (SELECT deduction_type_id FROM deduction_types WHERE name = 'FNPF' AND org_id = '0194a8b2-1234-7123-8123-123456789abc'::uuid LIMIT 1), 0, 99999999.99, 0.08, FALSE, NOW())
ON CONFLICT DO NOTHING;

-- Demo time clock aggregates for new users (September 9-12, 2025)
INSERT INTO time_clock_aggregates (time_clock_aggregate_id, org_id, user_id, start_date, end_date, total_duration_minutes, total_net_minutes, total_overtime_minutes, total_regular_minutes, total_payable_minutes, total_days_worked)
VALUES
  (uuid_generate_v7(), '0194a8b2-1234-7123-8123-123456789abc'::uuid, '0194a8b2-4450-7123-8123-123456789ac3'::uuid, '2025-09-09', '2025-09-12', 1920, 1920, 0, 1920, 1920, 4),
  (uuid_generate_v7(), '0194a8b2-1234-7123-8123-123456789abc'::uuid, '0194a8b2-4451-7123-8123-123456789ac3'::uuid, '2025-09-09', '2025-09-12', 1920, 1920, 0, 1920, 1920, 4),
  (uuid_generate_v7(), '0194a8b2-1234-7123-8123-123456789abc'::uuid, '0194a8b2-4452-7123-8123-123456789ac3'::uuid, '2025-09-09', '2025-09-12', 1920, 1920, 0, 1920, 1920, 4),
  (uuid_generate_v7(), '0194a8b2-1234-7123-8123-123456789abc'::uuid, '0194a8b2-4453-7123-8123-123456789ac3'::uuid, '2025-09-09', '2025-09-12', 1920, 1920, 0, 1920, 1920, 4),
  (uuid_generate_v7(), '0194a8b2-1234-7123-8123-123456789abc'::uuid, '0194a8b2-4454-7123-8123-123456789ac3'::uuid, '2025-09-09', '2025-09-12', 1920, 1920, 0, 1920, 1920, 4)
ON CONFLICT DO NOTHING;

-- ============================================================
-- Demo Procurement Data for Analytics Modules
-- Supporting the five new Reporting and Analysis Procurement modules
-- ============================================================

-- Additional Procurement Requests for analytics
INSERT INTO procurement_requests (
  org_id, description, justification, estimated_cost, quantity, preferred_vendor, 
  needed_by, project_id, inventory_item_id, location_id, status, 
  requester_id
) VALUES
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Laptop Computers for Development Team', 'Replace aging hardware for improved productivity', 25000.00, 10, 'Pacific IT Solutions', '2025-10-15', NULL, NULL, 1, 'Approved', 1),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Office Furniture - Ergonomic Chairs', 'Improve workplace ergonomics and employee comfort', 8000.00, 20, 'Island Supplies Ltd', '2025-09-30', NULL, NULL, 1, 'Pending', 2),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Network Security Software Licenses', 'Annual renewal of enterprise security suite', 15000.00, 1, 'Coral Tech Traders', '2025-11-01', NULL, NULL, 1, 'In Review', 1),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Vehicle Fleet Maintenance Supplies', 'Quarterly stock of maintenance parts and fluids', 5500.00, 100, NULL, '2025-10-01', NULL, NULL, 2, 'Approved', 3),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Conference Room AV Equipment', 'Upgrade presentation systems in meeting rooms', 12000.00, 4, 'Pacific IT Solutions', '2025-12-15', NULL, NULL, 1, 'Pending', 2),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Catering Services for Annual Conference', 'Corporate event catering for 200 attendees', 18000.00, 1, NULL, '2025-11-20', NULL, NULL, 1, 'Draft', 4),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Cloud Storage Subscription Upgrade', 'Increase storage capacity for document management', 3600.00, 1, 'Coral Tech Traders', '2025-10-01', NULL, NULL, 1, 'Approved', 1),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Medical Supplies for First Aid Stations', 'Quarterly restocking of workplace first aid kits', 850.00, 25, 'Island Supplies Ltd', '2025-09-25', NULL, NULL, 1, 'Completed', 5),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Marketing Materials and Brochures', 'Q4 marketing campaign materials printing', 4200.00, 5000, NULL, '2025-10-30', NULL, NULL, 1, 'In Review', 4),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Professional Development Training', 'Technical certification courses for IT staff', 9500.00, 8, 'Pacific IT Solutions', '2025-11-30', NULL, NULL, 1, 'Pending', 1),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Backup Generator Maintenance', 'Annual service contract for emergency power systems', 7200.00, 1, NULL, '2025-10-15', NULL, NULL, 2, 'Approved', 3),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Software Development Tools', 'IDE licenses and development environment setup', 6800.00, 15, 'Coral Tech Traders', '2025-10-10', NULL, NULL, 1, 'Draft', 1),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Cleaning and Janitorial Supplies', 'Monthly supplies for facility maintenance', 1200.00, 50, 'Island Supplies Ltd', '2025-09-28', NULL, NULL, 1, 'Completed', 5),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Legal Consultation Services', 'Contract review and compliance advisory', 5000.00, 20, NULL, '2025-11-01', NULL, NULL, 1, 'Pending', 2),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, 'Data Center Cooling System Upgrade', 'Replace aging HVAC units in server room', 35000.00, 2, 'Pacific IT Solutions', '2025-12-01', NULL, NULL, 2, 'In Review', 1)
ON CONFLICT DO NOTHING;

-- Purchase Orders for analytics
INSERT INTO purchase_orders (
  org_id, vendor_id, order_date, order_number
) VALUES
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Pacific IT Solutions' LIMIT 1), '2025-08-05', 'PO-2025-001'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Island Supplies Ltd' LIMIT 1), '2025-08-20', 'PO-2025-002'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Coral Tech Traders' LIMIT 1), '2025-07-25', 'PO-2025-003'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Pacific IT Solutions' LIMIT 1), '2025-08-15', 'PO-2025-004'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Island Supplies Ltd' LIMIT 1), '2025-09-01', 'PO-2025-005'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Coral Tech Traders' LIMIT 1), '2025-08-10', 'PO-2025-006'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Pacific IT Solutions' LIMIT 1), '2025-09-05', 'PO-2025-007'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Island Supplies Ltd' LIMIT 1), '2025-07-30', 'PO-2025-008'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Coral Tech Traders' LIMIT 1), '2025-09-10', 'PO-2025-009'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Pacific IT Solutions' LIMIT 1), '2025-08-25', 'PO-2025-010'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Island Supplies Ltd' LIMIT 1), '2025-09-12', 'PO-2025-011'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Coral Tech Traders' LIMIT 1), '2025-08-18', 'PO-2025-012'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT vendor_id FROM vendors WHERE vendorName = 'Pacific IT Solutions' LIMIT 1), '2025-09-08', 'PO-2025-013')
ON CONFLICT DO NOTHING;

-- Goods Receipts for completed and partially received orders
INSERT INTO goods_receipts (
  org_id, purchase_order_id, received_date, quantity_received, notes
) VALUES
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-003' LIMIT 1), '2025-08-01', 1, 'Software licenses activated successfully'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-008' LIMIT 1), '2025-08-02', 25, 'All medical supplies received and stored'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-012' LIMIT 1), '2025-08-22', 15, 'IDE licenses distributed to development team'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-001' LIMIT 1), '2025-08-10', 8, 'Partial delivery - 8 of 10 laptops received'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-002' LIMIT 1), '2025-08-25', 20, 'All ergonomic chairs delivered and assembled'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-006' LIMIT 1), '2025-08-15', 1, 'Cloud storage upgrade activated'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-010' LIMIT 1), '2025-08-30', 2, 'New HVAC units installed in data center'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-013' LIMIT 1), '2025-09-10', 20, 'Training course enrollment confirmed'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-004' LIMIT 1), '2025-08-20', 4, 'Conference room AV equipment installed'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-001' LIMIT 1), '2025-08-15', 2, 'Remaining 2 laptops received - order complete')
ON CONFLICT DO NOTHING;

-- Vendor Invoices for payment tracking analytics
INSERT INTO vendor_invoices (
  org_id, purchase_order_id, invoice_number, invoice_date, amount, status
) VALUES
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-003' LIMIT 1), 'INV-CTT-2025-001', '2025-08-02', 14800.00, 'paid'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-008' LIMIT 1), 'INV-ISL-2025-005', '2025-08-03', 820.00, 'paid'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-012' LIMIT 1), 'INV-CTT-2025-008', '2025-08-23', 17500.00, 'paid'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-001' LIMIT 1), 'INV-PIT-2025-003', '2025-08-12', 19600.00, 'pending'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-002' LIMIT 1), 'INV-ISL-2025-007', '2025-08-26', 7800.00, 'approved'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-006' LIMIT 1), 'INV-CTT-2025-004', '2025-08-16', 3500.00, 'paid'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-010' LIMIT 1), 'INV-PIT-2025-009', '2025-09-01', 34500.00, 'pending'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-013' LIMIT 1), 'INV-PIT-2025-011', '2025-09-11', 7100.00, 'approved'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-004' LIMIT 1), 'INV-PIT-2025-007', '2025-08-21', 11500.00, 'paid'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-001' LIMIT 1), 'INV-PIT-2025-004', '2025-08-16', 4900.00, 'pending'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-007' LIMIT 1), 'INV-PIT-2025-010', '2025-09-06', 9200.00, 'overdue'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-011' LIMIT 1), 'INV-ISL-2025-012', '2025-09-13', 4950.00, 'pending'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-005' LIMIT 1), 'INV-ISL-2025-009', '2025-09-02', 5200.00, 'rejected'),
  ('0194a8b2-1234-7123-8123-123456789abc'::uuid, (SELECT purchase_order_id FROM purchase_orders WHERE order_number = 'PO-2025-009' LIMIT 1), 'INV-CTT-2025-010', '2025-09-11', 6650.00, 'approved')
ON CONFLICT DO NOTHING;

-- Compliance documents for Supplier Risk (mix of valid/expired)
INSERT INTO vendor_documents (vendor_document_id, vendor_id, doc_type, validity, isDeleted, updatedAt)
VALUES
  (uuid_generate_v7(), (SELECT vendor_id FROM vendors WHERE vendorName='Pacific IT Solutions' LIMIT 1), 'business_license', (current_date + 180), FALSE, now()),
  (uuid_generate_v7(), (SELECT vendor_id FROM vendors WHERE vendorName='Pacific IT Solutions' LIMIT 1), 'insurance_certificate', (current_date - 5), FALSE, now()),
  (uuid_generate_v7(), (SELECT vendor_id FROM vendors WHERE vendorName='Island Supplies Ltd' LIMIT 1), 'business_license', (current_date + 90), FALSE, now()),
  (uuid_generate_v7(), (SELECT vendor_id FROM vendors WHERE vendorName='Coral Tech Traders' LIMIT 1), 'business_license', (current_date + 365), FALSE, now())
ON CONFLICT DO NOTHING;

-- Budget data for Cost Optimization Lab analytics
INSERT INTO budgets (
  department, project, costCenter, accountCode, timePeriod, budgetType, 
  category, budgetedAmount, actualAmount, isDeleted, updatedAt
) VALUES
  ('IT', 'Infrastructure Upgrade', 'CC-001', 'ACC-IT-001', 'Quarterly', 'Initial', 'Hardware', 50000.00, 36000.00, FALSE, NOW()),
  ('IT', 'Software Licensing', 'CC-001', 'ACC-IT-002', 'Annual', 'Initial', 'Software', 25000.00, 18300.00, FALSE, NOW()),
  ('Operations', 'Facility Maintenance', 'CC-002', 'ACC-OPS-001', 'Monthly', 'Initial', 'Maintenance', 8000.00, 6850.00, FALSE, NOW()),
  ('HR', 'Training and Development', 'CC-003', 'ACC-HR-001', 'Quarterly', 'Initial', 'Training', 15000.00, 9500.00, FALSE, NOW()),
  ('Finance', 'Professional Services', 'CC-004', 'ACC-FIN-001', 'Quarterly', 'Initial', 'Consulting', 12000.00, 5000.00, FALSE, NOW()),
  ('Operations', 'Office Supplies', 'CC-002', 'ACC-OPS-002', 'Monthly', 'Initial', 'Supplies', 3000.00, 2850.00, FALSE, NOW()),
  ('IT', 'Cloud Services', 'CC-001', 'ACC-IT-003', 'Monthly', 'Revised', 'Services', 4000.00, 3500.00, FALSE, NOW()),
  ('Marketing', 'Promotional Materials', 'CC-005', 'ACC-MKT-001', 'Quarterly', 'Initial', 'Marketing', 6000.00, 4200.00, FALSE, NOW()),
  ('Operations', 'Vehicle Maintenance', 'CC-002', 'ACC-OPS-003', 'Quarterly', 'Initial', 'Fleet', 18000.00, 12350.00, FALSE, NOW()),
  ('IT', 'Data Center Operations', 'CC-001', 'ACC-IT-004', 'Annual', 'Initial', 'Infrastructure', 60000.00, 34500.00, FALSE, NOW())
ON CONFLICT DO NOTHING;

-- Additional spend category data for analytics
INSERT INTO items (itemName, price, vatPercent, vatIncluded) VALUES
  ('Enterprise Laptop', 2450.00, 15, 'No'),
  ('Ergonomic Office Chair', 390.00, 15, 'No'),
  ('Software License - Security Suite', 14800.00, 0, 'Yes'),
  ('Conference Room Display', 2875.00, 15, 'No'),
  ('Medical First Aid Kit', 32.80, 15, 'Yes'),
  ('Professional Training Course', 1187.50, 0, 'Yes'),
  ('Cloud Storage Subscription', 3500.00, 0, 'Yes'),
  ('HVAC Unit - Industrial', 17250.00, 15, 'No'),
  ('Marketing Brochures (1000 units)', 840.00, 15, 'Yes'),
  ('Legal Consultation (per hour)', 250.00, 0, 'Yes'),
  ('Vehicle Maintenance Kit', 125.00, 15, 'Yes'),
  ('IDE Software License', 1166.67, 0, 'Yes'),
  ('Cleaning Supplies Monthly Pack', 96.00, 15, 'Yes'),
  ('Network Security Appliance', 5800.00, 15, 'No'),
  ('Project Management Software', 450.00, 0, 'Yes')
ON CONFLICT DO NOTHING;

-- 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 org_id to purchase_orders if not exists
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'purchase_orders') THEN
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'purchase_orders' AND column_name = 'org_id'
    ) THEN
      EXECUTE 'ALTER TABLE purchase_orders ADD COLUMN org_id uuid NULL REFERENCES orgs(org_id) ON DELETE SET NULL';
    END IF;
  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;

  -- Add org_id to goods_receipts if the table exists and column is missing
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'goods_receipts') THEN
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'goods_receipts' AND column_name = 'org_id'
    ) THEN
      EXECUTE 'ALTER TABLE goods_receipts ADD COLUMN org_id uuid NULL REFERENCES orgs(org_id) ON DELETE SET NULL';
    END IF;
  END IF;

  -- Add org_id to vendor_invoices if the table exists and column is missing
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'vendor_invoices') THEN
    IF NOT EXISTS (
      SELECT 1 FROM information_schema.columns
      WHERE table_name = 'vendor_invoices' AND column_name = 'org_id'
    ) THEN
      EXECUTE 'ALTER TABLE vendor_invoices ADD COLUMN org_id uuid NULL REFERENCES orgs(org_id) ON DELETE SET NULL';
    END IF;
  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)
ON CONFLICT (id) DO UPDATE
SET procurement_request_id = COALESCE(rfqs.procurement_request_id, EXCLUDED.procurement_request_id);

-- Also backfill vendor invitations into rfqs to support Supplier Risk responsiveness
INSERT INTO rfqs (id, vendor_id, sent_at)
SELECT DISTINCT ON (i.rfq_id)
  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
WHERE r.id IS NULL
ORDER BY i.rfq_id, i.sent_at DESC NULLS LAST
ON CONFLICT (id) DO UPDATE
SET vendor_id = COALESCE(rfqs.vendor_id, EXCLUDED.vendor_id),
    sent_at   = COALESCE(rfqs.sent_at, EXCLUDED.sent_at);

-- 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)
-- Ensure requests land within the last 90 days window
UPDATE procurement_requests SET updatedAt = now() - interval '75 days' WHERE description = 'Office Furniture - Ergonomic Chairs' AND updatedAt > now() - interval '120 days';
UPDATE procurement_requests SET updatedAt = now() - interval '55 days' WHERE description = 'Laptop Computers for Development Team' AND updatedAt > now() - interval '120 days';
UPDATE procurement_requests SET updatedAt = now() - interval '35 days' WHERE description = 'Network Security Software Licenses' AND updatedAt > now() - interval '120 days';
UPDATE procurement_requests SET updatedAt = now() - interval '20 days' WHERE description = 'Conference Room AV Equipment' AND updatedAt > now() - interval '120 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
