-- AmCEO Cloud Sync — run in Supabase SQL Editor
-- Pilot tables (unchanged for backward compatibility)

CREATE TABLE IF NOT EXISTS businesses (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL,
  type TEXT NOT NULL,
  country TEXT DEFAULT 'Ghana',
  city TEXT,
  currency TEXT DEFAULT 'GHS',
  logo_url TEXT,
  settings JSONB DEFAULT '{}',
  tax_settings JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS team_members (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  email TEXT NOT NULL,
  full_name TEXT,
  role TEXT NOT NULL,
  status TEXT DEFAULT 'invited',
  invited_at TIMESTAMPTZ DEFAULT NOW(),
  accepted_at TIMESTAMPTZ
);

ALTER TABLE businesses ENABLE ROW LEVEL SECURITY;
ALTER TABLE team_members ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Allow all for authenticated users" ON businesses;
CREATE POLICY "Allow all for authenticated users"
ON businesses FOR ALL USING (
  auth.role() = 'authenticated' OR auth.role() = 'service_role'
);

DROP POLICY IF EXISTS "Allow all for authenticated users" ON team_members;
CREATE POLICY "Allow all for authenticated users"
ON team_members FOR ALL USING (
  auth.role() = 'authenticated' OR auth.role() = 'service_role'
);


-- Shared trigger: bump version on every update.
-- Cloud Sync LWW: preserve a client-supplied updated_at when it is strictly
-- newer than the stored value. Blindly stamping NOW() let a stale upsert win
-- over a newer peer edit (CRM conflict test failure on live Supabase).
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.updated_at IS NULL OR NEW.updated_at <= OLD.updated_at THEN
    NEW.updated_at = NOW();
  END IF;
  NEW.version = OLD.version + 1;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- finance_accounts (Chart of Accounts — Cloud Sync F1 LWW)
CREATE TABLE IF NOT EXISTS finance_accounts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  code TEXT NOT NULL,
  name TEXT,
  type TEXT,
  category TEXT,
  normal_balance TEXT,
  is_system BOOLEAN DEFAULT false,
  is_active BOOLEAN DEFAULT true,
  parent_code TEXT,
  balance NUMERIC DEFAULT 0,
  currency TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_accounts_tenant_business ON finance_accounts (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_accounts_tenant_updated ON finance_accounts (tenant_id, updated_at);
CREATE UNIQUE INDEX IF NOT EXISTS uq_finance_accounts_business_code
  ON finance_accounts (business_id, code)
  WHERE business_id IS NOT NULL AND code IS NOT NULL;

ALTER TABLE finance_accounts ADD COLUMN IF NOT EXISTS category TEXT;
ALTER TABLE finance_accounts ADD COLUMN IF NOT EXISTS normal_balance TEXT;
ALTER TABLE finance_accounts ADD COLUMN IF NOT EXISTS is_system BOOLEAN DEFAULT false;
ALTER TABLE finance_accounts ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT true;
ALTER TABLE finance_accounts ADD COLUMN IF NOT EXISTS parent_code TEXT;
ALTER TABLE finance_accounts ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- finance_transactions
CREATE TABLE IF NOT EXISTS finance_transactions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  date DATE NOT NULL, description TEXT, amount NUMERIC NOT NULL, type TEXT, category TEXT, payment_method TEXT, account_id UUID, reference TEXT, tags JSONB DEFAULT '[]', receipt_url TEXT, source TEXT DEFAULT NULL, source_type TEXT DEFAULT NULL,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_transactions_tenant_business ON finance_transactions (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_transactions_tenant_updated ON finance_transactions (tenant_id, updated_at);

ALTER TABLE finance_transactions
  ADD COLUMN IF NOT EXISTS source TEXT DEFAULT NULL;

ALTER TABLE finance_transactions
  ADD COLUMN IF NOT EXISTS source_type TEXT DEFAULT NULL;

ALTER TABLE finance_transactions
  ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- finance_invoices
CREATE TABLE IF NOT EXISTS finance_invoices (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  invoice_number TEXT NOT NULL, customer_id UUID, customer_name TEXT, items JSONB DEFAULT '[]', subtotal NUMERIC DEFAULT 0, tax_amount NUMERIC DEFAULT 0, total NUMERIC DEFAULT 0, status TEXT DEFAULT 'draft', issue_date DATE, due_date DATE, paid_at TIMESTAMPTZ, notes TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_invoices_tenant_business ON finance_invoices (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_invoices_tenant_updated ON finance_invoices (tenant_id, updated_at);
ALTER TABLE finance_invoices ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- finance_bills
CREATE TABLE IF NOT EXISTS finance_bills (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  bill_number TEXT, vendor_name TEXT, vendor_id UUID, items JSONB DEFAULT '[]', subtotal NUMERIC DEFAULT 0, tax_amount NUMERIC DEFAULT 0, total NUMERIC DEFAULT 0, status TEXT DEFAULT 'pending', bill_date DATE, due_date DATE, paid_at TIMESTAMPTZ, notes TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_bills_tenant_business ON finance_bills (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_bills_tenant_updated ON finance_bills (tenant_id, updated_at);
ALTER TABLE finance_bills ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- finance_journal_entries (envelope append-only + status/void for F2)
CREATE TABLE IF NOT EXISTS finance_journal_entries (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  reference TEXT, date DATE NOT NULL, description TEXT, lines JSONB DEFAULT '[]', source TEXT, source_id UUID,
  status TEXT DEFAULT 'Posted',
  posted_at TIMESTAMPTZ DEFAULT NULL,
  voided_at TIMESTAMPTZ DEFAULT NULL,
  created_by TEXT,
  currency TEXT,
  exchange_rate NUMERIC DEFAULT 1,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_journal_entries_tenant_business ON finance_journal_entries (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_journal_entries_tenant_updated ON finance_journal_entries (tenant_id, updated_at);
ALTER TABLE finance_journal_entries ADD COLUMN IF NOT EXISTS status TEXT DEFAULT 'Posted';
ALTER TABLE finance_journal_entries ADD COLUMN IF NOT EXISTS posted_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE finance_journal_entries ADD COLUMN IF NOT EXISTS voided_at TIMESTAMPTZ DEFAULT NULL;
ALTER TABLE finance_journal_entries ADD COLUMN IF NOT EXISTS created_by TEXT;
ALTER TABLE finance_journal_entries ADD COLUMN IF NOT EXISTS currency TEXT;
ALTER TABLE finance_journal_entries ADD COLUMN IF NOT EXISTS exchange_rate NUMERIC DEFAULT 1;
ALTER TABLE finance_journal_entries ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- finance_budgets (LWW F1 — amounts JSONB holds local lines[] envelope)
CREATE TABLE IF NOT EXISTS finance_budgets (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  year INTEGER NOT NULL, period_type TEXT DEFAULT 'monthly', amounts JSONB DEFAULT '{}',
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_budgets_tenant_business ON finance_budgets (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_budgets_tenant_updated ON finance_budgets (tenant_id, updated_at);
ALTER TABLE finance_budgets ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- finance_payables
CREATE TABLE IF NOT EXISTS finance_payables (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  description TEXT NOT NULL, type TEXT, amount NUMERIC NOT NULL, due_date DATE, status TEXT DEFAULT 'pending', paid_at TIMESTAMPTZ, reference TEXT, source_id UUID,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_payables_tenant_business ON finance_payables (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_payables_tenant_updated ON finance_payables (tenant_id, updated_at);
ALTER TABLE finance_payables ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- finance_bank_accounts (F0 schema; sync in F4)
-- Full account numbers stay LOCAL ONLY this pass — cloud stores last-4 only.
-- (Client-side encryptCredential is device-bound; peers cannot decrypt shared ciphertexts.)
CREATE TABLE IF NOT EXISTS finance_bank_accounts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL,
  bank_name TEXT,
  account_number_last4 TEXT,
  account_type TEXT,
  currency TEXT DEFAULT 'GHS',
  coa_account_code TEXT,
  opening_balance NUMERIC DEFAULT 0,
  opening_date DATE,
  is_active BOOLEAN DEFAULT true,
  contact_email TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_bank_accounts_tenant_business ON finance_bank_accounts (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_bank_accounts_tenant_updated ON finance_bank_accounts (tenant_id, updated_at);

-- finance_invoice_payments (append-only; sync wiring in F3)
CREATE TABLE IF NOT EXISTS finance_invoice_payments (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  invoice_id UUID NOT NULL,
  date DATE NOT NULL,
  amount NUMERIC NOT NULL,
  method TEXT,
  reference TEXT,
  bank_account_code TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_invoice_payments_tenant_business ON finance_invoice_payments (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_invoice_payments_invoice ON finance_invoice_payments (invoice_id);

-- finance_bill_payments (append-only; sync wiring in F3)
CREATE TABLE IF NOT EXISTS finance_bill_payments (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  bill_id UUID NOT NULL,
  date DATE NOT NULL,
  amount NUMERIC NOT NULL,
  method TEXT,
  reference TEXT,
  bank_account_code TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_bill_payments_tenant_business ON finance_bill_payments (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_bill_payments_bill ON finance_bill_payments (bill_id);

-- finance_reconciliations (append-only completed history; sync in F4)
-- In-progress wizard state is React-only and never synced.
CREATE TABLE IF NOT EXISTS finance_reconciliations (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  bank_account_code TEXT NOT NULL,
  bank_account_name TEXT,
  period_end DATE NOT NULL,
  opening_balance NUMERIC DEFAULT 0,
  closing_balance NUMERIC DEFAULT 0,
  book_balance NUMERIC DEFAULT 0,
  difference NUMERIC DEFAULT 0,
  deposits_in_transit NUMERIC DEFAULT 0,
  outstanding_checks NUMERIC DEFAULT 0,
  matched_transaction_ids JSONB DEFAULT '[]'::jsonb,
  status TEXT NOT NULL DEFAULT 'Completed',
  created_by TEXT,
  completed_at TIMESTAMPTZ,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_finance_reconciliations_tenant_business ON finance_reconciliations (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_finance_reconciliations_tenant_updated ON finance_reconciliations (tenant_id, updated_at);

-- hr_employees (H0/H1 Cloud Sync)
-- Salary syncs full among licensed peers; UI gates with AmCEO view_salaries.
-- Bank: bank_account_last4 only — full PAN local + secret-at-rest. No TIN/SSNIT columns.
CREATE TABLE IF NOT EXISTS hr_employees (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  employee_number TEXT, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, phone TEXT, department TEXT, employment_type TEXT, job_title TEXT, salary NUMERIC DEFAULT 0, hourly_rate NUMERIC, daily_rate NUMERIC, session_rate NUMERIC, contract_start_date DATE, contract_end_date DATE, is_recurring BOOLEAN DEFAULT false, tax_settings JSONB DEFAULT '{}', bank_details JSONB DEFAULT '{}', status TEXT DEFAULT 'active',
  bank_account_last4 TEXT,
  verification_token TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_hr_employees_tenant_business ON hr_employees (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_hr_employees_tenant_updated ON hr_employees (tenant_id, updated_at);
ALTER TABLE hr_employees ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;
ALTER TABLE hr_employees ADD COLUMN IF NOT EXISTS bank_account_last4 TEXT;
ALTER TABLE hr_employees ADD COLUMN IF NOT EXISTS verification_token TEXT;

-- hr_payroll_runs (H0 meta; H3 append-only ProcessedPayrollRun envelopes)
CREATE TABLE IF NOT EXISTS hr_payroll_runs (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  period_month INTEGER NOT NULL, period_year INTEGER NOT NULL, status TEXT DEFAULT 'draft', total_gross NUMERIC DEFAULT 0, total_net NUMERIC DEFAULT 0, total_paye NUMERIC DEFAULT 0, total_ssnit NUMERIC DEFAULT 0, total_wht NUMERIC DEFAULT 0, entries JSONB DEFAULT '[]', processed_at TIMESTAMPTZ, processed_by UUID,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_hr_payroll_runs_tenant_business ON hr_payroll_runs (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_hr_payroll_runs_tenant_updated ON hr_payroll_runs (tenant_id, updated_at);
ALTER TABLE hr_payroll_runs ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- hr_leave_requests (H0 meta; LWW status in H2)
CREATE TABLE IF NOT EXISTS hr_leave_requests (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  employee_id UUID NOT NULL, type TEXT NOT NULL, start_date DATE NOT NULL, end_date DATE NOT NULL, days NUMERIC NOT NULL, reason TEXT, status TEXT DEFAULT 'pending', approved_by UUID, approved_at TIMESTAMPTZ,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_hr_leave_requests_tenant_business ON hr_leave_requests (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_hr_leave_requests_tenant_updated ON hr_leave_requests (tenant_id, updated_at);
ALTER TABLE hr_leave_requests ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- hr_contracts (H0 meta; LWW in later pass — letter PDFs stay local-only)
CREATE TABLE IF NOT EXISTS hr_contracts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  employee_id UUID NOT NULL, contract_type TEXT NOT NULL, start_date DATE NOT NULL, end_date DATE, job_title TEXT, department TEXT, gross_salary NUMERIC DEFAULT 0, key_terms TEXT, status TEXT DEFAULT 'draft',
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_hr_contracts_tenant_business ON hr_contracts (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_hr_contracts_tenant_updated ON hr_contracts (tenant_id, updated_at);
ALTER TABLE hr_contracts ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- hr_contract_history (H2 append-only audit; upsert-by-id, never LWW-replace)
CREATE TABLE IF NOT EXISTS hr_contract_history (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  employee_id UUID NOT NULL,
  employee_name TEXT,
  created_by TEXT,
  summary TEXT,
  changes JSONB DEFAULT '[]'::jsonb,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_hr_contract_history_tenant_business ON hr_contract_history (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_hr_contract_history_tenant_updated ON hr_contract_history (tenant_id, updated_at);
CREATE INDEX IF NOT EXISTS idx_hr_contract_history_employee ON hr_contract_history (tenant_id, employee_id);

-- hr_staff_documents (H5b metadata; blobs in Storage bucket amceo-hr-documents)
CREATE TABLE IF NOT EXISTS hr_staff_documents (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  employee_id UUID NOT NULL,
  file_name TEXT NOT NULL,
  category TEXT NOT NULL DEFAULT 'other',
  mime_type TEXT NOT NULL,
  size_bytes BIGINT NOT NULL DEFAULT 0,
  storage_path TEXT,
  uploaded_by TEXT,
  uploaded_at TIMESTAMPTZ,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_hr_staff_documents_tenant_business
  ON hr_staff_documents (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_hr_staff_documents_tenant_updated
  ON hr_staff_documents (tenant_id, updated_at);
CREATE INDEX IF NOT EXISTS idx_hr_staff_documents_employee
  ON hr_staff_documents (tenant_id, employee_id);

ALTER TABLE hr_staff_documents
  ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;


-- inventory_products
CREATE TABLE IF NOT EXISTS inventory_products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, sku TEXT, barcode TEXT, category TEXT, unit_price NUMERIC DEFAULT 0, cost_price NUMERIC DEFAULT NULL, selling_price NUMERIC DEFAULT NULL, margin_percent NUMERIC DEFAULT NULL, markup_percent NUMERIC DEFAULT NULL, current_stock NUMERIC DEFAULT 0, reorder_level NUMERIC DEFAULT 0, supplier_id UUID, unit_of_measure TEXT DEFAULT 'unit', vat_rate NUMERIC DEFAULT 0, purpose TEXT DEFAULT 'sales', notes TEXT, is_active BOOLEAN DEFAULT true,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_inventory_products_tenant_business ON inventory_products (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_inventory_products_tenant_updated ON inventory_products (tenant_id, updated_at);

ALTER TABLE inventory_products
  ADD COLUMN IF NOT EXISTS cost_price NUMERIC DEFAULT NULL;
ALTER TABLE inventory_products
  ADD COLUMN IF NOT EXISTS selling_price NUMERIC DEFAULT NULL;
ALTER TABLE inventory_products
  ADD COLUMN IF NOT EXISTS margin_percent NUMERIC DEFAULT NULL;
ALTER TABLE inventory_products
  ADD COLUMN IF NOT EXISTS markup_percent NUMERIC DEFAULT NULL;
ALTER TABLE inventory_products
  ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- inventory_movements
CREATE TABLE IF NOT EXISTS inventory_movements (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  product_id UUID NOT NULL, type TEXT NOT NULL, quantity NUMERIC NOT NULL, reason TEXT, reference TEXT, notes TEXT, created_by UUID,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

ALTER TABLE inventory_movements
  ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

CREATE INDEX IF NOT EXISTS idx_inventory_movements_tenant_business ON inventory_movements (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_tenant_updated ON inventory_movements (tenant_id, updated_at);

-- scm_suppliers
CREATE TABLE IF NOT EXISTS scm_suppliers (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, contact_person TEXT, email TEXT, phone TEXT, address TEXT, city TEXT, payment_terms TEXT, tax_number TEXT, bank_details JSONB DEFAULT '{}', categories JSONB DEFAULT '[]', status TEXT DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_scm_suppliers_tenant_business ON scm_suppliers (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_scm_suppliers_tenant_updated ON scm_suppliers (tenant_id, updated_at);

-- scm_purchase_orders
CREATE TABLE IF NOT EXISTS scm_purchase_orders (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  po_number TEXT NOT NULL, supplier_id UUID NOT NULL, items JSONB DEFAULT '[]', subtotal NUMERIC DEFAULT 0, tax_amount NUMERIC DEFAULT 0, total NUMERIC DEFAULT 0, payment_terms TEXT, delivery_address TEXT, expected_delivery DATE, status TEXT DEFAULT 'draft', notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_scm_purchase_orders_tenant_business ON scm_purchase_orders (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_scm_purchase_orders_tenant_updated ON scm_purchase_orders (tenant_id, updated_at);

-- scm_grns
CREATE TABLE IF NOT EXISTS scm_grns (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  grn_number TEXT NOT NULL, po_id UUID, supplier_id UUID, items JSONB DEFAULT '[]', status TEXT DEFAULT 'draft', received_by UUID, received_at TIMESTAMPTZ, notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_scm_grns_tenant_business ON scm_grns (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_scm_grns_tenant_updated ON scm_grns (tenant_id, updated_at);

-- scm_requisitions
CREATE TABLE IF NOT EXISTS scm_requisitions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  req_number TEXT NOT NULL, items JSONB DEFAULT '[]', department TEXT, priority TEXT, justification TEXT, date_required DATE, status TEXT DEFAULT 'draft', requested_by UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_scm_requisitions_tenant_business ON scm_requisitions (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_scm_requisitions_tenant_updated ON scm_requisitions (tenant_id, updated_at);

-- assets
CREATE TABLE IF NOT EXISTS assets (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, category TEXT, serial_number TEXT, purchase_date DATE, purchase_cost NUMERIC DEFAULT 0, current_value NUMERIC DEFAULT 0, depreciation_method TEXT, depreciation_rate NUMERIC DEFAULT 0, location TEXT, status TEXT DEFAULT 'active', notes TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_assets_tenant_business ON assets (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_assets_tenant_updated ON assets (tenant_id, updated_at);

-- crm_companies
CREATE TABLE IF NOT EXISTS crm_companies (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, industry TEXT, website TEXT, phone TEXT, email TEXT, address TEXT, city TEXT, annual_revenue NUMERIC, employee_count INTEGER, notes TEXT, tags JSONB DEFAULT '[]', status TEXT DEFAULT 'active', assigned_to UUID,
  meta JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_crm_companies_tenant_business ON crm_companies (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_crm_companies_tenant_updated ON crm_companies (tenant_id, updated_at);

ALTER TABLE crm_companies ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}';

-- crm_contacts
CREATE TABLE IF NOT EXISTS crm_contacts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT, phone TEXT, job_title TEXT, department TEXT, company_id UUID, company_name TEXT, notes TEXT, tags JSONB DEFAULT '[]', status TEXT DEFAULT 'active', assigned_to UUID, linked_customer_id UUID,
  meta JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_crm_contacts_tenant_business ON crm_contacts (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_crm_contacts_tenant_updated ON crm_contacts (tenant_id, updated_at);

ALTER TABLE crm_contacts ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}';

-- crm_deals
CREATE TABLE IF NOT EXISTS crm_deals (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  title TEXT NOT NULL, value NUMERIC DEFAULT 0, currency TEXT DEFAULT 'GHS', stage TEXT DEFAULT 'lead', probability INTEGER DEFAULT 0, expected_close_date DATE, contact_id UUID, company_id UUID, assigned_to UUID, description TEXT, notes TEXT, linked_invoice_ids JSONB DEFAULT '[]', linked_transaction_ids JSONB DEFAULT '[]',
  meta JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_crm_deals_tenant_business ON crm_deals (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_crm_deals_tenant_updated ON crm_deals (tenant_id, updated_at);

ALTER TABLE crm_deals ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}';

-- crm_activities
CREATE TABLE IF NOT EXISTS crm_activities (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  type TEXT NOT NULL, title TEXT NOT NULL, description TEXT, date TIMESTAMPTZ, due_date TIMESTAMPTZ, completed BOOLEAN DEFAULT false, contact_id UUID, deal_id UUID, company_id UUID, created_by UUID,
  meta JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_crm_activities_tenant_business ON crm_activities (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_crm_activities_tenant_updated ON crm_activities (tenant_id, updated_at);

ALTER TABLE crm_activities ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}';

-- marketing_campaigns
CREATE TABLE IF NOT EXISTS marketing_campaigns (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, goal TEXT, type TEXT, status TEXT DEFAULT 'draft', platforms JSONB DEFAULT '{}', audience JSONB DEFAULT '{}', content JSONB DEFAULT '{}', schedule JSONB DEFAULT '{}', tracking JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_marketing_campaigns_tenant_business ON marketing_campaigns (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_marketing_campaigns_tenant_updated ON marketing_campaigns (tenant_id, updated_at);

-- marketing_segments
CREATE TABLE IF NOT EXISTS marketing_segments (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, description TEXT, filters JSONB DEFAULT '{}', contact_count INTEGER DEFAULT 0,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_marketing_segments_tenant_business ON marketing_segments (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_marketing_segments_tenant_updated ON marketing_segments (tenant_id, updated_at);

-- marketing_contacts
CREATE TABLE IF NOT EXISTS marketing_contacts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, email TEXT, phone TEXT, source TEXT, tags JSONB DEFAULT '[]', status TEXT DEFAULT 'active', unsubscribed_at TIMESTAMPTZ, last_campaign_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_marketing_contacts_tenant_business ON marketing_contacts (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_marketing_contacts_tenant_updated ON marketing_contacts (tenant_id, updated_at);

-- marketing_templates
CREATE TABLE IF NOT EXISTS marketing_templates (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, type TEXT, subject TEXT, content TEXT, variables JSONB DEFAULT '[]', business_type TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_marketing_templates_tenant_business ON marketing_templates (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_marketing_templates_tenant_updated ON marketing_templates (tenant_id, updated_at);

-- helpdesk_tickets
CREATE TABLE IF NOT EXISTS helpdesk_tickets (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  ticket_number TEXT NOT NULL, title TEXT NOT NULL, description TEXT, type TEXT DEFAULT 'internal', status TEXT DEFAULT 'open', priority TEXT DEFAULT 'medium', category TEXT, reporter JSONB DEFAULT '{}', assigned_to UUID, linked_module JSONB DEFAULT '{}', sla JSONB DEFAULT '{}', tags JSONB DEFAULT '[]', source TEXT DEFAULT 'in_app', closed_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_helpdesk_tickets_tenant_business ON helpdesk_tickets (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_helpdesk_tickets_tenant_updated ON helpdesk_tickets (tenant_id, updated_at);

-- helpdesk_comments
CREATE TABLE IF NOT EXISTS helpdesk_comments (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  ticket_id UUID NOT NULL, author_id UUID, author_name TEXT, author_type TEXT, content TEXT NOT NULL, is_internal BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_helpdesk_comments_tenant_business ON helpdesk_comments (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_helpdesk_comments_tenant_updated ON helpdesk_comments (tenant_id, updated_at);

-- pos_sessions
CREATE TABLE IF NOT EXISTS pos_sessions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  session_number TEXT NOT NULL, opened_by UUID, opened_at TIMESTAMPTZ, closed_at TIMESTAMPTZ, opening_float NUMERIC DEFAULT 0, closing_float NUMERIC, total_sales NUMERIC DEFAULT 0, total_cash NUMERIC DEFAULT 0, total_card NUMERIC DEFAULT 0, total_mobile_money NUMERIC DEFAULT 0, total_refunds NUMERIC DEFAULT 0, status TEXT DEFAULT 'open',
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_pos_sessions_tenant_business ON pos_sessions (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_pos_sessions_tenant_updated ON pos_sessions (tenant_id, updated_at);
ALTER TABLE pos_sessions ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- pos_products (LEGACY catalog — NOT wired for Cloud Sync; terminal uses Inventory)
CREATE TABLE IF NOT EXISTS pos_products (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, sku TEXT, barcode TEXT, price NUMERIC DEFAULT 0, category TEXT, tax_rate NUMERIC DEFAULT 0, stock_tracked BOOLEAN DEFAULT false, current_stock NUMERIC DEFAULT 0, is_active BOOLEAN DEFAULT true,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_pos_products_tenant_business ON pos_products (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_pos_products_tenant_updated ON pos_products (tenant_id, updated_at);

-- pos_transactions
CREATE TABLE IF NOT EXISTS pos_transactions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  receipt_number TEXT NOT NULL, session_id UUID, items JSONB DEFAULT '[]', subtotal NUMERIC DEFAULT 0, discount_amount NUMERIC DEFAULT 0, tax_amount NUMERIC DEFAULT 0, total NUMERIC DEFAULT 0, payment_method TEXT, payment_details JSONB DEFAULT '{}', customer_name TEXT, customer_phone TEXT, status TEXT DEFAULT 'completed', served_by UUID, voided_at TIMESTAMPTZ, void_reason TEXT, refunded_from UUID,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_pos_transactions_tenant_business ON pos_transactions (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_pos_transactions_tenant_updated ON pos_transactions (tenant_id, updated_at);
ALTER TABLE pos_transactions ADD COLUMN IF NOT EXISTS meta JSONB DEFAULT '{}'::jsonb;

-- task_projects
CREATE TABLE IF NOT EXISTS task_projects (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  name TEXT NOT NULL, description TEXT, type TEXT, color TEXT, status TEXT DEFAULT 'active', start_date DATE, end_date DATE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_task_projects_tenant_business ON task_projects (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_task_projects_tenant_updated ON task_projects (tenant_id, updated_at);

-- tasks
CREATE TABLE IF NOT EXISTS tasks (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  title TEXT NOT NULL, description TEXT, status TEXT DEFAULT 'todo', priority TEXT DEFAULT 'medium', category TEXT, assignee_id UUID, due_date DATE, project_id UUID, completed_at TIMESTAMPTZ, parent_task_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_tasks_tenant_business ON tasks (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_tasks_tenant_updated ON tasks (tenant_id, updated_at);

-- industry_module_data
CREATE TABLE IF NOT EXISTS industry_module_data (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  industry_type TEXT NOT NULL, module_key TEXT NOT NULL, payload JSONB DEFAULT '{}',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_industry_module_data_tenant_business ON industry_module_data (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_industry_module_data_tenant_updated ON industry_module_data (tenant_id, updated_at);

-- credit_accounts
CREATE TABLE IF NOT EXISTS credit_accounts (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID NOT NULL,
  account_number TEXT NOT NULL,
  customer_id TEXT,
  contact_id TEXT,
  patient_id TEXT,
  student_id TEXT,
  buyer_id TEXT,
  customer_name TEXT NOT NULL,
  customer_phone TEXT,
  customer_email TEXT,
  customer_address TEXT,
  credit_limit NUMERIC NOT NULL DEFAULT 0,
  current_balance NUMERIC NOT NULL DEFAULT 0,
  available_credit NUMERIC NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active',
  suspended_reason TEXT,
  payment_terms_days INTEGER DEFAULT 30,
  allow_override BOOLEAN DEFAULT false,
  total_purchased NUMERIC DEFAULT 0,
  total_paid NUMERIC DEFAULT 0,
  last_purchase_date DATE,
  last_payment_date DATE,
  overdue_amount NUMERIC DEFAULT 0,
  notes TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_credit_accounts_tenant_business ON credit_accounts (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_credit_accounts_updated ON credit_accounts (tenant_id, updated_at);
CREATE INDEX IF NOT EXISTS idx_credit_accounts_status ON credit_accounts (tenant_id, business_id, status);

-- credit_transactions
CREATE TABLE IF NOT EXISTS credit_transactions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID NOT NULL,
  credit_account_id TEXT NOT NULL,
  customer_name TEXT,
  type TEXT NOT NULL,
  amount NUMERIC NOT NULL,
  balance_before NUMERIC NOT NULL,
  balance_after NUMERIC NOT NULL,
  source TEXT NOT NULL,
  source_id TEXT,
  source_label TEXT,
  items TEXT,
  due_date DATE,
  is_paid BOOLEAN DEFAULT false,
  paid_at TIMESTAMPTZ,
  payment_method TEXT,
  payment_ref TEXT,
  finance_transaction_id TEXT,
  notes TEXT,
  created_by TEXT,
  meta JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_credit_transactions_tenant_business ON credit_transactions (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_credit_transactions_updated ON credit_transactions (tenant_id, updated_at);
CREATE INDEX IF NOT EXISTS idx_credit_transactions_account ON credit_transactions (credit_account_id);

-- livestock_production
CREATE TABLE IF NOT EXISTS livestock_production (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  tenant_id UUID NOT NULL,
  business_id UUID,
  production_id TEXT NOT NULL, livestock_id UUID, livestock_name TEXT, species TEXT, farm_id UUID, farm_name TEXT, production_type TEXT NOT NULL, date DATE NOT NULL, eggs_collected NUMERIC, eggs_broken NUMERIC, eggs_net NUMERIC, tray_count NUMERIC, morning_yield NUMERIC, evening_yield NUMERIC, total_yield NUMERIC, milk_quality TEXT, live_weight NUMERIC, dressed_weight NUMERIC, dressing_percent NUMERIC, cuts TEXT, product_type TEXT, quantity NUMERIC, unit TEXT, sale_price NUMERIC, total_revenue NUMERIC, buyer_id UUID, buyer_name TEXT, notes TEXT, sale_recorded BOOLEAN DEFAULT false, finance_transaction_id UUID, linked_inventory_product_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),
  deleted_at TIMESTAMPTZ DEFAULT NULL,
  version BIGINT NOT NULL DEFAULT 1
);

CREATE INDEX IF NOT EXISTS idx_livestock_production_tenant_business ON livestock_production (tenant_id, business_id);
CREATE INDEX IF NOT EXISTS idx_livestock_production_tenant_updated ON livestock_production (tenant_id, updated_at);

-- error_reports (centralized crash reporting from AmCEO desktop → amceo-admin)
-- Technical/diagnostic data only — no business content, names, or emails.
CREATE TABLE IF NOT EXISTS error_reports (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  error_signature TEXT NOT NULL,
  error_message TEXT NOT NULL,
  stack_trace TEXT,
  component_stack TEXT,
  app_version TEXT,
  os TEXT,
  os_version TEXT,
  machine_id_hash TEXT NOT NULL,
  deployment_mode TEXT,
  occurrence_count INTEGER NOT NULL DEFAULT 1,
  first_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  client_timestamp TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_error_reports_sig_machine
  ON error_reports(error_signature, machine_id_hash);

CREATE INDEX IF NOT EXISTS idx_error_reports_last_seen
  ON error_reports(last_seen_at DESC);

CREATE INDEX IF NOT EXISTS idx_error_reports_occurrence
  ON error_reports(occurrence_count DESC);

GRANT SELECT, INSERT, UPDATE ON public.error_reports TO service_role;

-- schema_versions
CREATE TABLE IF NOT EXISTS schema_versions (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  version TEXT NOT NULL UNIQUE,
  applied_at TIMESTAMPTZ DEFAULT NOW(),
  description TEXT
);

ALTER TABLE finance_accounts ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON finance_accounts;
CREATE POLICY "service_role_all" ON finance_accounts
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON finance_accounts;
CREATE POLICY "tenant_isolation" ON finance_accounts
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_transactions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON finance_transactions;
CREATE POLICY "service_role_all" ON finance_transactions
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON finance_transactions;
CREATE POLICY "tenant_isolation" ON finance_transactions
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_invoices ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON finance_invoices;
CREATE POLICY "service_role_all" ON finance_invoices
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON finance_invoices;
CREATE POLICY "tenant_isolation" ON finance_invoices
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_bills ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON finance_bills;
CREATE POLICY "service_role_all" ON finance_bills
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON finance_bills;
CREATE POLICY "tenant_isolation" ON finance_bills
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_journal_entries ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON finance_journal_entries;
CREATE POLICY "service_role_all" ON finance_journal_entries
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON finance_journal_entries;
CREATE POLICY "tenant_isolation" ON finance_journal_entries
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_budgets ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON finance_budgets;
CREATE POLICY "service_role_all" ON finance_budgets
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON finance_budgets;
CREATE POLICY "tenant_isolation" ON finance_budgets
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_payables ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON finance_payables;
CREATE POLICY "service_role_all" ON finance_payables
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON finance_payables;
CREATE POLICY "tenant_isolation" ON finance_payables
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_bank_accounts ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "service_role_all" ON finance_bank_accounts;
CREATE POLICY "service_role_all" ON finance_bank_accounts
  FOR ALL TO service_role USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "tenant_isolation" ON finance_bank_accounts;
CREATE POLICY "tenant_isolation" ON finance_bank_accounts
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_invoice_payments ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "service_role_all" ON finance_invoice_payments;
CREATE POLICY "service_role_all" ON finance_invoice_payments
  FOR ALL TO service_role USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "tenant_isolation" ON finance_invoice_payments;
CREATE POLICY "tenant_isolation" ON finance_invoice_payments
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_bill_payments ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "service_role_all" ON finance_bill_payments;
CREATE POLICY "service_role_all" ON finance_bill_payments
  FOR ALL TO service_role USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "tenant_isolation" ON finance_bill_payments;
CREATE POLICY "tenant_isolation" ON finance_bill_payments
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE finance_reconciliations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "service_role_all" ON finance_reconciliations;
CREATE POLICY "service_role_all" ON finance_reconciliations
  FOR ALL TO service_role USING (true) WITH CHECK (true);
DROP POLICY IF EXISTS "tenant_isolation" ON finance_reconciliations;
CREATE POLICY "tenant_isolation" ON finance_reconciliations
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE hr_employees ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON hr_employees;
CREATE POLICY "service_role_all" ON hr_employees
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON hr_employees;
CREATE POLICY "tenant_isolation" ON hr_employees
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

-- Salary: AmCEO desktop enforces view_salaries on display. Cloud Sync uses service_role
-- (bypasses RLS). Do not expose salary via public/anon APIs.

ALTER TABLE hr_payroll_runs ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON hr_payroll_runs;
CREATE POLICY "service_role_all" ON hr_payroll_runs
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON hr_payroll_runs;
CREATE POLICY "tenant_isolation" ON hr_payroll_runs
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE hr_leave_requests ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON hr_leave_requests;
CREATE POLICY "service_role_all" ON hr_leave_requests
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON hr_leave_requests;
CREATE POLICY "tenant_isolation" ON hr_leave_requests
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE hr_contracts ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON hr_contracts;
CREATE POLICY "service_role_all" ON hr_contracts
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON hr_contracts;
CREATE POLICY "tenant_isolation" ON hr_contracts
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE hr_contract_history ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON hr_contract_history;
CREATE POLICY "service_role_all" ON hr_contract_history
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON hr_contract_history;
CREATE POLICY "tenant_isolation" ON hr_contract_history
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE hr_staff_documents ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON hr_staff_documents;
CREATE POLICY "service_role_all" ON hr_staff_documents
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON hr_staff_documents;
CREATE POLICY "tenant_isolation" ON hr_staff_documents
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());


ALTER TABLE inventory_products ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON inventory_products;
CREATE POLICY "service_role_all" ON inventory_products
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON inventory_products;
CREATE POLICY "tenant_isolation" ON inventory_products
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE inventory_movements ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON inventory_movements;
CREATE POLICY "service_role_all" ON inventory_movements
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON inventory_movements;
CREATE POLICY "tenant_isolation" ON inventory_movements
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE scm_suppliers ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON scm_suppliers;
CREATE POLICY "service_role_all" ON scm_suppliers
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON scm_suppliers;
CREATE POLICY "tenant_isolation" ON scm_suppliers
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE scm_purchase_orders ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON scm_purchase_orders;
CREATE POLICY "service_role_all" ON scm_purchase_orders
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON scm_purchase_orders;
CREATE POLICY "tenant_isolation" ON scm_purchase_orders
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE scm_grns ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON scm_grns;
CREATE POLICY "service_role_all" ON scm_grns
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON scm_grns;
CREATE POLICY "tenant_isolation" ON scm_grns
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE scm_requisitions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON scm_requisitions;
CREATE POLICY "service_role_all" ON scm_requisitions
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON scm_requisitions;
CREATE POLICY "tenant_isolation" ON scm_requisitions
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE assets ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON assets;
CREATE POLICY "service_role_all" ON assets
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON assets;
CREATE POLICY "tenant_isolation" ON assets
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE crm_companies ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON crm_companies;
CREATE POLICY "service_role_all" ON crm_companies
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON crm_companies;
CREATE POLICY "tenant_isolation" ON crm_companies
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE crm_contacts ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON crm_contacts;
CREATE POLICY "service_role_all" ON crm_contacts
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON crm_contacts;
CREATE POLICY "tenant_isolation" ON crm_contacts
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE crm_deals ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON crm_deals;
CREATE POLICY "service_role_all" ON crm_deals
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON crm_deals;
CREATE POLICY "tenant_isolation" ON crm_deals
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE crm_activities ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON crm_activities;
CREATE POLICY "service_role_all" ON crm_activities
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON crm_activities;
CREATE POLICY "tenant_isolation" ON crm_activities
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE marketing_campaigns ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON marketing_campaigns;
CREATE POLICY "service_role_all" ON marketing_campaigns
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON marketing_campaigns;
CREATE POLICY "tenant_isolation" ON marketing_campaigns
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE marketing_segments ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON marketing_segments;
CREATE POLICY "service_role_all" ON marketing_segments
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON marketing_segments;
CREATE POLICY "tenant_isolation" ON marketing_segments
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE marketing_contacts ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON marketing_contacts;
CREATE POLICY "service_role_all" ON marketing_contacts
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON marketing_contacts;
CREATE POLICY "tenant_isolation" ON marketing_contacts
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE marketing_templates ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON marketing_templates;
CREATE POLICY "service_role_all" ON marketing_templates
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON marketing_templates;
CREATE POLICY "tenant_isolation" ON marketing_templates
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE helpdesk_tickets ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON helpdesk_tickets;
CREATE POLICY "service_role_all" ON helpdesk_tickets
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON helpdesk_tickets;
CREATE POLICY "tenant_isolation" ON helpdesk_tickets
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE helpdesk_comments ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON helpdesk_comments;
CREATE POLICY "service_role_all" ON helpdesk_comments
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON helpdesk_comments;
CREATE POLICY "tenant_isolation" ON helpdesk_comments
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE pos_sessions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON pos_sessions;
CREATE POLICY "service_role_all" ON pos_sessions
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON pos_sessions;
CREATE POLICY "tenant_isolation" ON pos_sessions
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE pos_products ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON pos_products;
CREATE POLICY "service_role_all" ON pos_products
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON pos_products;
CREATE POLICY "tenant_isolation" ON pos_products
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE pos_transactions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON pos_transactions;
CREATE POLICY "service_role_all" ON pos_transactions
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON pos_transactions;
CREATE POLICY "tenant_isolation" ON pos_transactions
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE task_projects ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON task_projects;
CREATE POLICY "service_role_all" ON task_projects
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON task_projects;
CREATE POLICY "tenant_isolation" ON task_projects
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON tasks;
CREATE POLICY "service_role_all" ON tasks
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON tasks;
CREATE POLICY "tenant_isolation" ON tasks
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE industry_module_data ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON industry_module_data;
CREATE POLICY "service_role_all" ON industry_module_data
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON industry_module_data;
CREATE POLICY "tenant_isolation" ON industry_module_data
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE credit_accounts ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON credit_accounts;
DROP POLICY IF EXISTS "tenant_isolation" ON credit_accounts;

CREATE POLICY "service_role_all" ON credit_accounts
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

CREATE POLICY "tenant_isolation" ON credit_accounts
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE credit_transactions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON credit_transactions;
DROP POLICY IF EXISTS "tenant_isolation" ON credit_transactions;

CREATE POLICY "service_role_all" ON credit_transactions
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

CREATE POLICY "tenant_isolation" ON credit_transactions
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE livestock_production ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON livestock_production;
CREATE POLICY "service_role_all" ON livestock_production
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "tenant_isolation" ON livestock_production;
CREATE POLICY "tenant_isolation" ON livestock_production
  FOR ALL TO authenticated
  USING (tenant_id = auth.uid())
  WITH CHECK (tenant_id = auth.uid());

ALTER TABLE schema_versions ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "service_role_all" ON schema_versions;
CREATE POLICY "service_role_all" ON schema_versions
  FOR ALL TO service_role USING (true)
  WITH CHECK (true);

DROP POLICY IF EXISTS "authenticated_read" ON schema_versions;
CREATE POLICY "authenticated_read" ON schema_versions
  FOR SELECT TO authenticated USING (true);

DROP TRIGGER IF EXISTS set_finance_accounts_updated_at ON finance_accounts;
CREATE TRIGGER set_finance_accounts_updated_at
  BEFORE UPDATE ON finance_accounts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_transactions_updated_at ON finance_transactions;
CREATE TRIGGER set_finance_transactions_updated_at
  BEFORE UPDATE ON finance_transactions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_invoices_updated_at ON finance_invoices;
CREATE TRIGGER set_finance_invoices_updated_at
  BEFORE UPDATE ON finance_invoices
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_bills_updated_at ON finance_bills;
CREATE TRIGGER set_finance_bills_updated_at
  BEFORE UPDATE ON finance_bills
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_journal_entries_updated_at ON finance_journal_entries;
CREATE TRIGGER set_finance_journal_entries_updated_at
  BEFORE UPDATE ON finance_journal_entries
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_budgets_updated_at ON finance_budgets;
CREATE TRIGGER set_finance_budgets_updated_at
  BEFORE UPDATE ON finance_budgets
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_payables_updated_at ON finance_payables;
CREATE TRIGGER set_finance_payables_updated_at
  BEFORE UPDATE ON finance_payables
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_bank_accounts_updated_at ON finance_bank_accounts;
CREATE TRIGGER set_finance_bank_accounts_updated_at
  BEFORE UPDATE ON finance_bank_accounts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_invoice_payments_updated_at ON finance_invoice_payments;
CREATE TRIGGER set_finance_invoice_payments_updated_at
  BEFORE UPDATE ON finance_invoice_payments
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_bill_payments_updated_at ON finance_bill_payments;
CREATE TRIGGER set_finance_bill_payments_updated_at
  BEFORE UPDATE ON finance_bill_payments
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_finance_reconciliations_updated_at ON finance_reconciliations;
CREATE TRIGGER set_finance_reconciliations_updated_at
  BEFORE UPDATE ON finance_reconciliations
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_hr_employees_updated_at ON hr_employees;
CREATE TRIGGER set_hr_employees_updated_at
  BEFORE UPDATE ON hr_employees
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_hr_payroll_runs_updated_at ON hr_payroll_runs;
CREATE TRIGGER set_hr_payroll_runs_updated_at
  BEFORE UPDATE ON hr_payroll_runs
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_hr_leave_requests_updated_at ON hr_leave_requests;
CREATE TRIGGER set_hr_leave_requests_updated_at
  BEFORE UPDATE ON hr_leave_requests
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_hr_contracts_updated_at ON hr_contracts;
CREATE TRIGGER set_hr_contracts_updated_at
  BEFORE UPDATE ON hr_contracts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_hr_contract_history_updated_at ON hr_contract_history;
CREATE TRIGGER set_hr_contract_history_updated_at
  BEFORE UPDATE ON hr_contract_history
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_hr_staff_documents_updated_at ON hr_staff_documents;
CREATE TRIGGER set_hr_staff_documents_updated_at
  BEFORE UPDATE ON hr_staff_documents
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();


DROP TRIGGER IF EXISTS set_inventory_products_updated_at ON inventory_products;
CREATE TRIGGER set_inventory_products_updated_at
  BEFORE UPDATE ON inventory_products
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_inventory_movements_updated_at ON inventory_movements;
CREATE TRIGGER set_inventory_movements_updated_at
  BEFORE UPDATE ON inventory_movements
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_scm_suppliers_updated_at ON scm_suppliers;
CREATE TRIGGER set_scm_suppliers_updated_at
  BEFORE UPDATE ON scm_suppliers
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_scm_purchase_orders_updated_at ON scm_purchase_orders;
CREATE TRIGGER set_scm_purchase_orders_updated_at
  BEFORE UPDATE ON scm_purchase_orders
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_scm_grns_updated_at ON scm_grns;
CREATE TRIGGER set_scm_grns_updated_at
  BEFORE UPDATE ON scm_grns
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_scm_requisitions_updated_at ON scm_requisitions;
CREATE TRIGGER set_scm_requisitions_updated_at
  BEFORE UPDATE ON scm_requisitions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_assets_updated_at ON assets;
CREATE TRIGGER set_assets_updated_at
  BEFORE UPDATE ON assets
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_crm_companies_updated_at ON crm_companies;
CREATE TRIGGER set_crm_companies_updated_at
  BEFORE UPDATE ON crm_companies
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_crm_contacts_updated_at ON crm_contacts;
CREATE TRIGGER set_crm_contacts_updated_at
  BEFORE UPDATE ON crm_contacts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_crm_deals_updated_at ON crm_deals;
CREATE TRIGGER set_crm_deals_updated_at
  BEFORE UPDATE ON crm_deals
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_crm_activities_updated_at ON crm_activities;
CREATE TRIGGER set_crm_activities_updated_at
  BEFORE UPDATE ON crm_activities
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_marketing_campaigns_updated_at ON marketing_campaigns;
CREATE TRIGGER set_marketing_campaigns_updated_at
  BEFORE UPDATE ON marketing_campaigns
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_marketing_segments_updated_at ON marketing_segments;
CREATE TRIGGER set_marketing_segments_updated_at
  BEFORE UPDATE ON marketing_segments
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_marketing_contacts_updated_at ON marketing_contacts;
CREATE TRIGGER set_marketing_contacts_updated_at
  BEFORE UPDATE ON marketing_contacts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_marketing_templates_updated_at ON marketing_templates;
CREATE TRIGGER set_marketing_templates_updated_at
  BEFORE UPDATE ON marketing_templates
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_helpdesk_tickets_updated_at ON helpdesk_tickets;
CREATE TRIGGER set_helpdesk_tickets_updated_at
  BEFORE UPDATE ON helpdesk_tickets
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_helpdesk_comments_updated_at ON helpdesk_comments;
CREATE TRIGGER set_helpdesk_comments_updated_at
  BEFORE UPDATE ON helpdesk_comments
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_pos_sessions_updated_at ON pos_sessions;
CREATE TRIGGER set_pos_sessions_updated_at
  BEFORE UPDATE ON pos_sessions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_pos_products_updated_at ON pos_products;
CREATE TRIGGER set_pos_products_updated_at
  BEFORE UPDATE ON pos_products
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_pos_transactions_updated_at ON pos_transactions;
CREATE TRIGGER set_pos_transactions_updated_at
  BEFORE UPDATE ON pos_transactions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_task_projects_updated_at ON task_projects;
CREATE TRIGGER set_task_projects_updated_at
  BEFORE UPDATE ON task_projects
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_tasks_updated_at ON tasks;
CREATE TRIGGER set_tasks_updated_at
  BEFORE UPDATE ON tasks
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_industry_module_data_updated_at ON industry_module_data;
CREATE TRIGGER set_industry_module_data_updated_at
  BEFORE UPDATE ON industry_module_data
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_credit_accounts_updated_at ON credit_accounts;
CREATE TRIGGER set_credit_accounts_updated_at
  BEFORE UPDATE ON credit_accounts
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_credit_transactions_updated_at ON credit_transactions;
CREATE TRIGGER set_credit_transactions_updated_at
  BEFORE UPDATE ON credit_transactions
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

DROP TRIGGER IF EXISTS set_livestock_production_updated_at ON livestock_production;
CREATE TRIGGER set_livestock_production_updated_at
  BEFORE UPDATE ON livestock_production
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

INSERT INTO schema_versions (version, description)
VALUES ('1.0.21',
  'Industry modules — Education, Healthcare, Agriculture, Construction, Logistics, Hospitality, Manufacturing, NGO, Digital, Retail, Group Tools with industry_module_data sync table')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.22',
  'Industry form audit — expanded JSON payloads: healthcare (NHIS, vitals, prescriptions, lab rows), construction (requisition workflow, IPC status), manufacturing (BOM stock, production dates), NGO (program activities), logistics (trip expenses, vehicle/driver fields), retail promotions, digital subscriptions, hospitality order lines')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.23',
  'Agriculture livestock_production table; harvest form fields (storage, moisture, notes); manufacturing→inventory sync; optional agriculture inventory prompts')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.24',
  'Inventory product purpose (sales | operational); POS filters to sales-only; Finance payables duplicate FinanceLayout removed')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.25',
  'Add credit_accounts and credit_transactions tables for full credit sales management across all modules and POS')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.26',
  'Add source and source_type columns to finance_transactions for opening balance and transaction origin tracking')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.27',
  'Added cost price, selling price, margin %, and markup % fields to inventory products; unified Manufacturing finished goods pricing with shared model')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.28',
  'Added error_reports table for centralized crash reporting from AmCEO desktop clients.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.29',
  'CRM Cloud Sync: meta JSONB on crm_* tables for local-only fields (country, taxNumber, address, wonAt); module sync registry wiring.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.30',
  'BUGFIX Cloud Sync LWW: update_updated_at() previously always set updated_at=NOW() on UPDATE, so a stale upsert could overwrite a newer peer edit after conflict merge. Fix: preserve NEW.updated_at when it is strictly newer than OLD.updated_at; otherwise stamp NOW(). (App also stores meta.clientUpdatedAt as a belt-and-suspenders LWW clock.)')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.31',
  'Inventory Cloud Sync (hybrid): meta JSONB on inventory_products + inventory_movements for clientUpdatedAt / local-only fields. Product metadata uses LWW; stock movements are append-only and applied as deltas so concurrent offline stock changes are not lost. stockByWarehouse remains local-only this pass.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.32',
  'Finance Cloud Sync F0/F1: meta JSONB on finance_* tables; COA columns on finance_accounts (category, normal_balance, is_system, is_active, parent_code); journal status/voided_at/posted_at; finance_bank_accounts (last-4 only); finance_invoice_payments + finance_bill_payments (append-only, sync in F3). F1 wires LWW sync for COA + budgets. FOLLOW-UP: mirror into amceo-admin dual-schema.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.33',
  'Finance Cloud Sync F4: finance_reconciliations append-only completed history. Bank accounts LWW sync uses existing finance_bank_accounts (last-4 only). In-progress wizard state stays local/React-only. FOLLOW-UP: mirror into amceo-admin dual-schema.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.34',
  'Finance Cloud Sync F5: meta JSONB on credit_accounts + credit_transactions for clientUpdatedAt / local-only fields. Legacy finance_transactions sync is LWW read-only mirror (does not post journals). FOLLOW-UP: mirror into amceo-admin dual-schema.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.35',
  'HR Cloud Sync H0/H1: meta JSONB on hr_employees/hr_payroll_runs/hr_leave_requests/hr_contracts; bank_account_last4 + verification_token on hr_employees. Salary syncs full (app view_salaries). TIN/SSNIT omitted from cloud. Employee metadata LWW via meta.clientUpdatedAt. FOLLOW-UP: mirror into amceo-admin dual-schema.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.36',
  'HR Cloud Sync H2: leave requests LWW + tombstones; contracts LWW (bank last-4, TIN/SSNIT omitted); hr_contract_history append-only upsert-by-id. FOLLOW-UP: mirror into amceo-admin dual-schema.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.37',
  'HR Cloud Sync H3: hr_payroll_runs append-only upsert-by-id (ProcessedPayrollRun envelopes with per-employee gross/net/PAYE/SSNIT). Never LWW-replace. Payslips local-only/regenerable. Finance journals for payroll remain F2-only (no double-post on HR pull). Salary amounts sync full; UI view_salaries gates display. FOLLOW-UP: mirror into amceo-admin dual-schema.')
ON CONFLICT (version) DO NOTHING;


-- H5b: private Storage bucket for staff documents (path: tenant/business/employee/doc)
INSERT INTO storage.buckets (id, name, public, file_size_limit, allowed_mime_types)
VALUES (
  'amceo-hr-documents',
  'amceo-hr-documents',
  false,
  8388608,
  ARRAY['application/pdf', 'image/jpeg', 'image/png']::text[]
)
ON CONFLICT (id) DO UPDATE SET
  public = EXCLUDED.public,
  file_size_limit = EXCLUDED.file_size_limit,
  allowed_mime_types = EXCLUDED.allowed_mime_types;

DROP POLICY IF EXISTS "amceo_hr_docs_auth_select" ON storage.objects;
DROP POLICY IF EXISTS "amceo_hr_docs_auth_insert" ON storage.objects;
DROP POLICY IF EXISTS "amceo_hr_docs_auth_update" ON storage.objects;
DROP POLICY IF EXISTS "amceo_hr_docs_auth_delete" ON storage.objects;

CREATE POLICY "amceo_hr_docs_auth_select" ON storage.objects
  FOR SELECT TO authenticated
  USING (
    bucket_id = 'amceo-hr-documents'
    AND (storage.foldername(name))[1] = auth.uid()::text
  );

CREATE POLICY "amceo_hr_docs_auth_insert" ON storage.objects
  FOR INSERT TO authenticated
  WITH CHECK (
    bucket_id = 'amceo-hr-documents'
    AND (storage.foldername(name))[1] = auth.uid()::text
  );

CREATE POLICY "amceo_hr_docs_auth_update" ON storage.objects
  FOR UPDATE TO authenticated
  USING (
    bucket_id = 'amceo-hr-documents'
    AND (storage.foldername(name))[1] = auth.uid()::text
  );

CREATE POLICY "amceo_hr_docs_auth_delete" ON storage.objects
  FOR DELETE TO authenticated
  USING (
    bucket_id = 'amceo-hr-documents'
    AND (storage.foldername(name))[1] = auth.uid()::text
  );


INSERT INTO schema_versions (version, description)
VALUES ('1.0.38',
  'HR Cloud Sync H5b: hr_staff_documents metadata + private Storage bucket amceo-hr-documents (lazy blob download). Tombstones delete Storage objects. App ACL OWNER/HR_MANAGER; service_role used for sync. FOLLOW-UP: mirror into amceo-admin dual-schema.')
ON CONFLICT (version) DO NOTHING;

INSERT INTO schema_versions (version, description)
VALUES ('1.0.39',
  'POS Cloud Sync P0/P1: meta JSONB on pos_sessions + pos_transactions. Sync sessions + receipt transactions only (no pos_products, no cart drafts). Pull must not re-apply Inventory/Finance side effects. Closed session + voided/refunded status are terminal. Z-reports stay derived. FOLLOW-UP: mirror into amceo-admin dual-schema.')
ON CONFLICT (version) DO NOTHING;