INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  131,
  'Auto Order',
  'Manage Auto Orders for Products',
  'auto_orders',
  '[{"label":"Product","field_name":"product_id","data_column":"product_id","type_id":13,"options_table":"products","options_label_column":"name","options_value_column":"product_id","order_index":1},
    {"label":"Minimum Stock Qty","field_name":"minimum_stock_qty","data_column":"minimum_stock_qty","type_id":2,"order_index":2},
    {"label":"Order Qty","field_name":"order_qty","data_column":"order_qty","type_id":2,"order_index":3}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;


-- Software Info form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  112,
  'Software Info',
  'Software Information',
  'software_info',
  '[{"label":"Manufacturer Name","field_name":"manufacturer_name","data_column":"manufacturer_name","type_id":1,"order_index":1,"default_value":"Cybex PTE Limited."},
    {"label":"Serial Number","field_name":"serial_number","data_column":"serial_number","type_id":1,"order_index":2,"default_value":"CYBEXPTE-ERP-2025-001."},
    {"label":"Software Version","field_name":"software_version","data_column":"software_version","type_id":1,"order_index":3,"default_value":"2025.08"}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;


-- Insert default system info record
INSERT INTO software_info (manufacturer_name, serial_number, software_version)
SELECT 'Cybex PTE Limited.', 'CYBEXPTE-ERP-2025-001.', '2025.08'
WHERE NOT EXISTS (SELECT 1 FROM software_info LIMIT 1);

-- POS Settings form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  113,
  'POS Settings',
  'Point of Sale Settings',
  'pos_settings',
  '[{"label":"Password","field_name":"password","data_column":"password","type_id":1,"order_index":1,"default_value":"2CBP6HMW"},
    {"label":"PIN","field_name":"pin","data_column":"pin","type_id":1,"order_index":2,"default_value":"3840"},
    {"label":"SDC Base URL","field_name":"sdc_endpoint","data_column":"sdc_endpoint","type_id":1,"order_index":3,"placeholder":"http://devesdc.sandbox.vms.frcs.org.fj:8888/<tenant>/api"},
    {"label":"SDC PIN URL","field_name":"sdc_pin_url","data_column":"sdc_pin_url","type_id":1,"order_index":4,"placeholder":"http://devesdc.sandbox.vms.frcs.org.fj:8888/<tenant>/api/v3/pin"}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;


-- Procurement form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  114,
  'Restaurant Product',
  'Manage Restaurant Products',
  'products',
  '[{"label":"Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
{"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":2},
{"label":"Quantity","field_name":"quantity","data_column":"quantity","type_id":1,"order_index":3},
{"label":"Price","field_name":"price","data_column":"price","type_id":1,"order_index":4},
{"label":"Barcode (GTIN)","field_name":"barcode","data_column":"barcode","type_id":1,"order_index":5},
{"label":"Product Number","field_name":"product_number","data_column":"product_number","type_id":1,"order_index":6},
{"label":"Brand","field_name":"brand","data_column":"brand","type_id":1,"order_index":7},
{"label":"Size","field_name":"size","data_column":"size","type_id":1,"order_index":8},
{"label":"Category","field_name":"category_id","data_column":"category_id","type_id":13,"options_table":"product_categories","options_label_column":"name","options_value_column":"category_id","order_index":9},
{"label":"VAT","field_name":"vat","json_column":"tax_rates","type_id":4,"order_index":11,"options":[{"display_text":"12.5%","value":"G"},{"display_text":"0%","value":"A"}]},
{"label":"STT","field_name":"stt","json_column":"tax_rates","type_id":4,"order_index":12,"options":[{"display_text":"Not Applicable","value":""},{"display_text":"6%","value":"E"}]},
{"label":"Ingredients","field_name":"ingredients","type_id":22,"columns":[{"name":"ingredient_id","label":"Ingredient","type":"select-search","options_table":"ingredients","options_label_column":"name","options_value_column":"id"},{"name":"quantity","label":"Quantity","type":"number"},{"name":"units","label":"Units","type":"select","options":[{"display_text":"Pieces","value":"pieces"},{"display_text":"Kg","value":"kg"},{"display_text":"Grams","value":"grams"},{"display_text":"Liters","value":"liters"},{"display_text":"Ml","value":"ml"}]}],"link_table":"ingredients_to_products","link_columns":{"product_id":"id","ingredient_id":"ingredient_id","quantity":"quantity","units":"unit"},"order_index":10}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Procurement form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  115,
  'Procurement',
  'Manage procurement and L.P.O records',
  'procurements',
  '[
    {"label":"L.P.O Vendor","field_name":"vendor_id","data_column":"vendor_id","type_id":4,"options_table":"Vendors","options_label_column":"vendorName","options_value_column":"id","order_index":1},
    {"label":"Quotation Number","field_name":"quotation_number","data_column":"quotation_number","type_id":1,"order_index":3},
    {"label":"Invoice Number","field_name":"invoice_number","data_column":"invoice_number","type_id":1,"order_index":4},
    {"label":"Requested By Date","field_name":"requested_by_date","data_column":"requested_by_date","type_id":3,"order_index":5},
    {"label":"Product Barcode","field_name":"product_barcode","data_column":"product_barcode","type_id":1,"order_index":2,"show_product_below":true}
  ]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;



-- Vendor Bank Accounts form
INSERT INTO forms (
  id, name, description, mapped_table, elements, tenant_id, branch_id
) VALUES (
  120,
  'Vendor Bank Account',
  'Manage vendor bank account details',
  'vendor_bank_accounts',
  '[
  {"label":"Vendor","field_name":"vendor_id","data_column":"vendor_id","type_id":4,"options_table":"Vendors","options_label_column":"vendorName","options_value_column":"vendor_id","order_index":1},
    {"label":"Account Name","field_name":"account_name","data_column":"account_name","type_id":1,"order_index":2},
    {"label":"Account Number","field_name":"account_number","data_column":"account_number","type_id":1,"order_index":3},
    {"label":"Bank Name","field_name":"bank_name","data_column":"bank_name","type_id":1,"order_index":4},
    {"label":"IFSC/SWIFT Code","field_name":"ifsc_swift","data_column":"ifsc_swift","type_id":1,"order_index":5},
    {"label":"Branch","field_name":"branch","data_column":"branch","type_id":1,"order_index":6},
    {"label":"Account Type","field_name":"account_type","data_column":"account_type","type_id":4,"order_index":7,"options":[{"display_text":"Savings","value":"savings"},{"display_text":"Current","value":"current"},{"display_text":"Other","value":"other"}]},
    {"label":"Active","field_name":"is_active","data_column":"is_active","type_id":5,"order_index":8}
  ]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Ingredients form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  116,
  'Ingredients',
  'Manage Ingredients',
  'ingredients',
  '[{"label":"Ingredient Name","field_name":"name","data_column":"name","type_id":1,"order_index":1},
    {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":2},
    {"label":"Units","field_name":"units","data_column":"units","type_id":4,"order_index":4,"options":[{"display_text":"Pieces","value":"pieces"},{"display_text":"Kg","value":"kg"},{"display_text":"Grams","value":"grams"},{"display_text":"Liters","value":"liters"},{"display_text":"Ml","value":"ml"}]},
    {"label":"Quantity","field_name":"quantity","data_column":"quantity","type_id":1,"order_index":3},
    {"label":"Price","field_name":"price","data_column":"price","type_id":1,"order_index":5}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;


  -- Purchase Order Form
  INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
  VALUES (
    130,
    'Purchase Order',
    'Form for creating and editing purchase orders',
    'purchase_orders',
    '[
      {"label":"Vendor","field_name":"vendor_id","data_column":"vendor_id","type_id":4,"options_table":"vendors","options_label_column":"vendorname","options_value_column":"vendor_id","order_index":1},
      {"label":"Order Date","field_name":"order_date","data_column":"order_date","type_id":3,"order_index":2},
      {"label":"Order Number","field_name":"order_number","data_column":"order_number","type_id":1,"order_index":3},
      {"label":"Products","field_name":"products","type_id":22,"columns":[
        {"name":"product_barcode","label":"Product","type":"select-search","options_table":"products","options_label_column":"name","options_value_column":"barcode","show_selected_name":true},
        {"name":"quantity","label":"Qty","type":"number"},
  {"name":"units","label":"Units","type":"select","options":[{"display_text":"Box","value":"box"},{"display_text":"Package","value":"package"},{"display_text":"Carton","value":"carton"}]}
      ],"order_index":6}
    ]',
    NULL,
    NULL
  )
  ON CONFLICT (id) DO NOTHING;

-- Product Discount Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  132,
  'Product Discount',
  'Manage product discounts with date ranges',
  'product_discounts',
  '[
    {"label":"Product","field_name":"product_name","type_id":1,"order_index":1,"readonly":true,"default":""},
    {"field_name":"product_id","data_column":"product_id","type_id":1,"order_index":0,"hidden":true,"required":true},
    {"label":"Discount Amount","field_name":"discount_amount","data_column":"discount_amount","type_id":2,"order_index":2,"required":true,"placeholder":"Enter discount amount"},
    {"label":"Start Date","field_name":"start_date","data_column":"start_date","type_id":3,"order_index":3,"required":true},
    {"label":"End Date","field_name":"end_date","data_column":"end_date","type_id":3,"order_index":4,"required":true}
  ]',
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;

-- Discount Rule Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  150,
  'Discount Rule',
  'Configure discount rules with conditional inputs',
  'discount_rules',
  '[
    {"label":"Discount Name","field_name":"name","data_column":"name","type_id":1,"order_index":1,"required":true},
    {"label":"Discount Type","field_name":"discount_type","data_column":"discount_type","type_id":4,"order_index":2,"required":true,"options":[{"value":"product_percent","label":"Percentage on Product"},{"value":"product_amount","label":"Amount on Product"},{"value":"category_percent","label":"Category (% Only)"},{"value":"store_percent","label":"Storewide (% Only)"},{"value":"bogo_product","label":"Buy X Get Y Free - Product"},{"value":"bogo_category","label":"Buy X Get Y Free - Category"},{"value":"bogo_store","label":"Buy X Get Y Free - Storewide"},{"value":"x_for_y","label":"X Products for $Y"},{"value":"percent_for_x_items","label":"% Off for X Items"}]},
    {"label":"Product","field_name":"product_id","data_column":"product_id","type_id":13,"options_table":"products","options_label_column":"name","options_value_column":"product_id","order_index":3,"condition_field":"discount_type","condition_value":"product_percent|product_amount|bogo_product|x_for_y|percent_for_x_items","required":true},
    {"label":"Category","field_name":"category_id","data_column":"category_id","type_id":13,"options_table":"product_categories","options_label_column":"name","options_value_column":"category_id","order_index":4,"condition_field":"discount_type","condition_value":"category_percent|bogo_category","required":true},
    {"label":"Discount Percentage","field_name":"percentage_value","data_column":"percentage_value","type_id":2,"order_index":5,"condition_field":"discount_type","condition_value":"product_percent|category_percent|store_percent|percent_for_x_items","required":true},
    {"label":"Discount Amount","field_name":"amount_value","data_column":"amount_value","type_id":2,"order_index":6,"condition_field":"discount_type","condition_value":"product_amount","required":true},
    {"label":"Buy Quantity","field_name":"buy_quantity","data_column":"buy_quantity","type_id":2,"order_index":7,"condition_field":"discount_type","condition_value":"bogo_product|bogo_category|bogo_store","required":true},
    {"label":"Free Quantity","field_name":"free_quantity","data_column":"free_quantity","type_id":2,"order_index":8,"condition_field":"discount_type","condition_value":"bogo_product|bogo_category|bogo_store","required":true},
    {"label":"Bundle Quantity","field_name":"bundle_quantity","data_column":"bundle_quantity","type_id":2,"order_index":9,"condition_field":"discount_type","condition_value":"x_for_y","required":true},
    {"label":"Bundle Price","field_name":"bundle_price","data_column":"bundle_price","type_id":2,"order_index":10,"condition_field":"discount_type","condition_value":"x_for_y","required":true},
    {"label":"Required Item Count","field_name":"threshold_quantity","data_column":"threshold_quantity","type_id":2,"order_index":11,"condition_field":"discount_type","condition_value":"percent_for_x_items","required":true},
    {"label":"Start Date","field_name":"start_date","data_column":"start_date","type_id":3,"order_index":12},
    {"label":"End Date","field_name":"end_date","data_column":"end_date","type_id":3,"order_index":13},
    {"label":"Notes","field_name":"notes","data_column":"notes","type_id":8,"order_index":14}
  ]',
  NULL,
  NULL
)
ON CONFLICT (id) DO NOTHING;

-- Employee Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  113,
  'Employee Form',
  'Edit employee details',
  'Employees',
  '[{"label":"Full Name","field_name":"full_name","data_column":"full_name","type_id":1,"order_index":1,"required":true},
    {"label":"Designation","field_name":"designation","data_column":"designation","type_id":1,"order_index":2},
    {"label":"Department","field_name":"department","data_column":"department","type_id":1,"order_index":3},
    {"label":"Join Date","field_name":"join_date","data_column":"join_date","type_id":3,"order_index":4},
    {"label":"Salary Type","field_name":"salary.salary_type","data_column":"salary.salary_type","type_id":4,"order_index":5,"options":[{"value":"hourly","label":"Hourly"},{"value":"monthly","label":"Monthly"},{"value":"contract","label":"Contract"}]},
    {"label":"Salary Rate","field_name":"salary.rate","data_column":"salary.rate","type_id":2,"order_index":6},
    {"label":"Currency","field_name":"salary.currency","data_column":"salary.currency","type_id":1,"order_index":7,"default":"FJD"}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Open Positions Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  117,
  'Open Position',
  'Form to create and edit open positions',
  'open_positions',
  '[{"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":0,"required":true},
    {"label":"Description","field_name":"description","data_column":"description","type_id":8,"order_index":1},
    {"label":"Branch","field_name":"branch_id","data_column":"branch_id","type_id":1,"order_index":2},
    {"label":"Position","field_name":"position","data_column":"position","type_id":1,"order_index":3},
    {"label":"Applicant Type","field_name":"applicant_type","data_column":"applicant_type","type_id":4,"order_index":4,"options":[{"value":"All","label":"All"},{"value":"Internal Only","label":"Internal Only"},{"value":"External Only","label":"External Only"}]},
    {"label":"Open Date","field_name":"open_date","data_column":"open_date","type_id":3,"order_index":5},
    {"label":"Close Date","field_name":"close_date","data_column":"close_date","type_id":3,"order_index":6},
    {"label":"Department","field_name":"department","data_column":"department","type_id":1,"order_index":7},
    {"label":"Qualifications","field_name":"qualifications","data_column":"qualifications","type_id":8,"order_index":8},
    {"label":"Salary","field_name":"salary","data_column":"salary","type_id":2,"order_index":9},
    {"label":"Status","field_name":"status","data_column":"status","type_id":4,"order_index":10,"options":[{"value":"Open","label":"Open"},{"value":"Closed","label":"Closed"},{"value":"Pending","label":"Pending"}]}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Application Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  118,
  'Job Application',
  'Form to submit job applications',
  'applications',
  '[{"label":"Position","field_name":"open_position_id","data_column":"open_position_id","type_id":4,"order_index":0,"required":true,"options_table":"open_positions","options_label_column":"title","options_value_column":"open_position_id"},
    {"label":"First Name","field_name":"first_name","data_column":"first_name","type_id":1,"order_index":1,"required":true},
    {"label":"Last Name","field_name":"last_name","data_column":"last_name","type_id":1,"order_index":2,"required":true},
    {"label":"Email","field_name":"email","data_column":"email","type_id":15,"order_index":3,"required":true},
    {"label":"Phone","field_name":"phone","data_column":"phone","type_id":1,"order_index":4},
    {"label":"Address","field_name":"address","data_column":"address","type_id":8,"order_index":5},
    {"label":"Date of Birth","field_name":"date_of_birth","data_column":"date_of_birth","type_id":3,"order_index":6},
    {"label":"Status","field_name":"status","data_column":"status","type_id":4,"order_index":7,"required":true,"options":[{"value":"New","label":"New"},{"value":"Under Review","label":"Under Review"},{"value":"ShortListed","label":"ShortListed"},{"value":"Interviewing","label":"Interviewing"},{"value":"Rejected","label":"Rejected"},{"value":"Hired","label":"Hired"}]},
    {"label":"Notes","field_name":"notes","data_column":"notes","type_id":8,"order_index":8}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Interview Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  119,
  'Interview',
  'Form to schedule and manage job interviews',
  'interviews',
  '[{"label":"Application","field_name":"application_id","data_column":"application_id","type_id":4,"order_index":0,"required":true,"options_table":"applications","options_label_column":"first_name","options_value_column":"application_id"},
    {"label":"Interview Date","field_name":"interview_date","data_column":"interview_date","type_id":3,"order_index":1,"required":true},
    {"label":"Interview Number","field_name":"interview_number","data_column":"interview_number","type_id":2,"order_index":2,"required":true},
    {"label":"Interviewers","field_name":"interviewer_id","data_column":"interviewer_id","type_id":14,"order_index":3,"options_table":"users","options_label_column":"first_name","options_value_column":"user_id"},
    {"label":"Status","field_name":"status","data_column":"status","type_id":4,"order_index":4,"options":[{"value":"Scheduled","label":"Scheduled"},{"value":"Completed","label":"Completed"},{"value":"Cancelled","label":"Cancelled"}]},
    {"label":"Notes","field_name":"notes","data_column":"notes","type_id":8,"order_index":5},
    {"label":"Feedback","field_name":"feedback","data_column":"feedback","type_id":8,"order_index":6},
    {"label":"Rating","field_name":"rating","data_column":"rating","type_id":2,"order_index":7}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- New Employee Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  140,
  'New Employee',
  'Form to create and manage new employee profiles',
  'users_profiles',
  '[{"label":"Title","field_name":"title","data_column":"title","type_id":1,"order_index":1},
    {"label":"Address","field_name":"address","data_column":"address","type_id":16,"order_index":2},
    {"label":"Phone Number","field_name":"phone_number","data_column":"phone_number","type_id":16,"order_index":3,"required":true},
    {"label":"Email","field_name":"email","data_column":"email","type_id":16,"order_index":4},
    {"label":"Date of Birth","field_name":"date_of_birth","data_column":"date_of_birth","type_id":3,"order_index":5},
    {"label":"Profile Picture","field_name":"profile_pic","data_column":"profile_pic","type_id":1,"order_index":6},
    {"label":"Notes","field_name":"notes","data_column":"notes","type_id":8,"order_index":7}]',
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;


-- RFQ Core Schema (idempotent)
-- Duplicated from migrations/20250926_rfq_core.sql for new database installs
DO $$ BEGIN
  CREATE EXTENSION IF NOT EXISTS pgcrypto;
EXCEPTION WHEN others THEN NULL; END $$;

CREATE TABLE IF NOT EXISTS rfq_headers (
  rfq_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id uuid NULL,
  department_id uuid NULL,
  type text NOT NULL CHECK (type IN ('RFQ','EOI')),
  rfq_number text NULL,
  numbering_format text NULL,
  title text NOT NULL,
  category text NULL,
  currency text NULL,
  incoterms text NULL,
  summary_markdown text NULL,
  summary_html text NULL,
  publish_at timestamptz NULL,
  due_at timestamptz NOT NULL,
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','open','paused','closed','awarded','archived')),
  created_by uuid NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updatedAt timestamptz NOT NULL DEFAULT now(),
  tender_box_opened_at timestamptz NULL,
  tender_box_opened_by uuid NULL,
  scoring_config jsonb NULL
);
CREATE INDEX IF NOT EXISTS idx_rfq_headers_status_due ON rfq_headers(status, due_at);
CREATE INDEX IF NOT EXISTS idx_rfq_headers_org_type ON rfq_headers(org_id, type);
CREATE UNIQUE INDEX IF NOT EXISTS uq_rfq_org_number ON rfq_headers(org_id, rfq_number) WHERE rfq_number IS NOT NULL;

CREATE TABLE IF NOT EXISTS rfq_sections (
  rfq_section_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  section_key text NULL,
  heading text NULL,
  body_markdown text NULL,
  body_html text NULL,
  sort_order int NOT NULL DEFAULT 0,
  include_in_print boolean NOT NULL DEFAULT true,
  include_in_email boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updatedAt timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_rfq_sections_rfq ON rfq_sections(rfq_id);


-- Add SDC endpoint URL if not present (for upgrades)
ALTER TABLE pos_settings
  ADD COLUMN IF NOT EXISTS sdc_endpoint text NULL;
-- Form support on sections
ALTER TABLE rfq_template_sections
  ADD COLUMN IF NOT EXISTS content_type text DEFAULT 'text',
  ADD COLUMN IF NOT EXISTS form_id int NULL,
  ADD COLUMN IF NOT EXISTS elements jsonb NULL;

ALTER TABLE rfq_sections
  ADD COLUMN IF NOT EXISTS content_type text DEFAULT 'text',
  ADD COLUMN IF NOT EXISTS form_id int NULL,
  ADD COLUMN IF NOT EXISTS elements jsonb NULL;

CREATE TABLE IF NOT EXISTS rfq_attachments (
  rfq_attachment_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  label text NULL,
  filetype text NULL,
  file_url text NULL,
  include_when text NOT NULL DEFAULT 'always',
  sort_order int NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updatedAt timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_rfq_attachments_rfq ON rfq_attachments(rfq_id);

CREATE TABLE IF NOT EXISTS rfq_items (
  id bigserial PRIMARY KEY,
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  description text NOT NULL,
  quantity numeric(14,4) NULL,
  uom text NULL,
  required_specs jsonb NULL,
  delivery_terms text NULL,
  delivery_location text NULL,
  is_optional boolean NOT NULL DEFAULT false,
  item_attachments jsonb NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updatedAt timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_rfq_items_rfq ON rfq_items(rfq_id);
CREATE INDEX IF NOT EXISTS idx_rfq_items_specs ON rfq_items USING GIN(required_specs);

CREATE TABLE IF NOT EXISTS rfq_invites (
  invite_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  vendor_id uuid NOT NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE,
  contact_id uuid NULL REFERENCES vendor_contacts(vendor_contact_id) ON DELETE SET NULL,
  vendor_user_id uuid NULL REFERENCES users(user_id) ON DELETE SET NULL,
  channel text NULL,
  status text NOT NULL DEFAULT 'invited' CHECK (status IN ('invited','delivered','opened','responded','declined')),
  sent_at timestamptz NULL,
  delivered_at timestamptz NULL,
  opened_at timestamptz NULL,
  responded_at timestamptz NULL,
  declined_reason text NULL,
  reminder_count int NOT NULL DEFAULT 0,
  last_reminder_at timestamptz NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updatedAt timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS uq_rfq_invite_unique ON rfq_invites(rfq_id, vendor_id);
CREATE INDEX IF NOT EXISTS idx_rfq_invites_status ON rfq_invites(rfq_id, status);

CREATE TABLE IF NOT EXISTS vendor_quotes (
  quote_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  invite_id uuid NULL REFERENCES rfq_invites(invite_id) ON DELETE SET NULL,
  vendor_id uuid NOT NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE,
  currency text NULL,
  total_amount numeric(14,2) NULL,
  tax_total numeric(14,2) NULL,
  delivery_days int NULL,
  valid_thru date NULL,
  notes text NULL,
  attachments jsonb NULL,
  revision_no int NOT NULL DEFAULT 1,
  submitted_at timestamptz NULL,
  status text NOT NULL DEFAULT 'draft' CHECK (status IN ('draft','submitted','revised','withdrawn','final')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updatedAt timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_vendor_quotes_rfq_vendor ON vendor_quotes(rfq_id, vendor_id);
CREATE INDEX IF NOT EXISTS idx_vendor_quotes_status ON vendor_quotes(rfq_id, status);

CREATE TABLE IF NOT EXISTS vendor_quote_items (
  quote_item_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  quote_id uuid NOT NULL REFERENCES vendor_quotes(quote_id) ON DELETE CASCADE,
  rfq_item_id bigint NULL REFERENCES rfq_items(id) ON DELETE SET NULL,
  is_alternate boolean NOT NULL DEFAULT false,
  alt_label text NULL,
  unit_price numeric(14,4) NULL,
  tax numeric(14,4) NULL,
  lead_time_days int NULL,
  notes text NULL
);
CREATE INDEX IF NOT EXISTS idx_vendor_quote_items_quote ON vendor_quote_items(quote_id);

CREATE TABLE IF NOT EXISTS rfq_awards (
  award_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  scope text NOT NULL CHECK (scope IN ('header','item')),
  rfq_item_id bigint NULL REFERENCES rfq_items(id) ON DELETE SET NULL,
  vendor_id uuid NOT NULL REFERENCES vendors(vendor_id) ON DELETE RESTRICT,
  notes text NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_rfq_awards_rfq ON rfq_awards(rfq_id);

CREATE TABLE IF NOT EXISTS rfq_shortlists (
  shortlist_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  vendor_id uuid NOT NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE,
  org_id uuid NULL REFERENCES orgs(org_id) ON DELETE SET NULL,
  notes text NULL,
  created_by uuid NULL REFERENCES users(user_id) ON DELETE SET NULL,
  updated_by uuid NULL REFERENCES users(user_id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updatedAt timestamptz NOT NULL DEFAULT now(),
  UNIQUE (rfq_id, vendor_id)
);
CREATE INDEX IF NOT EXISTS idx_rfq_shortlists_rfq ON rfq_shortlists(rfq_id);

CREATE TABLE IF NOT EXISTS rfq_events (
  event_id bigserial PRIMARY KEY,
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  actor_id uuid NULL,
  actor_type text NOT NULL DEFAULT 'internal',
  type text NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_rfq_events_rfq ON rfq_events(rfq_id, created_at);
CREATE INDEX IF NOT EXISTS idx_rfq_events_payload_gin ON rfq_events USING GIN(payload);

CREATE TABLE IF NOT EXISTS rfq_scoring_notes (
  note_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  vendor_id uuid NOT NULL REFERENCES vendors(vendor_id) ON DELETE CASCADE,
  org_id uuid NULL,
  criterion_key text NOT NULL,
  note_text text NULL,
  created_by uuid NULL REFERENCES users(user_id) ON DELETE SET NULL,
  updated_by uuid NULL REFERENCES users(user_id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updatedAt timestamptz NOT NULL DEFAULT now(),
  CONSTRAINT uq_rfq_scoring_notes_key UNIQUE (rfq_id, vendor_id, criterion_key)
);
CREATE INDEX IF NOT EXISTS idx_rfq_scoring_notes_rfq ON rfq_scoring_notes(rfq_id);


-- Approval fields on rfq_headers
ALTER TABLE rfq_headers
  ADD COLUMN IF NOT EXISTS requested_by uuid NULL,
  ADD COLUMN IF NOT EXISTS approval_status text NOT NULL DEFAULT 'draft',
  ADD COLUMN IF NOT EXISTS approval_started_at timestamptz NULL,
  ADD COLUMN IF NOT EXISTS approval_completed_at timestamptz NULL;

-- RFQ approval instances
CREATE TABLE IF NOT EXISTS rfq_approval_instances (
  approval_instance_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  rfq_id               uuid NOT NULL REFERENCES rfq_headers(rfq_id) ON DELETE CASCADE,
  stage_no             int NOT NULL DEFAULT 1,
  approver_user_id     uuid NULL,
  approver_group_id    uuid NULL,
  status               text NOT NULL DEFAULT 'pending',
  decided_at           timestamptz NULL,
  comments             text NULL,
  created_at           timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS rfq_approval_instances_rfq_idx ON rfq_approval_instances(rfq_id);

CREATE TABLE IF NOT EXISTS numbering_sequences (
  org_id uuid NOT NULL,
  type text NOT NULL,
  year int NOT NULL,
  next_seq int NOT NULL DEFAULT 1,
  CONSTRAINT numbering_sequences_unique UNIQUE (org_id, type, year)
);

-- Seed a dashboard page config with the RFQ Template Builder card centered.
-- Only inserts when no dashboard config exists yet.
INSERT INTO page_configs (page, header_title, cards, layout_type)
SELECT 'dashboard', 'Dashboard',
       '[{"text":"RFQ Template Builder","modal":"rfq_template_builder"}]',
       'grid'
WHERE NOT EXISTS (SELECT 1 FROM page_configs WHERE page = 'dashboard' AND isDeleted = false);

-- Reserve Bank of Fiji RFQ Templates Seed (idempotent)
DO $$
DECLARE
  v_tpl uuid;
BEGIN
  ---------------------------------------------------------------------------
  -- General Goods & Services (Default)
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'General Goods & Services (RBF Default)', 'RBF_GS_DEFAULT', 'general',
          'Default RFQ template for general goods and services', true,
          ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  -- Sections
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'background') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'background', 'Background', 'Provide organizational context and objectives of the RFQ.', 10, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'scope') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'scope', 'Scope of Work', 'Describe the scope, locations, and expected outcomes.', 20, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'technical_requirements') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'technical_requirements', 'Technical Requirements', 'List minimum specifications, standards, and compliance requirements.', 30, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'deliverables') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'deliverables', 'Deliverables', 'Define deliverables, documentation, and acceptance criteria.', 40, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'service_levels') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'service_levels', 'Service Levels (SLA)', 'Specify response/restore times, uptime targets, and penalties.', 50, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'pricing') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'pricing', 'Pricing & Validity', 'Provide pricing, currency (FJD), taxes, and validity (days).', 60, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'submission') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'submission', 'Submission Instructions', 'Describe submission method, deadline, and required forms.', 70, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'evaluation') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'evaluation', 'Evaluation Criteria', 'Outline evaluation method and weighting.', 80, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'terms') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'terms', 'Commercial Terms', 'Payment terms, delivery terms (Incoterms if applicable), warranty.', 90, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'confidentiality') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'confidentiality', 'Confidentiality & Ethics', 'Confidentiality, anti-bribery, and code of conduct expectations.', 100, true, true);
  END IF;

  -- Attachments
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Standard RFQ Terms & Conditions (RBF)') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Standard RFQ Terms & Conditions (RBF)', 'pdf', 'templates/rbf/terms_conditions.pdf', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Non-Disclosure Agreement') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Non-Disclosure Agreement', 'pdf', 'templates/rbf/nda.pdf', 'pre_issuance', 20);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Pricing Template') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Pricing Template', 'xlsx', 'templates/rbf/pricing_template.xlsx', 'always', 30);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Code of Conduct & Anti-Bribery') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Code of Conduct & Anti-Bribery', 'pdf', 'templates/rbf/code_of_conduct_anti_bribery.pdf', 'always', 40);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Supplier Registration Form') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Supplier Registration Form', 'docx', 'templates/rbf/supplier_registration_form.docx', 'pre_issuance', 50);
  END IF;

  ---------------------------------------------------------------------------
  -- IT Infrastructure & Hardware
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'IT Infrastructure & Hardware', 'RBF_IT_INFRA', 'it',
          'Servers, network, endpoints, and related hardware', false,
          ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'background';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Environment overview and objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope of Work', 'Bill of materials, delivery, and installation scope.', 20, true, true),
      (v_tpl, 'technical_requirements', 'Technical Requirements', 'CPU, RAM, storage, certifications, redundancy, power & cooling.', 30, true, true),
      (v_tpl, 'warranty_support', 'Warranty & Support', 'Warranty terms, response and restore targets.', 40, true, true),
      (v_tpl, 'delivery_installation', 'Delivery & Installation', 'Site survey, logistics, racking, labelling.', 50, true, true),
      (v_tpl, 'acceptance_testing', 'Acceptance & Testing', 'UAT/POC criteria, burn-in, documentation.', 60, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Provide unit costs, BoM, and any optional items.', 70, true, true),
      (v_tpl, 'evaluation', 'Evaluation Criteria', 'Method and weighting.', 80, true, true),
      (v_tpl, 'terms', 'Commercial Terms', 'Payment, delivery schedule, liquidated damages.', 90, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'BoM Template') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'BoM Template', 'xlsx', 'templates/rbf/it/bom_template.xlsx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Rack & Power Standards') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Rack & Power Standards', 'pdf', 'templates/rbf/it/rack_power_standards.pdf', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Software & SaaS Procurement
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Software & SaaS', 'RBF_SOFTWARE_SAAS', 'it',
          'Commercial software and SaaS subscriptions', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'functional_requirements';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Business need and stakeholders.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Modules and user counts.', 20, true, true),
      (v_tpl, 'functional_requirements', 'Functional Requirements', 'Feature checklist and must-haves.', 30, true, true),
      (v_tpl, 'nonfunctional_security', 'Security & Compliance', 'Authentication, encryption, audit, compliance (ISO 27001, PCI if applicable).', 40, true, true),
      (v_tpl, 'data_residency_privacy', 'Data Residency & Privacy', 'Storage location, retention, DPA requirements.', 50, true, true),
      (v_tpl, 'integration_requirements', 'Integration', 'APIs, formats, and systems to integrate.', 60, true, true),
      (v_tpl, 'support_sla', 'Support & SLA', 'Support hours, response/restore commitments.', 70, true, true),
      (v_tpl, 'bcdr', 'BCDR', 'Backups, DR strategy and RTO/RPO.', 80, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Licenses, tiers, seats, implementation costs.', 90, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 100, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial terms and SLAs.', 110, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Security Questionnaire') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Security Questionnaire', 'xlsx', 'templates/rbf/it/security_questionnaire.xlsx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Data Processing Agreement') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Data Processing Agreement', 'pdf', 'templates/rbf/legal/dpa.pdf', 'awarded', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Cybersecurity Services
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Cybersecurity Services', 'RBF_CYBERSEC', 'security',
          'Assessments, GRC, penetration testing, or MSSP', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'methodology_standards';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Security objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'VA/PT/GRC/MDR scope and scale.', 20, true, true),
      (v_tpl, 'methodology_standards', 'Methodology & Standards', 'Follow OWASP, NIST, ISO 27001/2; provide sample reports.', 30, true, true),
      (v_tpl, 'deliverables', 'Deliverables', 'Reports, findings, remediation, workshops.', 40, true, true),
      (v_tpl, 'service_levels', 'Service Levels', 'Engagement timelines and SLAs.', 50, true, true),
      (v_tpl, 'team_quals_clearance', 'Team Qualifications', 'Certifications and clearances.', 60, true, true),
      (v_tpl, 'reporting', 'Reporting', 'Format, cadence, and review.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Fixed fee or T&M, travel policy.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'confidentiality', 'Confidentiality', 'Handling of sensitive information.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Sample Report Outline') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Sample Report Outline', 'pdf', 'templates/rbf/security/sample_report_outline.pdf', 'always', 10);
  END IF;

  ---------------------------------------------------------------------------
  -- Financial/Regulatory Consulting & Research
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Financial/Regulatory Consulting & Research', 'RBF_CONSULTING', 'consulting',
          'Policy, market studies, regulatory impact analyses', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'methodology';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background_objectives', 'Background & Objectives', 'Context, research goals and outcomes.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Tasks, datasets, and stakeholders.', 20, true, true),
      (v_tpl, 'methodology', 'Methodology', 'Approach, rigor, and quality control.', 30, true, true),
      (v_tpl, 'team_experience', 'Team & Experience', 'Experience in financial sector and references.', 40, true, true),
      (v_tpl, 'deliverables_milestones', 'Deliverables & Milestones', 'Reports, timelines, presentations.', 50, true, true),
      (v_tpl, 'governance_reporting', 'Governance & Reporting', 'Review cadence, steering oversight.', 60, true, true),
      (v_tpl, 'ip_rights', 'Intellectual Property', 'Ownership and licensing terms.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'T&M rates or fixed pricing.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Template Workplan') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Template Workplan', 'docx', 'templates/rbf/consulting/workplan_template.docx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'CV Matrix') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'CV Matrix', 'xlsx', 'templates/rbf/consulting/cv_matrix.xlsx', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- External Audit Services
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'External Audit Services', 'RBF_AUDIT', 'audit',
          'Financial, IT, or compliance audits', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'standards';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Audit objectives and scope.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Financial/IT/compliance scope.', 20, true, true),
      (v_tpl, 'standards', 'Standards', 'ISA/ISAE/ISACA or relevant standards.', 30, true, true),
      (v_tpl, 'independence_conflict', 'Independence & Conflict', 'Independence declaration requirements.', 40, true, true),
      (v_tpl, 'team_quals', 'Team Qualifications', 'Qualifications and experience.', 50, true, true),
      (v_tpl, 'deliverables', 'Deliverables', 'Reports and management letters.', 60, true, true),
      (v_tpl, 'timeline', 'Timeline', 'Milestones and schedule.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Fees and assumptions.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial and confidentiality terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Independence Declaration') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Independence Declaration', 'docx', 'templates/rbf/audit/independence_declaration.docx', 'pre_issuance', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Sample Management Letter') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Sample Management Letter', 'pdf', 'templates/rbf/audit/sample_management_letter.pdf', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Banking Equipment & Maintenance
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Banking Equipment & Maintenance', 'RBF_BANK_EQUIP', 'operations',
          'Cash processing equipment, CCTV, safes, and maintenance', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'preventive_maintenance';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Operational context and objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Supply, installation, and maintenance.', 20, true, true),
      (v_tpl, 'technical_requirements', 'Technical Requirements', 'Models, capacity, certifications.', 30, true, true),
      (v_tpl, 'installation_site_prep', 'Installation & Site Prep', 'Power, network, physical layout.', 40, true, true),
      (v_tpl, 'preventive_maintenance', 'Preventive Maintenance', 'PPM schedules, spares, and SLAs.', 50, true, true),
      (v_tpl, 'spares_warranty', 'Spares & Warranty', 'Spare parts and warranty handling.', 60, true, true),
      (v_tpl, 'training', 'Training', 'Operator and maintenance training.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Supply, installation, AMC.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial and confidentiality terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Site Survey Checklist') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Site Survey Checklist', 'xlsx', 'templates/rbf/operations/site_survey_checklist.xlsx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Handover Form') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Handover Form', 'docx', 'templates/rbf/operations/handover_form.docx', 'awarded', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Cash‑In‑Transit & Secure Logistics (CIT)
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Cash-In-Transit & Secure Logistics', 'RBF_CIT', 'security',
          'CIT services and secure logistics', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'security_compliance';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Cash movement profile and objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Routes, volumes, time windows.', 20, true, true),
      (v_tpl, 'security_compliance', 'Security & Compliance', 'Personnel vetting, SOPs, certifications.', 30, true, true),
      (v_tpl, 'risk_management', 'Risk Management', 'Threat model, contingencies.', 40, true, true),
      (v_tpl, 'incident_reporting', 'Incident Reporting', 'KPIs and escalation.', 50, true, true),
      (v_tpl, 'insurance_coverage', 'Insurance Coverage', 'Required coverage levels.', 60, true, true),
      (v_tpl, 'service_levels', 'Service Levels', 'OTP and tamper KPIs.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Route pricing and surcharges.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms_confidentiality', 'Terms & Confidentiality', 'Commercial and confidentiality terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Security Vetting Form') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Security Vetting Form', 'docx', 'templates/rbf/security/security_vetting_form.docx', 'pre_issuance', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Incident Template') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Incident Template', 'docx', 'templates/rbf/security/incident_template.docx', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Two-Envelope Technical & Financial Proposals
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Two-Envelope Technical & Financial', 'RBF_TECH_FIN_ENVELOPE', 'general',
          'For procurements that require separate technical and financial proposals', false,
          ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'submission_overview';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email, is_required)
    VALUES
      (v_tpl, 'submission_overview', 'Submission Overview',
        'Vendors must submit two separate digital files: **one technical proposal** and **one financial proposal**. Label files clearly (e.g. `VendorName - Technical.pdf` and `VendorName - Financial.xlsx`) and upload them as attachments when responding to this RFQ. Pricing information must be restricted to the financial proposal. Any proposal that combines technical and commercial content may be disqualified.',
        10, true, true, true);
  END IF;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'technical_proposal';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email, is_required)
    VALUES
      (v_tpl, 'technical_proposal', 'Technical Proposal Requirements',
        'Describe the solution, methodology, implementation approach, workplan, team CVs and compliance statements. The technical proposal must **exclude** all pricing or commercial terms. Include evidence of experience, certifications, quality assurance processes, risk management approach, and any mandatory forms (upload as appendices).',
        20, true, true, true);
  END IF;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'financial_proposal';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email, is_required)
    VALUES
      (v_tpl, 'financial_proposal', 'Financial Proposal Requirements',
        'Provide a detailed cost breakdown aligned to the scope. Show unit costs, taxes, optional items and total contract value. State currency (default FJD), payment schedule, validity period, and any assumptions. Do not repeat technical narrative in this document. Clearly reference the corresponding technical proposal sections.',
        30, true, true, true);
  END IF;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'evaluation_method';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'evaluation_method', 'Evaluation & Weighting',
        'The default weighting for this template is **70% technical** and **30% financial**. Evaluation teams should use the compare tab to score the technical proposal against published criteria (such as methodology, team, delivery plan, risk mitigation). Financial proposals are assessed on total cost of ownership and commercial compliance. Document consensus scores in the scoring notes area.',
        40, true, true);
  END IF;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'timeline_clarifications';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'timeline_clarifications', 'Timeline & Clarifications',
        'State the closing date/time, contact point for questions, and process for clarification responses. Remind vendors that late submissions or missing files can result in rejection. Where applicable, require acknowledgement that both documents were submitted on time.',
        50, true, true);
  END IF;

  ---------------------------------------------------------------------------
  -- Facilities & Building Services
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Facilities & Building Services', 'RBF_FACILITIES', 'facilities',
          'HVAC, power, cleaning, and soft services', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'standards_safety';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Facility profile and objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'HVAC, power, cleaning scope.', 20, true, true),
      (v_tpl, 'standards_safety', 'Standards & Safety', 'Applicable standards and safety protocols.', 30, true, true),
      (v_tpl, 'schedules_sla', 'Schedules & SLA', 'PPM schedules and SLAs.', 40, true, true),
      (v_tpl, 'staffing_quals', 'Staffing & Qualifications', 'Minimum staffing and skills.', 50, true, true),
      (v_tpl, 'reporting', 'Reporting', 'Logs, KPIs, and reports.', 60, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Service pricing and options.', 70, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 80, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial and confidentiality terms.', 90, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'HSE Requirements') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'HSE Requirements', 'pdf', 'templates/rbf/facilities/hse_requirements.pdf', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'PPM Schedule Template') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'PPM Schedule Template', 'xlsx', 'templates/rbf/facilities/ppm_schedule_template.xlsx', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Training & Capacity Building
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Training & Capacity Building', 'RBF_TRAINING', 'training',
          'Training programs and capability development', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'learning_objectives';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Audience and training objectives.', 10, true, true),
      (v_tpl, 'learning_objectives', 'Learning Objectives', 'Expected outcomes and competencies.', 20, true, true),
      (v_tpl, 'curriculum_outline', 'Curriculum Outline', 'Modules and durations.', 30, true, true),
      (v_tpl, 'trainer_quals', 'Trainer Qualifications', 'Required experience and certifications.', 40, true, true),
      (v_tpl, 'delivery_method', 'Delivery Method', 'Onsite/online/hybrid details.', 50, true, true),
      (v_tpl, 'materials_ip', 'Materials & IP', 'Ownership of materials and usage rights.', 60, true, true),
      (v_tpl, 'assessment_evaluation', 'Assessment & Evaluation', 'Assessments and feedback instruments.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Per-learner or program pricing.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial and confidentiality terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Attendance Sheet') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Attendance Sheet', 'xlsx', 'templates/rbf/training/attendance_sheet.xlsx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Course Evaluation Form') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Course Evaluation Form', 'docx', 'templates/rbf/training/course_evaluation_form.docx', 'awarded', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- EOI: Market Sounding
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'EOI: Market Sounding', 'RBF_EOI_MARKET', 'eoi',
          'Expression of Interest for market sounding', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'interest_statement';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Purpose of EOI and context.', 10, true, true),
      (v_tpl, 'interest_statement', 'Statement of Interest', 'Vendor expression of interest and relevance.', 20, true, true),
      (v_tpl, 'capability_summary', 'Capability Summary', 'Core capabilities and capacity.', 30, true, true),
      (v_tpl, 'relevant_experience', 'Relevant Experience', 'Case studies and references.', 40, true, true),
      (v_tpl, 'indicative_approach', 'Indicative Approach', 'High-level approach and timeline.', 50, true, true),
      (v_tpl, 'submission', 'Submission', 'Instructions and deadline.', 60, true, true),
      (v_tpl, 'shortlisting_criteria', 'Shortlisting Criteria', 'How EOIs will be shortlisted.', 70, true, true);
  END IF;

END $$;

-- ---------------------------------------------------------------------------
-- Vendor Onboarding Wizard (seed)
-- Idempotent: will not duplicate if already present
-- ---------------------------------------------------------------------------
DO $$
DECLARE v_wizard_id INTEGER;
BEGIN
  -- Try to insert the wizard; if sequence is out-of-sync insertion may conflict, so use ON CONFLICT DO NOTHING
  -- Ensure we have a wizard id: if exists, use it; otherwise reserve an unused id and insert with that id
  SELECT id INTO v_wizard_id FROM form_wizards WHERE name = 'Vendor Onboarding Wizard' LIMIT 1;
  IF v_wizard_id IS NULL THEN
    -- compute a free id (max(id)+1) to avoid primary key conflicts when sequences differ
    SELECT COALESCE(MAX(id), 0) + 1 INTO v_wizard_id FROM form_wizards;
  INSERT INTO form_wizards (id, name, description)
  VALUES (v_wizard_id, 'Vendor Onboarding Wizard', 'Public vendor onboarding wizard sequencing vendor, contact, address and documents');
    -- advance sequence if present
    BEGIN
      PERFORM setval('form_wizards_id_seq', (SELECT MAX(id) FROM form_wizards));
    EXCEPTION WHEN undefined_function THEN
      -- sequence may not exist in some environments; ignore
      NULL;
    END;
  END IF;

  -- Ensure legacy capability step is removed if present
  DELETE FROM form_wizard_steps WHERE wizard_id = v_wizard_id AND form_id = 612;

  -- Idempotently insert each step (guard by existence)
  IF NOT EXISTS (SELECT 1 FROM form_wizard_steps WHERE wizard_id = v_wizard_id AND form_id = 110) THEN
    INSERT INTO form_wizard_steps (wizard_id, step_number, form_id) VALUES (v_wizard_id, 1, 110);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM form_wizard_steps WHERE wizard_id = v_wizard_id AND form_id = 610) THEN
    INSERT INTO form_wizard_steps (wizard_id, step_number, form_id) VALUES (v_wizard_id, 2, 610);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM form_wizard_steps WHERE wizard_id = v_wizard_id AND form_id = 611) THEN
    INSERT INTO form_wizard_steps (wizard_id, step_number, form_id) VALUES (v_wizard_id, 3, 611);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM form_wizard_steps WHERE wizard_id = v_wizard_id AND form_id = 613) THEN
    INSERT INTO form_wizard_steps (wizard_id, step_number, form_id) VALUES (v_wizard_id, 4, 613);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM form_wizard_steps WHERE wizard_id = v_wizard_id AND form_id = 614) THEN
    INSERT INTO form_wizard_steps (wizard_id, step_number, form_id) VALUES (v_wizard_id, 5, 614);
  END IF;
END $$;

-- Reserve Bank of Fiji RFQ Templates Seed
-- Idempotent upsert of templates, sections and attachments
-- Applies to PostgreSQL. Safe to re-run.

DO $$
DECLARE
  v_tpl uuid;
BEGIN
  ---------------------------------------------------------------------------
  -- General Goods & Services (Default)
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'General Goods & Services (RBF Default)', 'RBF_GS_DEFAULT', 'general',
          'Default RFQ template for general goods and services', true,
          ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  -- Sections
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'background') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'background', 'Background', 'Provide organizational context and objectives of the RFQ.', 10, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'scope') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'scope', 'Scope of Work', 'Describe the scope, locations, and expected outcomes.', 20, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'technical_requirements') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'technical_requirements', 'Technical Requirements', 'List minimum specifications, standards, and compliance requirements.', 30, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'deliverables') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'deliverables', 'Deliverables', 'Define deliverables, documentation, and acceptance criteria.', 40, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'service_levels') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'service_levels', 'Service Levels (SLA)', 'Specify response/restore times, uptime targets, and penalties.', 50, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'pricing') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'pricing', 'Pricing & Validity', 'Provide pricing, currency (FJD), taxes, and validity (days).', 60, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'submission') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'submission', 'Submission Instructions', 'Describe submission method, deadline, and required forms.', 70, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'evaluation') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'evaluation', 'Evaluation Criteria', 'Outline evaluation method and weighting.', 80, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'terms') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'terms', 'Commercial Terms', 'Payment terms, delivery terms (Incoterms if applicable), warranty.', 90, true, true);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'confidentiality') THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES (v_tpl, 'confidentiality', 'Confidentiality & Ethics', 'Confidentiality, anti-bribery, and code of conduct expectations.', 100, true, true);
  END IF;

  -- Attachments
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Standard RFQ Terms & Conditions (RBF)') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Standard RFQ Terms & Conditions (RBF)', 'pdf', 'templates/rbf/terms_conditions.pdf', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Non-Disclosure Agreement') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Non-Disclosure Agreement', 'pdf', 'templates/rbf/nda.pdf', 'pre_issuance', 20);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Pricing Template') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Pricing Template', 'xlsx', 'templates/rbf/pricing_template.xlsx', 'always', 30);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Code of Conduct & Anti-Bribery') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Code of Conduct & Anti-Bribery', 'pdf', 'templates/rbf/code_of_conduct_anti_bribery.pdf', 'always', 40);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Supplier Registration Form') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Supplier Registration Form', 'docx', 'templates/rbf/supplier_registration_form.docx', 'pre_issuance', 50);
  END IF;

  ---------------------------------------------------------------------------
  -- IT Infrastructure & Hardware
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'IT Infrastructure & Hardware', 'RBF_IT_INFRA', 'it',
          'Servers, network, endpoints, and related hardware', false,
          ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'background';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Environment overview and objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope of Work', 'Bill of materials, delivery, and installation scope.', 20, true, true),
      (v_tpl, 'technical_requirements', 'Technical Requirements', 'CPU, RAM, storage, certifications, redundancy, power & cooling.', 30, true, true),
      (v_tpl, 'warranty_support', 'Warranty & Support', 'Warranty terms, response and restore targets.', 40, true, true),
      (v_tpl, 'delivery_installation', 'Delivery & Installation', 'Site survey, logistics, racking, labelling.', 50, true, true),
      (v_tpl, 'acceptance_testing', 'Acceptance & Testing', 'UAT/POC criteria, burn-in, documentation.', 60, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Provide unit costs, BoM, and any optional items.', 70, true, true),
      (v_tpl, 'evaluation', 'Evaluation Criteria', 'Method and weighting.', 80, true, true),
      (v_tpl, 'terms', 'Commercial Terms', 'Payment, delivery schedule, liquidated damages.', 90, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'BoM Template') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'BoM Template', 'xlsx', 'templates/rbf/it/bom_template.xlsx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Rack & Power Standards') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Rack & Power Standards', 'pdf', 'templates/rbf/it/rack_power_standards.pdf', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Software & SaaS Procurement
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Software & SaaS', 'RBF_SOFTWARE_SAAS', 'it',
          'Commercial software and SaaS subscriptions', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'functional_requirements';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Business need and stakeholders.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Modules and user counts.', 20, true, true),
      (v_tpl, 'functional_requirements', 'Functional Requirements', 'Feature checklist and must-haves.', 30, true, true),
      (v_tpl, 'nonfunctional_security', 'Security & Compliance', 'Authentication, encryption, audit, compliance (ISO 27001, PCI if applicable).', 40, true, true),
      (v_tpl, 'data_residency_privacy', 'Data Residency & Privacy', 'Storage location, retention, DPA requirements.', 50, true, true),
      (v_tpl, 'integration_requirements', 'Integration', 'APIs, formats, and systems to integrate.', 60, true, true),
      (v_tpl, 'support_sla', 'Support & SLA', 'Support hours, response/restore commitments.', 70, true, true),
      (v_tpl, 'bcdr', 'BCDR', 'Backups, DR strategy and RTO/RPO.', 80, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Licenses, tiers, seats, implementation costs.', 90, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 100, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial terms and SLAs.', 110, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Security Questionnaire') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Security Questionnaire', 'xlsx', 'templates/rbf/it/security_questionnaire.xlsx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Data Processing Agreement') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Data Processing Agreement', 'pdf', 'templates/rbf/legal/dpa.pdf', 'awarded', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Cybersecurity Services
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Cybersecurity Services', 'RBF_CYBERSEC', 'security',
          'Assessments, GRC, penetration testing, or MSSP', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'methodology_standards';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Security objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'VA/PT/GRC/MDR scope and scale.', 20, true, true),
      (v_tpl, 'methodology_standards', 'Methodology & Standards', 'Follow OWASP, NIST, ISO 27001/2; provide sample reports.', 30, true, true),
      (v_tpl, 'deliverables', 'Deliverables', 'Reports, findings, remediation, workshops.', 40, true, true),
      (v_tpl, 'service_levels', 'Service Levels', 'Engagement timelines and SLAs.', 50, true, true),
      (v_tpl, 'team_quals_clearance', 'Team Qualifications', 'Certifications and clearances.', 60, true, true),
      (v_tpl, 'reporting', 'Reporting', 'Format, cadence, and review.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Fixed fee or T&M, travel policy.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'confidentiality', 'Confidentiality', 'Handling of sensitive information.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Sample Report Outline') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Sample Report Outline', 'pdf', 'templates/rbf/security/sample_report_outline.pdf', 'always', 10);
  END IF;

  ---------------------------------------------------------------------------
  -- Financial/Regulatory Consulting & Research
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Financial/Regulatory Consulting & Research', 'RBF_CONSULTING', 'consulting',
          'Policy, market studies, regulatory impact analyses', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'methodology';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background_objectives', 'Background & Objectives', 'Context, research goals and outcomes.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Tasks, datasets, and stakeholders.', 20, true, true),
      (v_tpl, 'methodology', 'Methodology', 'Approach, rigor, and quality control.', 30, true, true),
      (v_tpl, 'team_experience', 'Team & Experience', 'Experience in financial sector and references.', 40, true, true),
      (v_tpl, 'deliverables_milestones', 'Deliverables & Milestones', 'Reports, timelines, presentations.', 50, true, true),
      (v_tpl, 'governance_reporting', 'Governance & Reporting', 'Review cadence, steering oversight.', 60, true, true),
      (v_tpl, 'ip_rights', 'Intellectual Property', 'Ownership and licensing terms.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'T&M rates or fixed pricing.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Template Workplan') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Template Workplan', 'docx', 'templates/rbf/consulting/workplan_template.docx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'CV Matrix') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'CV Matrix', 'xlsx', 'templates/rbf/consulting/cv_matrix.xlsx', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- External Audit Services
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'External Audit Services', 'RBF_AUDIT', 'audit',
          'Financial, IT, or compliance audits', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'standards';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Audit objectives and scope.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Financial/IT/compliance scope.', 20, true, true),
      (v_tpl, 'standards', 'Standards', 'ISA/ISAE/ISACA or relevant standards.', 30, true, true),
      (v_tpl, 'independence_conflict', 'Independence & Conflict', 'Independence declaration requirements.', 40, true, true),
      (v_tpl, 'team_quals', 'Team Qualifications', 'Qualifications and experience.', 50, true, true),
      (v_tpl, 'deliverables', 'Deliverables', 'Reports and management letters.', 60, true, true),
      (v_tpl, 'timeline', 'Timeline', 'Milestones and schedule.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Fees and assumptions.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial and confidentiality terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Independence Declaration') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Independence Declaration', 'docx', 'templates/rbf/audit/independence_declaration.docx', 'pre_issuance', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Sample Management Letter') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Sample Management Letter', 'pdf', 'templates/rbf/audit/sample_management_letter.pdf', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Banking Equipment & Maintenance
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Banking Equipment & Maintenance', 'RBF_BANK_EQUIP', 'operations',
          'Cash processing equipment, CCTV, safes, and maintenance', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'preventive_maintenance';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Operational context and objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Supply, installation, and maintenance.', 20, true, true),
      (v_tpl, 'technical_requirements', 'Technical Requirements', 'Models, capacity, certifications.', 30, true, true),
      (v_tpl, 'installation_site_prep', 'Installation & Site Prep', 'Power, network, physical layout.', 40, true, true),
      (v_tpl, 'preventive_maintenance', 'Preventive Maintenance', 'PPM schedules, spares, and SLAs.', 50, true, true),
      (v_tpl, 'spares_warranty', 'Spares & Warranty', 'Spare parts and warranty handling.', 60, true, true),
      (v_tpl, 'training', 'Training', 'Operator and maintenance training.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Supply, installation, AMC.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial and confidentiality terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Site Survey Checklist') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Site Survey Checklist', 'xlsx', 'templates/rbf/operations/site_survey_checklist.xlsx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Handover Form') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Handover Form', 'docx', 'templates/rbf/operations/handover_form.docx', 'awarded', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Cash-In-Transit & Secure Logistics (CIT)
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Cash-In-Transit & Secure Logistics', 'RBF_CIT', 'security',
          'CIT services and secure logistics', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'security_compliance';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Cash movement profile and objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'Routes, volumes, time windows.', 20, true, true),
      (v_tpl, 'security_compliance', 'Security & Compliance', 'Personnel vetting, SOPs, certifications.', 30, true, true),
      (v_tpl, 'risk_management', 'Risk Management', 'Threat model, contingencies.', 40, true, true),
      (v_tpl, 'incident_reporting', 'Incident Reporting', 'KPIs and escalation.', 50, true, true),
      (v_tpl, 'insurance_coverage', 'Insurance Coverage', 'Required coverage levels.', 60, true, true),
      (v_tpl, 'service_levels', 'Service Levels', 'OTP and tamper KPIs.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Route pricing and surcharges.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms_confidentiality', 'Terms & Confidentiality', 'Commercial and confidentiality terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Security Vetting Form') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Security Vetting Form', 'docx', 'templates/rbf/security/security_vetting_form.docx', 'pre_issuance', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Incident Template') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Incident Template', 'docx', 'templates/rbf/security/incident_template.docx', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Two-Envelope Technical & Financial Proposals
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Two-Envelope Technical & Financial', 'RBF_TECH_FIN_ENVELOPE', 'general',
          'For procurements that require separate technical and financial proposals', false,
          ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'submission_overview';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email, is_required)
    VALUES
      (v_tpl, 'submission_overview', 'Submission Overview',
        'Vendors must submit two separate digital files: **one technical proposal** and **one financial proposal**. Label files clearly (e.g. `VendorName - Technical.pdf` and `VendorName - Financial.xlsx`) and upload them as attachments when responding to this RFQ. Pricing information must be restricted to the financial proposal. Any proposal that combines technical and commercial content may be disqualified.',
        10, true, true, true);
  END IF;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'technical_proposal';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email, is_required)
    VALUES
      (v_tpl, 'technical_proposal', 'Technical Proposal Requirements',
        'Describe the solution, methodology, implementation approach, workplan, team CVs and compliance statements. The technical proposal must **exclude** all pricing or commercial terms. Include evidence of experience, certifications, quality assurance processes, risk management approach, and any mandatory forms (upload as appendices).',
        20, true, true, true);
  END IF;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'financial_proposal';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email, is_required)
    VALUES
      (v_tpl, 'financial_proposal', 'Financial Proposal Requirements',
        'Provide a detailed cost breakdown aligned to the scope. Show unit costs, taxes, optional items and total contract value. State currency (default FJD), payment schedule, validity period, and any assumptions. Do not repeat technical narrative in this document. Clearly reference the corresponding technical proposal sections.',
        30, true, true, true);
  END IF;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'evaluation_method';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'evaluation_method', 'Evaluation & Weighting',
        'The default weighting for this template is **70% technical** and **30% financial**. Evaluation teams should use the compare tab to score the technical proposal against published criteria (such as methodology, team, delivery plan, risk mitigation). Financial proposals are assessed on total cost of ownership and commercial compliance. Document consensus scores in the scoring notes area.',
        40, true, true);
  END IF;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'timeline_clarifications';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'timeline_clarifications', 'Timeline & Clarifications',
        'State the closing date/time, contact point for questions, and process for clarification responses. Remind vendors that late submissions or missing files can result in rejection. Where applicable, require acknowledgement that both documents were submitted on time.',
        50, true, true);
  END IF;

  ---------------------------------------------------------------------------
  -- Facilities & Building Services
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Facilities & Building Services', 'RBF_FACILITIES', 'facilities',
          'HVAC, power, cleaning, and soft services', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'standards_safety';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Facility profile and objectives.', 10, true, true),
      (v_tpl, 'scope', 'Scope', 'HVAC, power, cleaning scope.', 20, true, true),
      (v_tpl, 'standards_safety', 'Standards & Safety', 'Applicable standards and safety protocols.', 30, true, true),
      (v_tpl, 'schedules_sla', 'Schedules & SLA', 'PPM schedules and SLAs.', 40, true, true),
      (v_tpl, 'staffing_quals', 'Staffing & Qualifications', 'Minimum staffing and skills.', 50, true, true),
      (v_tpl, 'reporting', 'Reporting', 'Logs, KPIs, and reports.', 60, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Service pricing and options.', 70, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 80, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial and confidentiality terms.', 90, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'HSE Requirements') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'HSE Requirements', 'pdf', 'templates/rbf/facilities/hse_requirements.pdf', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'PPM Schedule Template') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'PPM Schedule Template', 'xlsx', 'templates/rbf/facilities/ppm_schedule_template.xlsx', 'always', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- Training & Capacity Building
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'Training & Capacity Building', 'RBF_TRAINING', 'training',
          'Training programs and capability development', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'learning_objectives';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Audience and training objectives.', 10, true, true),
      (v_tpl, 'learning_objectives', 'Learning Objectives', 'Expected outcomes and competencies.', 20, true, true),
      (v_tpl, 'curriculum_outline', 'Curriculum Outline', 'Modules and durations.', 30, true, true),
      (v_tpl, 'trainer_quals', 'Trainer Qualifications', 'Required experience and certifications.', 40, true, true),
      (v_tpl, 'delivery_method', 'Delivery Method', 'Onsite/online/hybrid details.', 50, true, true),
      (v_tpl, 'materials_ip', 'Materials & IP', 'Ownership of materials and usage rights.', 60, true, true),
      (v_tpl, 'assessment_evaluation', 'Assessment & Evaluation', 'Assessments and feedback instruments.', 70, true, true),
      (v_tpl, 'pricing', 'Pricing', 'Per-learner or program pricing.', 80, true, true),
      (v_tpl, 'evaluation', 'Evaluation', 'Method and weighting.', 90, true, true),
      (v_tpl, 'terms', 'Terms', 'Commercial and confidentiality terms.', 100, true, true);
  END IF;

  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Attendance Sheet') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Attendance Sheet', 'xlsx', 'templates/rbf/training/attendance_sheet.xlsx', 'always', 10);
  END IF;
  IF NOT EXISTS (SELECT 1 FROM rfq_template_attachments WHERE rfq_template_id = v_tpl AND label = 'Course Evaluation Form') THEN
    INSERT INTO rfq_template_attachments (rfq_template_id, label, filetype, file_url, include_when, sort_order)
    VALUES (v_tpl, 'Course Evaluation Form', 'docx', 'templates/rbf/training/course_evaluation_form.docx', 'awarded', 20);
  END IF;

  ---------------------------------------------------------------------------
  -- EOI: Market Sounding
  ---------------------------------------------------------------------------
  INSERT INTO rfq_templates (org_id, name, code, category, description, is_default, distribution_channels)
  VALUES (NULL, 'EOI: Market Sounding', 'RBF_EOI_MARKET', 'eoi',
          'Expression of Interest for market sounding', false, ARRAY['email','portal'])
  ON CONFLICT (code) DO UPDATE
    SET name = EXCLUDED.name,
        category = EXCLUDED.category,
        description = EXCLUDED.description,
        is_default = EXCLUDED.is_default,
        distribution_channels = EXCLUDED.distribution_channels
  RETURNING rfq_template_id INTO v_tpl;

  PERFORM 1 FROM rfq_template_sections WHERE rfq_template_id = v_tpl AND section_key = 'interest_statement';
  IF NOT FOUND THEN
    INSERT INTO rfq_template_sections (rfq_template_id, section_key, heading, body_markdown, sort_order, include_in_print, include_in_email)
    VALUES
      (v_tpl, 'background', 'Background', 'Purpose of EOI and context.', 10, true, true),
      (v_tpl, 'interest_statement', 'Statement of Interest', 'Vendor expression of interest and relevance.', 20, true, true),
      (v_tpl, 'capability_summary', 'Capability Summary', 'Core capabilities and capacity.', 30, true, true),
      (v_tpl, 'relevant_experience', 'Relevant Experience', 'Case studies and references.', 40, true, true),
      (v_tpl, 'indicative_approach', 'Indicative Approach', 'High-level approach and timeline.', 50, true, true),
      (v_tpl, 'submission', 'Submission', 'Instructions and deadline.', 60, true, true),
      (v_tpl, 'shortlisting_criteria', 'Shortlisting Criteria', 'How EOIs will be shortlisted.', 70, true, true);
  END IF;

END $$;

-- ============================================================================
-- INVENTORY MANAGEMENT FORMS (Phase 4 Declarative)
-- ============================================================================

-- Update forms sequence to avoid primary key conflicts
SELECT setval('forms_id_seq', COALESCE((SELECT MAX(id) FROM forms), 0) + 1, false);

-- Delete existing inventory forms if they exist
DELETE FROM forms WHERE name IN (
    'inventory_receive_form',
    'inventory_transfer_form', 
    'inventory_break_bulk_form',
    'inventory_adjustment_form',
    'warehouse_form'
);

-- Inventory Receive Form
INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'inventory_receive_form',
    'Receive inventory into the system from purchase orders or direct receipts',
    'inventory_receive',
    2,
    'grid',
    '[
        {
            "name": "lpo_number",
            "label": "LPO/PO Number",
            "type": "select-search",
            "required": false,
            "help_text": "Select an LPO to auto-populate products, or leave blank for direct receipt",
            "options_table": "purchase_orders",
            "options_label_column": "order_number",
            "options_value_column": "purchase_order_id",
            "onchange": "inventory:loadPurchaseOrderItems"
        },
        {
            "name": "vendor_id",
            "label": "Vendor/Supplier",
            "type": "select-search",
            "required": true,
            "options_table": "vendors",
            "options_label_column": "vendorname",
            "options_value_column": "vendor_id"
        },
        {
            "name": "branch_id",
            "label": "Receiving Warehouse/Branch",
            "type": "select",
            "required": true,
            "options_table": "branches",
            "options_label_column": "name",
            "options_value_column": "branch_id"
        },
        {
            "name": "delivery_note_number",
            "label": "Delivery Note Number",
            "type": "text",
            "required": false
        },
        {
            "name": "invoice_number",
            "label": "Invoice Number",
            "type": "text",
            "required": false
        },
        {
            "name": "date",
            "label": "Receive Date",
            "type": "date",
            "required": true,
            "default": "today"
        },
        {
            "name": "items",
            "label": "Items to Receive",
            "type": "dynamic-table",
            "required": true,
            "colspan": 2,
            "columns": [
                {
                    "name": "product_id",
                    "label": "Product",
                    "type": "select-search",
                    "options_table": "products",
                    "options_label_column": "name",
                    "options_value_column": "product_id"
                },
                {
                    "name": "quantity_ordered",
                    "label": "Qty Ordered",
                    "type": "number",
                    "readonly": true
                },
                {
                    "name": "quantity_received",
                    "label": "Qty Received",
                    "type": "number",
                    "required": true,
                    "min": 0.01
                },
                {
                    "name": "unit",
                    "label": "Unit",
                    "type": "select",
                    "options": [
                        {"value": "PIECE", "label": "Piece", "display_text": "Piece"},
                        {"value": "PACK", "label": "Pack", "display_text": "Pack"},
                        {"value": "CARTON", "label": "Carton", "display_text": "Carton"},
                        {"value": "BOX", "label": "Box", "display_text": "Box"}
                    ]
                },
                {
                    "name": "unit_cost",
                    "label": "Unit Cost",
                    "type": "number",
                    "step": 0.01
                },
                {
                    "name": "batch_number",
                    "label": "Batch #",
                    "type": "text"
                },
                {
                    "name": "expiry_date",
                    "label": "Expiry Date",
                    "type": "date"
                },
                {
                    "name": "notes",
                    "label": "Notes",
                    "type": "text"
                }
            ],
            "rows": []
        },
        {
            "name": "notes",
            "label": "General Notes",
            "type": "textarea",
            "required": false,
            "colspan": 2
        }
    ]'::jsonb
);

-- Inventory Transfer Form
INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'inventory_transfer_form',
    'Transfer inventory between warehouses or locations',
    'inventory_transfers',
    2,
    'grid',
    '[
        {
            "name": "from_branch_id",
            "label": "From Warehouse/Branch",
            "type": "select",
            "required": true,
            "options_table": "branches",
            "options_label_column": "name",
            "options_value_column": "branch_id"
        },
        {
            "name": "to_branch_id",
            "label": "To Warehouse/Branch",
            "type": "select",
            "required": true,
            "options_table": "branches",
            "options_label_column": "name",
            "options_value_column": "branch_id"
        },
        {
            "name": "transfer_date",
            "label": "Transfer Date",
            "type": "date",
            "required": true,
            "default": "today"
        },
        {
            "name": "reference_number",
            "label": "Transfer Reference",
            "type": "text",
            "required": false
        },
        {
            "name": "items",
            "label": "Items to Transfer",
            "type": "dynamic-table",
            "required": true,
            "colspan": 2,
            "columns": [
                {
                    "name": "product_id",
                    "label": "Product",
                    "type": "select-search",
                    "options_table": "products",
                    "options_label_column": "name",
                    "options_value_column": "product_id"
                },
                {
                    "name": "batch_number",
                    "label": "Batch Number",
                    "type": "text"
                },
                {
                    "name": "quantity",
                    "label": "Quantity",
                    "type": "number"
                },
                {
                    "name": "unit",
                    "label": "Unit",
                    "type": "select",
                    "options": [
                        {"value": "PIECE", "label": "Piece", "display_text": "Piece"},
                        {"value": "PACK", "label": "Pack", "display_text": "Pack"},
                        {"value": "CARTON", "label": "Carton", "display_text": "Carton"},
                        {"value": "BOX", "label": "Box", "display_text": "Box"}
                    ]
                },
                {
                    "name": "break_bulk",
                    "label": "Break Bulk?",
                    "type": "checkbox"
                },
                {
                    "name": "notes",
                    "label": "Notes",
                    "type": "text"
                }
            ],
            "rows": []
        },
        {
            "name": "notes",
            "label": "Transfer Notes",
            "type": "textarea",
            "required": false,
            "colspan": 2
        }
    ]'::jsonb
);

-- Inventory Break Bulk Form
INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'inventory_break_bulk_form',
    'Break down bulk items into smaller units',
    'inventory_breaks',
    2,
    'grid',
    '[
        {
            "name": "product_id",
            "label": "Product",
            "type": "select-search",
            "required": true,
            "options_table": "products",
            "options_label_column": "name",
            "options_value_column": "product_id",
            "help_text": "Select the product to break down into smaller units"
        },
        {
            "name": "branch_id",
            "label": "Warehouse/Branch",
            "type": "select",
            "required": true,
            "options_table": "branches",
            "options_label_column": "name",
            "options_value_column": "branch_id"
        },
        {
            "name": "from_unit",
            "label": "From Unit (Bulk)",
            "type": "select",
            "required": true,
            "options": [
                {"value": "CARTON", "label": "Carton", "display_text": "Carton"},
                {"value": "BOX", "label": "Box", "display_text": "Box"},
                {"value": "PACK", "label": "Pack", "display_text": "Pack"},
                {"value": "MASTER", "label": "Master Unit", "display_text": "Master Unit"}
            ],
            "help_text": "The larger unit you are breaking down"
        },
        {
            "name": "to_unit",
            "label": "To Unit (Individual)",
            "type": "select",
            "required": true,
            "options": [
                {"value": "PIECE", "label": "Piece", "display_text": "Piece"},
                {"value": "PACK", "label": "Pack", "display_text": "Pack"},
                {"value": "UNIT", "label": "Base Unit", "display_text": "Base Unit"}
            ],
            "help_text": "The smaller unit you are creating"
        },
        {
            "name": "quantity",
            "label": "Quantity to Break",
            "type": "number",
            "required": true,
            "min": 0.01,
            "help_text": "How many bulk units to break down"
        },
        {
            "name": "batch_number",
            "label": "Batch Number",
            "type": "text",
            "required": false
        },
        {
            "name": "notes",
            "label": "Notes",
            "type": "textarea",
            "required": false,
            "colspan": 2
        }
    ]'::jsonb
);

-- Inventory Adjustment Form
INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'inventory_adjustment_form',
    'Adjust inventory quantities for stock takes, damage, theft, etc.',
    'inventory_txn',
    2,
    'grid',
    '[
        {
            "name": "inventory_id",
            "label": "Inventory ID",
            "type": "hidden"
        },
        {
            "name": "product_name",
            "label": "Product",
            "type": "text",
            "required": false,
            "readonly": true,
            "visible_when": "inventory_id"
        },
        {
            "name": "product_id",
            "label": "Product",
            "type": "select-search",
            "required": true,
            "options_table": "products",
            "options_label_column": "name",
            "options_value_column": "product_id",
            "visible_when": "!inventory_id"
        },
        {
            "name": "current_qty",
            "label": "Current Stock",
            "type": "number",
            "readonly": true,
            "visible_when": "inventory_id"
        },
        {
            "name": "adjustment_type",
            "label": "Adjustment Type",
            "type": "select",
            "required": true,
            "options": [
                {"value": "increase", "label": "Increase (Add Stock)"},
                {"value": "decrease", "label": "Decrease (Remove Stock)"}
            ]
        },
        {
            "name": "quantity",
            "label": "Adjustment Quantity",
            "type": "number",
            "required": true,
            "min": 0.01
        },
        {
            "name": "branch_id",
            "label": "Warehouse/Branch",
            "type": "select",
            "required": true,
            "options_table": "branches",
            "options_label_column": "name",
            "options_value_column": "branch_id"
        },
        {
            "name": "reason",
            "label": "Reason",
            "type": "select",
            "required": true,
            "options": [
                {"value": "stock_take", "label": "Stock Take Correction"},
                {"value": "damage", "label": "Damaged Goods"},
                {"value": "theft", "label": "Theft/Loss"},
                {"value": "return", "label": "Customer Return"},
                {"value": "expired", "label": "Expired Product"},
                {"value": "other", "label": "Other"}
            ]
        },
        {
            "name": "reference_number",
            "label": "Reference Number",
            "type": "text",
            "required": false
        },
        {
            "name": "notes",
            "label": "Notes/Details",
            "type": "textarea",
            "required": true,
            "colspan": 2
        }
    ]'::jsonb
);

-- Warehouse Form
INSERT INTO forms (
    name,
    description,
    mapped_table,
    columns,
    layout_type,
    elements
) VALUES (
    'warehouse_form',
    'Create and manage warehouse/branch locations',
    'branches',
    2,
    'grid',
    '[
        {
            "name": "name",
            "label": "Warehouse/Branch Name",
            "type": "text",
            "required": true
        },
        {
            "name": "code",
            "label": "Warehouse Code",
            "type": "text",
            "required": true
        },
        {
            "name": "type",
            "label": "Location Type",
            "type": "select",
            "required": true,
            "options": [
                {"value": "warehouse", "label": "Warehouse"},
                {"value": "store", "label": "Store/Outlet"},
                {"value": "branch", "label": "Branch Office"},
                {"value": "transit", "label": "In Transit"}
            ]
        },
        {
            "name": "parent_branch_id",
            "label": "Parent Branch",
            "type": "select",
            "required": false,
            "options_table": "branches",
            "options_label_column": "name",
            "options_value_column": "branch_id",
            "options_where": "isDeleted = false",
            "help_text": "Leave empty for top-level branch. Select a parent to create a child branch (enables automatic inventory transfers between related branches)."
        },
        {
            "name": "address",
            "label": "Address",
            "type": "textarea",
            "required": false,
            "colspan": 2
        }
    ]'::jsonb
);

-- ============================================================================
-- POS Sessions View Permissions
-- ============================================================================
-- Grant select permissions on the enhanced POS sessions view
GRANT SELECT ON pos_sessions_with_details TO PUBLIC;

-- ============================================================================
-- Import/Export Configurations
-- ============================================================================
-- Default import/export configurations for various data types
-- ============================================================================

-- ============================================================================
-- Products Catalog (Generic)
-- ============================================================================
-- Import and export product catalog including all product details
-- Used by: /api/import-export/import/products_catalog and /api/import-export/export/products_catalog
-- ============================================================================
INSERT INTO import_export_configs (name, description, type, table_name, config) VALUES 
(
  'products_catalog',
  'Import and export product catalog including all product details',
  'both',
  'products',
  '{
    "table": "products",
    "primary_key": "product_id",
    "filename": "products_catalog",
    "columns": [
      "product_id",
      "name",
      "description",
      "price",
      "barcode",
      "brand",
      "product_number",
      "category_id",
      "tax_rates"
    ],
    "field_mappings": {
      "Product ID": "product_id",
      "Name": "name",
      "Description": "description",
      "Price": "price",
      "Barcode": "barcode",
      "Brand": "brand",
      "Product Number": "product_number",
      "Category": "category_id",
      "VAT Rate": {"column": "tax_rates", "json_field": "vat"},
      "STT Rate": {"column": "tax_rates", "json_field": "stt"}
    },
    "field_labels": {
      "product_id": "Product ID",
      "name": "Name",
      "description": "Description",
      "price": "Price",
      "barcode": "Barcode",
      "brand": "Brand",
      "product_number": "Product Number"
    },
    "transforms": {
      "price": {
        "type": "number",
        "decimals": 2
      },
      "category_id": {
        "type": "lookup",
        "table": "product_categories",
        "key_column": "name",
        "value_column": "category_id"
      }
    },
    "export_transforms": {
      "price": {
        "type": "number",
        "decimals": 2
      },
      "category_id": {
        "type": "lookup",
        "table": "product_categories",
        "key_column": "category_id",
        "display_column": "name"
      },
      "tax_rates": {
        "type": "json_decode"
      }
    },
    "validations": {
      "name": {
        "required": true,
        "max_length": 255
      },
      "price": {
        "required": false
      }
    },
    "unique_fields": ["barcode", "product_number"],
    "update_existing": true,
    "filters": {
      "isDeleted": false
    },
    "joins": [
      {
        "type": "left",
        "table": "product_categories",
        "first": "products.category_id",
        "operator": "=",
        "second": "product_categories.category_id"
      }
    ],
    "order_by": {
      "name": "ASC"
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name,
  config = EXCLUDED.config,
  updatedAt = now();

-- ============================================================================
-- GoFrugal Products Import
-- ============================================================================
-- Import products from GoFrugal POS system CSV export format
-- Handles category extraction and product parsing from GoFrugal structure
-- ============================================================================
INSERT INTO import_export_configs (name, description, type, table_name, config) VALUES 
(
  'products_gofrugal',
  'Import products from GoFrugal POS CSV export format with automatic category detection',
  'import',
  'products',
  '{
    "table": "products",
    "primary_key": "product_id",
    "filename": "products_gofrugal",
    "format": "gofrugal_csv",
    "field_mappings": {
      "Code": "barcode",
      "Name": "name", 
      "Description": "description",
      "Retail": "price",
      "COG": "cost_price"
    },
    "category_detection": {
      "enabled": true,
      "category_row_pattern": "^,Category :,,,,([^,]+),",
      "product_start_pattern": "^,[^,]+,",
      "skip_empty_rows": true,
      "skip_header_rows": 10
    },
    "default_values": {
      "tax_rates": {"vat": "G", "stt": ""},
      "base_unit": "PIECE",
      "isDeleted": false
    },
    "transforms": {
      "price": {
        "type": "number",
        "decimals": 2,
        "default": 0.00
      },
      "cost_price": {
        "type": "number",
        "decimals": 2,
        "default": 0.00
      },
      "barcode": {
        "type": "string",
        "trim": true,
        "required": true
      },
      "name": {
        "type": "string",
        "trim": true,
        "required": true,
        "max_length": 255
      }
    },
    "validations": {
      "name": {
        "required": true,
        "max_length": 255
      },
      "barcode": {
        "required": true,
        "max_length": 100
      },
      "price": {
        "required": false,
        "min": 0
      }
    },
    "unique_fields": ["barcode"],
    "update_existing": true,
    "filters": {
      "isDeleted": false
    },
    "import_options": {
      "create_categories": true,
      "skip_zero_qty": false,
      "auto_assign_org": true
    }
  }'
) ON CONFLICT (name) DO UPDATE SET
  description = EXCLUDED.description,
  type = EXCLUDED.type,
  table_name = EXCLUDED.table_name,
  config = EXCLUDED.config,
  updatedAt = now();

-- ============================================================================
-- Customer Management Forms
-- ============================================================================


-- Business Customer Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  200,
  'Business Customer',
  'Register business/corporate customer',
  'customers',
  $json$[
    {"label":"Company Name","field_name":"name","data_column":"name","type_id":1,"order_index":1,"required":true},
    {"label":"Business Registration Number","field_name":"business_reg_number","data_column":"business_reg_number","type_id":1,"order_index":2},
    {"label":"TIN","field_name":"tin","data_column":"tin","type_id":1,"order_index":3},
    {"label":"Email","field_name":"email","data_column":"email","type_id":15,"order_index":4},
    {"label":"Phone Number","field_name":"phone_number","data_column":"phone_number","type_id":1,"order_index":5},
    {"label":"Address","field_name":"address","data_column":"address","type_id":8,"order_index":6},
    {"label":"Contact Person Name","field_name":"contact_person_name","data_column":"contact_person_name","type_id":1,"order_index":7},
    {"label":"Contact Person Phone","field_name":"contact_person_phone","data_column":"contact_person_phone","type_id":1,"order_index":8},
    {"label":"Contact Person Email","field_name":"contact_person_email","data_column":"contact_person_email","type_id":15,"order_index":9},
    {"label":"Industry","field_name":"industry","data_column":"industry","type_id":1,"order_index":10},
    {"label":"Credit Limit","field_name":"credit_limit","data_column":"credit_limit","type_id":2,"order_index":11},
    {"field_name":"customer_type","data_column":"customer_type","type_id":17,"default_value":"business","order_index":12}
  ]$json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- Individual Customer Form
INSERT INTO forms (id, name, description, mapped_table, elements, tenant_id, branch_id)
VALUES (
  201,
  'Individual Customer',
  'Register individual/personal customer',
  'customers',
  $json$[
    {"label":"First Name","field_name":"first_name","data_column":"name","type_id":1,"order_index":1,"required":true,"transform":"combine_names"},
    {"label":"Last Name","field_name":"last_name","data_column":"name","type_id":1,"order_index":2,"required":true,"transform":"combine_names"},
    {"label":"TIN Number","field_name":"tin","data_column":"tin","type_id":1,"order_index":3},
    {"label":"Email","field_name":"email","data_column":"email","type_id":15,"order_index":4},
    {"label":"Phone Number","field_name":"phone_number","data_column":"phone_number","type_id":1,"order_index":5},
    {"label":"Address","field_name":"address","data_column":"address","type_id":8,"order_index":6},
    {"field_name":"customer_type","data_column":"customer_type","type_id":17,"default_value":"individual","order_index":7}
  ]$json$::jsonb,
  NULL,
  NULL
) ON CONFLICT (id) DO NOTHING;

-- ============================================================================
-- Invoice Form (Declarative)
-- ============================================================================

-- Delete existing form if it exists
DELETE FROM forms WHERE id = 9002;

-- Insert declarative quotation form definition
INSERT INTO forms (id, name, description, mapped_table, columns, layout_type, elements, isdeleted) VALUES (
  9002,
  'Quotation_Form',
  'Create and edit quotations with line items, formulas, and sections',
  'quotations',
  1,
  'grid',
  $json$[
    {
      "step": 1,
      "order": 1,
      "layout": {
        "type": "flex",
        "classes": "flex flex-wrap gap-4",
        "gap": "1rem"
      },
      "elements": [
        {
          "name": "customer_id",
          "field_name": "customer_id",
          "label": "Customer",
          "type": "select-search",
          "type_id": 6,
          "width": "flex-1",
          "options_table": "customers",
          "options_label_column": "name",
          "options_value_column": "customer_id",
          "required": true,
          "validation": {"required": true}
        },
        {
          "name": "quotation_date",
          "field_name": "quotation_date",
          "label": "Quotation Date",
          "type": "date",
          "type_id": 8,
          "width": "flex-1",
          "default_value": "today"
        },
        {
          "name": "due_date",
          "field_name": "due_date",
          "label": "Due Date",
          "type": "date",
          "type_id": 8,
          "width": "flex-1"
        }
      ]
    },
    {
      "step": 2,
      "order": 2,
      "layout": {
        "type": "grid",
        "classes": "form-grid",
        "columns": "1fr",
        "gap": "1rem"
      },
      "elements": [
        {
          "name": "line_items",
          "field_name": "line_items",
          "label": "Line Items",
          "type": "dynamic-table",
          "type_id": 15,
          "columns": [
            {
              "name": "name",
              "label": "Item Name",
              "type": "text",
              "width": "25%"
            },
            {
              "name": "description",
              "label": "Description",
              "type": "text",
              "width": "30%"
            },
            {
              "name": "unit_price",
              "label": "Unit Price",
              "type": "number",
              "width": "15%",
              "attrs": {"step": "0.01", "min": "0"}
            },
            {
              "name": "quantity",
              "label": "Quantity",
              "type": "number",
              "width": "10%",
              "attrs": {"step": "1", "min": "1"}
            },
            {
              "name": "total",
              "label": "Total",
              "type": "number",
              "width": "15%",
              "readonly": true,
              "formula": "unit_price * quantity",
              "attrs": {"step": "0.01"}
            }
          ],
          "aggregates": [
            {
              "column": "total",
              "operation": "sum",
              "display": "Grand Total: ${{value}}"
            }
          ],

          "min_rows": 1,
          "add_button_text": "Add Product",
          "row_label_prefix": "Product"
        }
      ]
    },
    {
      "step": 4,
      "order": 4,
      "layout": {
        "type": "flex",
        "classes": "flex flex-wrap gap-4",
        "gap": "1rem"
      },
      "elements": [
        {
          "name": "notes_additional",
          "field_name": "notes_additional",
          "label": "Additional Notes",
          "type": "textarea",
          "type_id": 2,
          "width": "flex-1",
          "rows": 4,
          "placeholder": "Enter additional notes..."
        },
        {
          "name": "payment_terms_details",
          "field_name": "payment_terms_details",
          "label": "Payment Terms Details",
          "type": "textarea",
          "type_id": 2,
          "width": "flex-1",
          "rows": 4,
          "placeholder": "Enter payment terms details..."
        }
      ]
    },
    {
      "name": "submit_btn",
      "type": "button",
      "label": "Save Quotation",
      "classes": ["btn", "btn-primary", "mr-2"],
      "action": "submit"
    },
    {
      "name": "cancel_btn",
      "type": "button",
      "label": "Cancel",
      "classes": ["btn", "btn-ghost"],
      "action": "close"
    }
  ]$json$::jsonb,
  false
);

-- ============================================================================
-- Quotation Form (Declarative)
-- ============================================================================

-- Delete existing form if it exists
DELETE FROM forms WHERE id = 9001;

-- Insert declarative invoice form definition
INSERT INTO forms (id, name, description, mapped_table, columns, layout_type, elements, isdeleted) VALUES (
  9001,
  'Invoice_Form',
  'Create and edit invoices with line items, formulas, and sections',
  'generic_invoices',
  1,
  'grid',
  $json$[
    {
      "step": 1,
      "order": 1,
      "layout": {
        "type": "flex",
        "classes": "flex flex-wrap gap-4",
        "gap": "1rem"
      },
      "elements": [
        {
          "name": "customer_id",
          "field_name": "customer_id",
          "label": "Customer",
          "type": "select-search",
          "type_id": 6,
          "width": "flex-1",
          "options_table": "customers",
          "options_label_column": "name",
          "options_value_column": "customer_id",
          "required": true,
          "validation": {"required": true}
        },
        {
          "name": "date",
          "field_name": "date",
          "label": "Invoice Date",
          "type": "date",
          "type_id": 8,
          "width": "flex-1",
          "default_value": "today"
        },
        {
          "name": "due_date",
          "field_name": "due_date",
          "label": "Due Date",
          "type": "date",
          "type_id": 8,
          "width": "flex-1"
        }
      ]
    },
    {
      "step": 2,
      "order": 2,
      "layout": {
        "type": "grid",
        "classes": "form-grid",
        "columns": "1fr",
        "gap": "1rem"
      },
      "elements": [
        {
          "name": "line_items",
          "field_name": "line_items",
          "label": "Line Items",
          "type": "dynamic-table",
          "type_id": 15,
          "columns": [
            {
              "name": "name",
              "label": "Item Name",
              "type": "text",
              "width": "25%"
            },
            {
              "name": "description",
              "label": "Description",
              "type": "text",
              "width": "30%"
            },
            {
              "name": "unit_price",
              "label": "Unit Price",
              "type": "number",
              "width": "15%",
              "attrs": {"step": "0.01", "min": "0"}
            },
            {
              "name": "quantity",
              "label": "Quantity",
              "type": "number",
              "width": "10%",
              "attrs": {"step": "1", "min": "1"}
            },
            {
              "name": "total",
              "label": "Total",
              "type": "number",
              "width": "15%",
              "readonly": true,
              "formula": "unit_price * quantity",
              "attrs": {"step": "0.01"}
            }
          ],
          "aggregates": [
            {
              "column": "total",
              "operation": "sum",
              "display": "Grand Total: ${{value}}"
            }
          ],

          "min_rows": 1,
          "add_button_text": "Add Product",
          "row_label_prefix": "Product"
        }
      ]
    },
    {
      "step": 4,
      "order": 4,
      "layout": {
        "type": "flex",
        "classes": "flex flex-wrap gap-4",
        "gap": "1rem"
      },
      "elements": [
        {
          "name": "notes_additional",
          "field_name": "notes_additional",
          "label": "Additional Notes",
          "type": "textarea",
          "type_id": 2,
          "width": "flex-1",
          "rows": 4,
          "placeholder": "Enter additional notes..."
        },
        {
          "name": "payment_terms_details",
          "field_name": "payment_terms_details",
          "label": "Payment Terms Details",
          "type": "textarea",
          "type_id": 2,
          "width": "flex-1",
          "rows": 4,
          "placeholder": "Enter payment terms details..."
        }
      ]
    },
    {
      "name": "submit_btn",
      "type": "button",
      "label": "Save Invoice",
      "classes": ["btn", "btn-primary", "mr-2"],
      "action": "submit"
    },
    {
      "name": "cancel_btn",
      "type": "button",
      "label": "Cancel",
      "classes": ["btn", "btn-ghost"],
      "action": "close"
    }
  ]$json$::jsonb,
  false
);
