-- cPanel deployment schema for murad.palai.ps
-- Generated on 2026-05-17

-- PostgreSQL database and user setup (optional if already created in cPanel)
CREATE DATABASE palai_murad;
CREATE USER palai_murad WITH PASSWORD 'vgzBx{lQIqOCCxY7';
GRANT ALL PRIVILEGES ON DATABASE palai_murad TO palai_murad;
\connect palai_murad

BEGIN;

-- ===== Migration: 001_init.sql =====

CREATE TABLE IF NOT EXISTS schema_migrations (
  id TEXT PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS settings (
  key TEXT PRIMARY KEY,
  value JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS customers (
  id TEXT PRIMARY KEY,
  customer_number TEXT,
  name TEXT NOT NULL,
  phone TEXT,
  address TEXT,
  category TEXT,
  credit_limit NUMERIC(18,4) DEFAULT 0,
  balance NUMERIC(18,4) DEFAULT 0,
  allowed_discount NUMERIC(18,4) DEFAULT 0,
  sales_rep_id TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS suppliers (
  id TEXT PRIMARY KEY,
  supplier_number TEXT,
  name TEXT NOT NULL,
  phone TEXT,
  address TEXT,
  payment_terms INTEGER,
  tax_number TEXT,
  account_id TEXT,
  account_code TEXT,
  balance NUMERIC(18,4) DEFAULT 0,
  inactive BOOLEAN DEFAULT FALSE,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS materials (
  id TEXT PRIMARY KEY,
  item_number TEXT,
  name TEXT NOT NULL,
  barcode TEXT,
  sale_price NUMERIC(18,4) DEFAULT 0,
  purchase_price NUMERIC(18,4) DEFAULT 0,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS invoices (
  id TEXT PRIMARY KEY,
  invoice_number TEXT NOT NULL,
  customer_id TEXT,
  customer_name TEXT,
  invoice_date DATE,
  currency_code TEXT,
  grand_total NUMERIC(18,4) DEFAULT 0,
  amount_due NUMERIC(18,4) DEFAULT 0,
  status TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS purchase_orders (
  id TEXT PRIMARY KEY,
  order_number TEXT NOT NULL,
  supplier_id TEXT,
  supplier_name TEXT,
  order_date DATE,
  currency_code TEXT,
  base_currency_code TEXT,
  grand_total NUMERIC(18,4) DEFAULT 0,
  status TEXT,
  posting_status TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS data_json_store (
  filename TEXT PRIMARY KEY,
  payload JSONB NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_invoices_invoice_number ON invoices (invoice_number);
CREATE INDEX IF NOT EXISTS idx_invoices_invoice_date ON invoices (invoice_date DESC);
CREATE INDEX IF NOT EXISTS idx_invoices_customer_name ON invoices (customer_name);

CREATE INDEX IF NOT EXISTS idx_purchase_orders_order_number ON purchase_orders (order_number);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_order_date ON purchase_orders (order_date DESC);
CREATE INDEX IF NOT EXISTS idx_purchase_orders_supplier_name ON purchase_orders (supplier_name);

CREATE INDEX IF NOT EXISTS idx_customers_name ON customers (name);
CREATE INDEX IF NOT EXISTS idx_suppliers_name ON suppliers (name);
CREATE INDEX IF NOT EXISTS idx_materials_name ON materials (name);


-- ===== Migration: 002_master_data_lists.sql =====

CREATE TABLE IF NOT EXISTS expenses (
  id TEXT PRIMARY KEY,
  item_number TEXT,
  name TEXT NOT NULL,
  sale_price NUMERIC(18,4) DEFAULT 0,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS product_shape_definitions (
  id TEXT PRIMARY KEY,
  item_number TEXT,
  name TEXT NOT NULL,
  sale_price NUMERIC(18,4) DEFAULT 0,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sales_reps (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT,
  region TEXT,
  commission_rate NUMERIC(18,4) DEFAULT 0,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS managed_units (
  id TEXT PRIMARY KEY,
  unit_number TEXT,
  description TEXT NOT NULL,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_expenses_name ON expenses (name);
CREATE INDEX IF NOT EXISTS idx_product_shapes_name ON product_shape_definitions (name);
CREATE INDEX IF NOT EXISTS idx_sales_reps_name ON sales_reps (name);
CREATE INDEX IF NOT EXISTS idx_managed_units_description ON managed_units (description);


-- ===== Migration: 003_item_groups_warehouses.sql =====

CREATE TABLE IF NOT EXISTS item_groups (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  is_pos_enabled BOOLEAN DEFAULT FALSE,
  uses_default_tax BOOLEAN DEFAULT TRUE,
  tax_rate NUMERIC(18,4),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS warehouses (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  shelves JSONB NOT NULL DEFAULT '[]'::jsonb,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_item_groups_name ON item_groups (name);
CREATE INDEX IF NOT EXISTS idx_warehouses_name ON warehouses (name);


-- ===== Migration: 004_banks_customer_pricing.sql =====

CREATE TABLE IF NOT EXISTS banks (
  id TEXT PRIMARY KEY,
  code TEXT NOT NULL,
  name TEXT NOT NULL,
  name_en TEXT,
  account_id TEXT,
  account_code TEXT,
  bank_number TEXT,
  full_name TEXT,
  branch TEXT,
  iban TEXT,
  currency_code TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS customer_pricing (
  category TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_banks_code ON banks (code);
CREATE INDEX IF NOT EXISTS idx_banks_name ON banks (name);


-- ===== Migration: 005_item_units.sql =====

-- Wave 5: Item Units Tracking
CREATE TABLE IF NOT EXISTS item_units (
  id TEXT PRIMARY KEY,
  item_id TEXT NOT NULL,
  rfid_code TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  invoice_id TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_item_units_item_id ON item_units (item_id);
CREATE INDEX IF NOT EXISTS idx_item_units_status ON item_units (status);
CREATE INDEX IF NOT EXISTS idx_item_units_invoice_id ON item_units (invoice_id);


-- ===== Migration: 006_internal_transfers.sql =====

-- Wave 6: Internal Transfers
CREATE TABLE IF NOT EXISTS internal_transfers (
  id TEXT PRIMARY KEY,
  transfer_number TEXT NOT NULL UNIQUE,
  material_id TEXT NOT NULL,
  quantity NUMERIC(12, 2) NOT NULL,
  from_warehouse_id TEXT NOT NULL,
  from_shelf_id TEXT,
  to_warehouse_id TEXT NOT NULL,
  to_shelf_id TEXT,
  transfer_date TIMESTAMPTZ NOT NULL,
  created_by TEXT NOT NULL,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_internal_transfers_material ON internal_transfers (material_id);
CREATE INDEX IF NOT EXISTS idx_internal_transfers_from_wh ON internal_transfers (from_warehouse_id);
CREATE INDEX IF NOT EXISTS idx_internal_transfers_to_wh ON internal_transfers (to_warehouse_id);
CREATE INDEX IF NOT EXISTS idx_internal_transfers_number ON internal_transfers (transfer_number);


-- ===== Migration: 007_inventory_movements.sql =====

-- Wave 7: Inventory Movements (Transaction Log - Immutable)
CREATE TABLE IF NOT EXISTS inventory_movements (
  id TEXT PRIMARY KEY,
  material_id TEXT NOT NULL,
  warehouse_id TEXT NOT NULL,
  shelf_id TEXT,
  quantity_in NUMERIC(12, 2) NOT NULL DEFAULT 0,
  quantity_out NUMERIC(12, 2) NOT NULL DEFAULT 0,
  reference_type TEXT NOT NULL,
  reference_id TEXT,
  shipment_id TEXT,
  invoice_id TEXT,
  movement_date TIMESTAMPTZ NOT NULL,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_inventory_movements_material ON inventory_movements (material_id);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_warehouse ON inventory_movements (warehouse_id);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_date ON inventory_movements (movement_date);
CREATE INDEX IF NOT EXISTS idx_inventory_movements_ref_type ON inventory_movements (reference_type);


-- ===== Migration: 008_customer_categories.sql =====

-- Wave 8: Customer Categories
CREATE TABLE IF NOT EXISTS customer_categories (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  name_en TEXT,
  discount_percent NUMERIC(5, 2) DEFAULT 0,
  description TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_customer_categories_name ON customer_categories (name);


-- ===== Migration: 009_parties.sql =====

-- Wave 9: Parties (Internal Reference - Suppliers/Customers linked entities)
CREATE TABLE IF NOT EXISTS parties (
  id TEXT PRIMARY KEY,
  party_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  party_name TEXT NOT NULL,
  party_code TEXT,
  contact_info TEXT,
  address TEXT,
  notes TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_parties_type ON parties (party_type);
CREATE INDEX IF NOT EXISTS idx_parties_entity_id ON parties (entity_id);
CREATE INDEX IF NOT EXISTS idx_parties_code ON parties (party_code);


-- ===== Migration: 010_remaining_legacy_entities.sql =====

-- Wave 10: Remaining legacy JSON entities migrated to PostgreSQL

CREATE TABLE IF NOT EXISTS chart_of_accounts (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS checks_issued (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS checks_received (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS currencies (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS daily_exchange_rates (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS employees (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS exchange_rate_sources (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS internal_transfer_docs (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS journal_entries (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS payments (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS production_reports (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS purchase_credit_notes (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS purchase_returns (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS shift_overrides (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS shipment_sequences (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS shipments (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS supplier_payment_locks (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS supplier_payments (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS transfer_locks (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sequence_counters (
  id TEXT PRIMARY KEY,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_chart_of_accounts_updated_at ON chart_of_accounts (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_checks_issued_updated_at ON checks_issued (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_checks_received_updated_at ON checks_received (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_currencies_updated_at ON currencies (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_daily_exchange_rates_updated_at ON daily_exchange_rates (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_employees_updated_at ON employees (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_exchange_rate_sources_updated_at ON exchange_rate_sources (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_internal_transfer_docs_updated_at ON internal_transfer_docs (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_journal_entries_updated_at ON journal_entries (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_payments_updated_at ON payments (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_production_reports_updated_at ON production_reports (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_purchase_credit_notes_updated_at ON purchase_credit_notes (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_purchase_returns_updated_at ON purchase_returns (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_shift_overrides_updated_at ON shift_overrides (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_shipment_sequences_updated_at ON shipment_sequences (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_shipments_updated_at ON shipments (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_supplier_payment_locks_updated_at ON supplier_payment_locks (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_supplier_payments_updated_at ON supplier_payments (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_transfer_locks_updated_at ON transfer_locks (updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_sequence_counters_updated_at ON sequence_counters (updated_at DESC);


-- ===== Migration: 011_banks_unique_code.sql =====

-- Enforce unique bank codes (case-insensitive) to avoid concurrent duplicate inserts.
CREATE UNIQUE INDEX IF NOT EXISTS ux_banks_code_ci
ON banks (LOWER(code));


-- ===== Migration: 012_item_groups_group_number_unique.sql =====

-- Ensure auto-generated item group numbers stay unique when multiple users create rows concurrently.
CREATE UNIQUE INDEX IF NOT EXISTS ux_item_groups_group_number
ON item_groups ((NULLIF(TRIM(raw->>'groupNumber'), '')))
WHERE NULLIF(TRIM(raw->>'groupNumber'), '') IS NOT NULL;


-- ===== Migration: 013_warehouses_unique_refs.sql =====

-- Ensure warehouse names and generated references stay unique in PostgreSQL.
CREATE UNIQUE INDEX IF NOT EXISTS ux_warehouses_name_ci
ON warehouses (LOWER(name));

CREATE UNIQUE INDEX IF NOT EXISTS ux_warehouses_number
ON warehouses ((NULLIF(TRIM(raw->>'warehouseNumber'), '')))
WHERE NULLIF(TRIM(raw->>'warehouseNumber'), '') IS NOT NULL;


-- ===== Migration: 014_product_shapes_unique_name.sql =====

-- Enforce case-insensitive uniqueness on product shape names
CREATE UNIQUE INDEX IF NOT EXISTS ux_product_shapes_name_ci
  ON product_shape_definitions (LOWER(name));

-- Add unique index on item_number (shape reference) where not null/empty
CREATE UNIQUE INDEX IF NOT EXISTS ux_product_shapes_item_number
  ON product_shape_definitions ((NULLIF(TRIM(item_number), '')))
  WHERE NULLIF(TRIM(item_number), '') IS NOT NULL;


-- ===== Migration: 015_managed_units_unique_constraints.sql =====

-- Enforce uniqueness for managed units and protect concurrent inserts.
CREATE UNIQUE INDEX IF NOT EXISTS ux_managed_units_unit_number
  ON managed_units ((NULLIF(TRIM(unit_number), '')))
  WHERE NULLIF(TRIM(unit_number), '') IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS ux_managed_units_description_ci
  ON managed_units (LOWER(TRIM(description)));


-- ===== Migration: 016_customer_categories_unique_constraints.sql =====

-- Add sequential customer category reference and enforce uniqueness.
ALTER TABLE customer_categories
  ADD COLUMN IF NOT EXISTS category_number TEXT;

WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
  FROM customer_categories
  WHERE NULLIF(TRIM(COALESCE(category_number, '')), '') IS NULL
)
UPDATE customer_categories cc
SET category_number = 'CAT-' || LPAD(ranked.rn::text, 4, '0')
FROM ranked
WHERE cc.id = ranked.id;

CREATE UNIQUE INDEX IF NOT EXISTS ux_customer_categories_category_number
  ON customer_categories ((NULLIF(TRIM(category_number), '')))
  WHERE NULLIF(TRIM(category_number), '') IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS ux_customer_categories_name_ci
  ON customer_categories (LOWER(TRIM(name)));


-- ===== Migration: 017_currencies_unique_reference.sql =====

-- Add sequential reference for currencies and enforce uniqueness for code/reference.

-- Normalize currency code casing before applying uniqueness.
UPDATE currencies
SET raw = jsonb_set(raw, '{code}', to_jsonb(UPPER(TRIM(COALESCE(raw->>'code', '')))), true),
    updated_at = NOW()
WHERE NULLIF(TRIM(COALESCE(raw->>'code', '')), '') IS NOT NULL
  AND raw->>'code' <> UPPER(TRIM(COALESCE(raw->>'code', '')));

-- Backfill missing currencyNumber values in deterministic order.
WITH ranked AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY created_at, id) AS rn
  FROM currencies
  WHERE NULLIF(TRIM(COALESCE(raw->>'currencyNumber', '')), '') IS NULL
)
UPDATE currencies c
SET raw = jsonb_set(
      c.raw,
      '{currencyNumber}',
      to_jsonb('CUR-' || LPAD(ranked.rn::text, 4, '0')),
      true
    ),
    updated_at = NOW()
FROM ranked
WHERE c.id = ranked.id;

CREATE UNIQUE INDEX IF NOT EXISTS ux_currencies_code_ci
  ON currencies (LOWER(TRIM(COALESCE(raw->>'code', ''))))
  WHERE NULLIF(TRIM(COALESCE(raw->>'code', '')), '') IS NOT NULL;

CREATE UNIQUE INDEX IF NOT EXISTS ux_currencies_reference
  ON currencies (TRIM(COALESCE(raw->>'currencyNumber', '')))
  WHERE NULLIF(TRIM(COALESCE(raw->>'currencyNumber', '')), '') IS NOT NULL;


-- ===== Migration: 018_warehouses_name_branch_unique.sql =====

-- Warehouse name should be unique within the same branch, not globally.
DROP INDEX IF EXISTS ux_warehouses_name_ci;

CREATE UNIQUE INDEX IF NOT EXISTS ux_warehouses_name_branch_ci
ON warehouses (
  LOWER(name),
  LOWER(COALESCE(NULLIF(TRIM(raw->>'branchName'), ''), ''))
);


-- ===== Migration: 019_document_locks.sql =====

-- Create document_locks table for preventing concurrent edits
CREATE TABLE IF NOT EXISTS document_locks (
  id TEXT PRIMARY KEY,
  document_type TEXT NOT NULL,
  document_id TEXT NOT NULL,
  user_id TEXT NOT NULL,
  user_name TEXT NOT NULL,
  locked_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  expires_at TIMESTAMPTZ NOT NULL DEFAULT (NOW() + INTERVAL '30 minutes'),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE(document_type, document_id)
);

-- Create index for efficient queries
CREATE INDEX IF NOT EXISTS idx_document_locks_expires_at ON document_locks(expires_at);
CREATE INDEX IF NOT EXISTS idx_document_locks_user_id ON document_locks(user_id);
CREATE INDEX IF NOT EXISTS idx_document_locks_document_type_id ON document_locks(document_type, document_id);

-- Upsert showEditingMessageToUser setting
INSERT INTO settings (key, value, updated_at)
VALUES ('showEditingMessageToUser', 'false', NOW())
ON CONFLICT (key)
DO UPDATE SET value = EXCLUDED.value, updated_at = NOW();


-- ===== Migration: 020_setup_flag.sql =====

-- Setup flag and license storage in settings table
-- Uses the existing key-value settings table

INSERT INTO settings (key, value, updated_at)
VALUES ('setup_complete', 'false'::jsonb, NOW())
ON CONFLICT (key) DO NOTHING;

INSERT INTO settings (key, value, updated_at)
VALUES ('license', 'null'::jsonb, NOW())
ON CONFLICT (key) DO NOTHING;

INSERT INTO settings (key, value, updated_at)
VALUES ('eula_acceptance', 'null'::jsonb, NOW())
ON CONFLICT (key) DO NOTHING;


-- ===== Migration: 025_bank_reconciliation_periods.sql =====

-- Wave 11: Bank Reconciliation Foundation
-- Bank Reconciliation Periods Table

CREATE TABLE IF NOT EXISTS bank_reconciliation_periods (
  id TEXT PRIMARY KEY,
  bank_account_id TEXT NOT NULL,
  period_start_date DATE NOT NULL,
  period_end_date DATE NOT NULL,
  statement_start_balance NUMERIC(19, 2),
  statement_end_balance NUMERIC(19, 2) NOT NULL,
  ledger_start_balance NUMERIC(19, 2),
  ledger_end_balance NUMERIC(19, 2),
  status TEXT NOT NULL DEFAULT 'draft'
    CHECK (status IN ('draft', 'in-progress', 'completed', 'locked')),
  locked_at TIMESTAMPTZ,
  locked_by TEXT,
  completed_at TIMESTAMPTZ,
  completed_by TEXT,
  reconciliation_difference NUMERIC(19, 4),
  approved_by TEXT,
  approved_at TIMESTAMPTZ,
  notes TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  created_by TEXT NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_recon_bank FOREIGN KEY (bank_account_id) REFERENCES banks(id),
  CONSTRAINT check_period_dates CHECK (period_end_date >= period_start_date),
  UNIQUE(bank_account_id, period_start_date, period_end_date)
);

CREATE INDEX IF NOT EXISTS idx_recon_periods_bank ON bank_reconciliation_periods(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_recon_periods_status ON bank_reconciliation_periods(status);
CREATE INDEX IF NOT EXISTS idx_recon_periods_date ON bank_reconciliation_periods(period_start_date, period_end_date);
CREATE INDEX IF NOT EXISTS idx_recon_periods_created ON bank_reconciliation_periods(created_at DESC);


-- ===== Migration: 026_bank_transactions.sql =====

-- Wave 11: Bank Reconciliation - Bank Transactions
-- Bank Statement Transactions Table

CREATE TABLE IF NOT EXISTS bank_transactions (
  id TEXT PRIMARY KEY,
  reconciliation_period_id TEXT NOT NULL,
  bank_account_id TEXT NOT NULL,
  transaction_date DATE NOT NULL,
  amount NUMERIC(19, 2) NOT NULL,
  direction TEXT NOT NULL
    CHECK (direction IN ('inward', 'outward', 'debit', 'credit')),
  description TEXT,
  bank_reference TEXT,
  type TEXT
    CHECK (type IN ('check', 'transfer', 'fee', 'interest', 'charge', 'other')),
  currency_code TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_bank_txn_period FOREIGN KEY (reconciliation_period_id) 
    REFERENCES bank_reconciliation_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_bank_txn_account FOREIGN KEY (bank_account_id) 
    REFERENCES banks(id),
  CONSTRAINT check_bank_txn_amount CHECK (amount > 0)
);

CREATE INDEX IF NOT EXISTS idx_bank_txn_period ON bank_transactions(reconciliation_period_id);
CREATE INDEX IF NOT EXISTS idx_bank_txn_account ON bank_transactions(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_bank_txn_date ON bank_transactions(transaction_date);
CREATE INDEX IF NOT EXISTS idx_bank_txn_type ON bank_transactions(type);
CREATE INDEX IF NOT EXISTS idx_bank_txn_created ON bank_transactions(created_at DESC);


-- ===== Migration: 027_ledger_transactions.sql =====

-- Wave 11: Bank Reconciliation - Ledger Transactions
-- Extracted Journal Entry Lines for Bank Accounts

CREATE TABLE IF NOT EXISTS ledger_transactions (
  id TEXT PRIMARY KEY,
  bank_account_id TEXT NOT NULL,
  journal_entry_id TEXT NOT NULL,
  journal_entry_line_idx INT NOT NULL,
  transaction_date DATE NOT NULL,
  amount NUMERIC(19, 2) NOT NULL,
  direction TEXT NOT NULL
    CHECK (direction IN ('inward', 'outward', 'debit', 'credit')),
  description TEXT,
  reference_type TEXT,
  reference_id TEXT,
  posting_status TEXT NOT NULL DEFAULT 'posted'
    CHECK (posting_status IN ('draft', 'posted')),
  reconciliation_status TEXT NOT NULL DEFAULT 'unreconciled'
    CHECK (reconciliation_status IN ('unreconciled', 'reconciled', 'partial', 'disputed')),
  reconciliation_period_id TEXT,
  last_reconciliation_date TIMESTAMPTZ,
  currency_code TEXT,
  fx_rate NUMERIC(19, 6) DEFAULT 1,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_ledger_txn_account FOREIGN KEY (bank_account_id) 
    REFERENCES banks(id),
  CONSTRAINT fk_ledger_txn_period FOREIGN KEY (reconciliation_period_id) 
    REFERENCES bank_reconciliation_periods(id),
  CONSTRAINT check_ledger_txn_amount CHECK (amount > 0),
  CONSTRAINT check_ledger_txn_rate CHECK (fx_rate > 0)
);

CREATE INDEX IF NOT EXISTS idx_ledger_txn_bank ON ledger_transactions(bank_account_id);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_date ON ledger_transactions(transaction_date);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_status ON ledger_transactions(reconciliation_status);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_je ON ledger_transactions(journal_entry_id);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_period ON ledger_transactions(reconciliation_period_id);
CREATE INDEX IF NOT EXISTS idx_ledger_txn_created ON ledger_transactions(created_at DESC);


-- ===== Migration: 028_reconciliation_matches.sql =====

-- Wave 11: Bank Reconciliation - Matches
-- Linking Bank Transactions to Ledger Transactions

CREATE TABLE IF NOT EXISTS reconciliation_matches (
  id TEXT PRIMARY KEY,
  reconciliation_period_id TEXT NOT NULL,
  bank_transaction_id TEXT NOT NULL,
  ledger_transaction_ids TEXT[] NOT NULL DEFAULT '{}',
  match_type TEXT NOT NULL
    CHECK (match_type IN ('auto', 'manual', 'partial', 'multi-line')),
  match_confidence NUMERIC(5, 2) NOT NULL DEFAULT 100,
  match_date TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  matched_by TEXT NOT NULL,
  notes TEXT,
  status TEXT NOT NULL DEFAULT 'matched'
    CHECK (status IN ('matched', 'unmatched', 'disputed', 'pending')),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_match_period FOREIGN KEY (reconciliation_period_id) 
    REFERENCES bank_reconciliation_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_match_bank_txn FOREIGN KEY (bank_transaction_id) 
    REFERENCES bank_transactions(id) ON DELETE CASCADE,
  CONSTRAINT check_match_confidence CHECK (match_confidence >= 0 AND match_confidence <= 100)
);

CREATE INDEX IF NOT EXISTS idx_match_period ON reconciliation_matches(reconciliation_period_id);
CREATE INDEX IF NOT EXISTS idx_match_bank_txn ON reconciliation_matches(bank_transaction_id);
CREATE INDEX IF NOT EXISTS idx_match_status ON reconciliation_matches(status);
CREATE INDEX IF NOT EXISTS idx_match_confidence ON reconciliation_matches(match_confidence);
CREATE INDEX IF NOT EXISTS idx_match_created ON reconciliation_matches(created_at DESC);


-- ===== Migration: 029_bank_reconciliation_differences.sql =====

-- Wave 11: Bank Reconciliation - Differences
-- Unmatched and Unresolved Items

CREATE TABLE IF NOT EXISTS bank_reconciliation_differences (
  id TEXT PRIMARY KEY,
  reconciliation_period_id TEXT NOT NULL,
  type TEXT NOT NULL
    CHECK (type IN (
      'unmatched_bank_transaction',
      'unmatched_ledger_transaction',
      'amount_mismatch',
      'date_variance',
      'outstanding_check',
      'deposit_in_transit',
      'bank_fee',
      'forex_difference',
      'other'
    )),
  amount NUMERIC(19, 2) NOT NULL,
  description TEXT,
  bank_transaction_id TEXT,
  ledger_transaction_id TEXT,
  resolution_type TEXT
    CHECK (resolution_type IS NULL OR resolution_type IN (
      'pending',
      'identified',
      'adjusted',
      'write_off',
      'reversed'
    )),
  resolution_date TIMESTAMPTZ,
  resolved_by TEXT,
  adjustment_entry_id TEXT,
  notes TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_diff_period FOREIGN KEY (reconciliation_period_id) 
    REFERENCES bank_reconciliation_periods(id) ON DELETE CASCADE,
  CONSTRAINT fk_diff_bank_txn FOREIGN KEY (bank_transaction_id) 
    REFERENCES bank_transactions(id),
  CONSTRAINT fk_diff_ledger_txn FOREIGN KEY (ledger_transaction_id) 
    REFERENCES ledger_transactions(id)
);

CREATE INDEX IF NOT EXISTS idx_diff_period ON bank_reconciliation_differences(reconciliation_period_id);
CREATE INDEX IF NOT EXISTS idx_diff_type ON bank_reconciliation_differences(type);
CREATE INDEX IF NOT EXISTS idx_diff_resolution ON bank_reconciliation_differences(resolution_type);
CREATE INDEX IF NOT EXISTS idx_diff_created ON bank_reconciliation_differences(created_at DESC);


-- ===== Migration: 030_recurring_expenses.sql =====

CREATE TABLE IF NOT EXISTS recurring_expense_templates (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  expense_item_id TEXT,
  expense_account_code TEXT NOT NULL,
  payable_account_code TEXT NOT NULL DEFAULT '2130',
  default_amount NUMERIC(18,4) NOT NULL,
  currency_code TEXT,
  frequency TEXT NOT NULL CHECK (frequency IN ('monthly', 'yearly')),
  interval_count INTEGER NOT NULL DEFAULT 1 CHECK (interval_count > 0),
  start_date DATE NOT NULL,
  next_run_date DATE NOT NULL,
  day_of_month INTEGER CHECK (day_of_month BETWEEN 1 AND 31),
  month_of_year INTEGER CHECK (month_of_year BETWEEN 1 AND 12),
  end_date DATE,
  approval_policy TEXT NOT NULL DEFAULT 'single_step' CHECK (approval_policy IN ('none', 'single_step', 'two_step')),
  auto_submit_for_approval BOOLEAN NOT NULL DEFAULT FALSE,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'closed')),
  notes TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_recurring_expense_templates_next_run ON recurring_expense_templates (next_run_date);
CREATE INDEX IF NOT EXISTS idx_recurring_expense_templates_status ON recurring_expense_templates (status);

CREATE TABLE IF NOT EXISTS expense_documents (
  id TEXT PRIMARY KEY,
  template_id TEXT REFERENCES recurring_expense_templates(id) ON DELETE SET NULL,
  expense_item_id TEXT,
  expense_account_code TEXT NOT NULL,
  payable_account_code TEXT NOT NULL DEFAULT '2130',
  period_year INTEGER NOT NULL,
  period_month INTEGER NOT NULL CHECK (period_month BETWEEN 1 AND 12),
  due_date DATE NOT NULL,
  description TEXT NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  currency_code TEXT,
  payment_method TEXT NOT NULL DEFAULT 'bank' CHECK (payment_method IN ('cash', 'bank', 'credit', 'mixed')),
  status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN ('draft', 'pending_approval', 'approved', 'rejected', 'posted', 'partially_paid', 'paid', 'cancelled')),
  paid_amount NUMERIC(18,4) NOT NULL DEFAULT 0,
  submitted_by TEXT,
  submitted_at TIMESTAMPTZ,
  approved_by TEXT,
  approved_at TIMESTAMPTZ,
  rejected_by TEXT,
  rejected_at TIMESTAMPTZ,
  rejected_reason TEXT,
  posted_by TEXT,
  posted_at TIMESTAMPTZ,
  journal_entry_id TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (template_id, period_year, period_month)
);

CREATE INDEX IF NOT EXISTS idx_expense_documents_status ON expense_documents (status);
CREATE INDEX IF NOT EXISTS idx_expense_documents_due_date ON expense_documents (due_date);

CREATE TABLE IF NOT EXISTS expense_payments (
  id TEXT PRIMARY KEY,
  document_id TEXT NOT NULL REFERENCES expense_documents(id) ON DELETE CASCADE,
  payment_date DATE NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  payment_method TEXT NOT NULL CHECK (payment_method IN ('cash', 'bank', 'credit', 'mixed')),
  account_code TEXT NOT NULL,
  reference_number TEXT,
  notes TEXT,
  created_by TEXT NOT NULL,
  journal_entry_id TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_expense_payments_document ON expense_payments (document_id);


-- ===== Migration: 031_expense_document_history.sql =====

CREATE TABLE IF NOT EXISTS expense_document_history (
  id TEXT PRIMARY KEY,
  document_id TEXT NOT NULL REFERENCES expense_documents(id) ON DELETE CASCADE,
  from_status TEXT,
  to_status TEXT NOT NULL,
  action TEXT NOT NULL CHECK (action IN ('created', 'submitted', 'approved', 'rejected', 'posted', 'payment', 'status_changed')),
  actor_id TEXT,
  actor_name TEXT,
  note TEXT,
  details JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_expense_document_history_document ON expense_document_history (document_id, created_at);

-- ===== Migration: 031_recurring_expenses_advanced.sql =====

-- Migration: Advanced Recurring Expenses Architecture
-- Phase 1: Contract, Schedule, Obligation, Posting, Allocation layers

-- ============================================================================
-- 1. RECURRING EXPENSE CONTRACTS
-- ============================================================================
CREATE TABLE IF NOT EXISTS recurring_expense_contracts (
  id TEXT PRIMARY KEY,
  template_id TEXT REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  name TEXT NOT NULL,
  vendor_id TEXT,
  contract_date DATE NOT NULL,
  start_date DATE NOT NULL,
  end_date DATE,
  renewal_terms TEXT NOT NULL DEFAULT 'manual_renew' CHECK (renewal_terms IN ('auto_renew', 'manual_renew', 'term_end')),
  escalation_policy TEXT NOT NULL DEFAULT 'none' CHECK (escalation_policy IN ('none', 'fixed_percent', 'indexed')),
  escalation_value NUMERIC(8,4),
  allocation_strategy TEXT NOT NULL DEFAULT 'none' CHECK (allocation_strategy IN ('none', 'branch', 'cost_center', 'project', 'department')),
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'expired', 'terminated', 'under_review')),
  notes TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_recurring_contracts_template_id ON recurring_expense_contracts(template_id);
CREATE INDEX IF NOT EXISTS idx_recurring_contracts_status ON recurring_expense_contracts(status);
CREATE INDEX IF NOT EXISTS idx_recurring_contracts_dates ON recurring_expense_contracts(start_date, end_date);

-- ============================================================================
-- 2. RECURRING SCHEDULES
-- ============================================================================
CREATE TABLE IF NOT EXISTS recurring_schedules (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL UNIQUE REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  contract_id TEXT REFERENCES recurring_expense_contracts(id) ON DELETE SET NULL,
  current_period_start DATE,
  current_period_end DATE,
  next_scheduled_date DATE NOT NULL,
  last_generated_date DATE,
  missed_generations INTEGER NOT NULL DEFAULT 0,
  last_retry_at TIMESTAMPTZ,
  retry_count INTEGER NOT NULL DEFAULT 0,
  status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'paused', 'archived')),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_recurring_schedules_next_date ON recurring_schedules(next_scheduled_date);
CREATE INDEX IF NOT EXISTS idx_recurring_schedules_status ON recurring_schedules(status);

-- ============================================================================
-- 3. EXPENSE BASIS & OBLIGATIONS
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_obligations (
  id TEXT PRIMARY KEY,
  document_id TEXT REFERENCES expense_documents(id) ON DELETE CASCADE,
  contract_id TEXT REFERENCES recurring_expense_contracts(id) ON DELETE SET NULL,
  template_id TEXT REFERENCES recurring_expense_templates(id) ON DELETE SET NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  gross_amount NUMERIC(18,4) NOT NULL,
  currency_code TEXT,
  basis TEXT NOT NULL DEFAULT 'cash' CHECK (basis IN ('cash', 'accrual', 'prepaid')),
  recognition_date DATE,
  settlement_date DATE,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'recognized', 'settled', 'partially_settled', 'reversed')),
  reversal_obligation_id TEXT REFERENCES expense_obligations(id) ON DELETE SET NULL,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (document_id, contract_id)
);

CREATE INDEX IF NOT EXISTS idx_expense_obligations_document ON expense_obligations(document_id);
CREATE INDEX IF NOT EXISTS idx_expense_obligations_contract ON expense_obligations(contract_id);
CREATE INDEX IF NOT EXISTS idx_expense_obligations_status ON expense_obligations(status);
CREATE INDEX IF NOT EXISTS idx_expense_obligations_dates ON expense_obligations(period_start, period_end);

-- ============================================================================
-- 4. EXPENSE POSTINGS (Journal Entry Bridge)
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_postings (
  id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES expense_obligations(id) ON DELETE CASCADE,
  document_id TEXT REFERENCES expense_documents(id) ON DELETE CASCADE,
  journal_entry_id TEXT,
  debit_account TEXT NOT NULL,
  credit_account TEXT NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  posting_date DATE NOT NULL,
  posting_type TEXT NOT NULL DEFAULT 'regular' CHECK (posting_type IN ('regular', 'reversal', 'adjustment')),
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'posted', 'reversed', 'cancelled')),
  reversal_posting_id TEXT REFERENCES expense_postings(id) ON DELETE SET NULL,
  reversal_reason TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_expense_postings_obligation ON expense_postings(obligation_id);
CREATE INDEX IF NOT EXISTS idx_expense_postings_document ON expense_postings(document_id);
CREATE INDEX IF NOT EXISTS idx_expense_postings_journal_entry ON expense_postings(journal_entry_id);
CREATE INDEX IF NOT EXISTS idx_expense_postings_status ON expense_postings(status);
CREATE INDEX IF NOT EXISTS idx_expense_postings_posting_date ON expense_postings(posting_date);

-- ============================================================================
-- 5. EXPENSE ALLOCATIONS
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_allocations (
  id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES expense_obligations(id) ON DELETE CASCADE,
  allocation_type TEXT NOT NULL CHECK (allocation_type IN ('branch', 'cost_center', 'project', 'department', 'employee')),
  allocation_target_id TEXT NOT NULL,
  allocation_target_name TEXT,
  percentage NUMERIC(5,2) NOT NULL CHECK (percentage > 0 AND percentage <= 100),
  amount NUMERIC(18,4) NOT NULL,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_expense_allocations_obligation ON expense_allocations(obligation_id);
CREATE INDEX IF NOT EXISTS idx_expense_allocations_type_target ON expense_allocations(allocation_type, allocation_target_id);

-- ============================================================================
-- 6. PREPAID EXPENSE AMORTIZATION SCHEDULE
-- ============================================================================
CREATE TABLE IF NOT EXISTS prepaid_amortization_schedules (
  id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES expense_obligations(id) ON DELETE CASCADE,
  period_number INTEGER NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'posted', 'reversed')),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (obligation_id, period_number)
);

CREATE INDEX IF NOT EXISTS idx_prepaid_schedules_obligation ON prepaid_amortization_schedules(obligation_id);
CREATE INDEX IF NOT EXISTS idx_prepaid_schedules_status ON prepaid_amortization_schedules(status);

-- ============================================================================
-- 7. ESCALATION RULES
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_escalation_rules (
  id TEXT PRIMARY KEY,
  contract_id TEXT NOT NULL REFERENCES recurring_expense_contracts(id) ON DELETE CASCADE,
  escalation_type TEXT NOT NULL CHECK (escalation_type IN ('fixed_percent', 'indexed', 'tiered')),
  escalation_value NUMERIC(8,4),
  index_source TEXT, -- 'cpi', 'custom', 'manual'
  effective_date DATE NOT NULL,
  end_date DATE,
  status TEXT NOT NULL DEFAULT 'active',
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_escalation_rules_contract ON expense_escalation_rules(contract_id);

-- ============================================================================
-- 8. PAYMENT SCHEDULE (Separate from Obligation)
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_payment_schedules (
  id TEXT PRIMARY KEY,
  obligation_id TEXT NOT NULL REFERENCES expense_obligations(id) ON DELETE CASCADE,
  due_date DATE NOT NULL,
  amount NUMERIC(18,4) NOT NULL,
  payment_method TEXT DEFAULT 'bank',
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'partially_paid', 'paid', 'overdue', 'cancelled')),
  paid_amount NUMERIC(18,4) NOT NULL DEFAULT 0,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_payment_schedules_obligation ON expense_payment_schedules(obligation_id);
CREATE INDEX IF NOT EXISTS idx_payment_schedules_due_date ON expense_payment_schedules(due_date);
CREATE INDEX IF NOT EXISTS idx_payment_schedules_status ON expense_payment_schedules(status);

-- ============================================================================
-- 9. EXPENSE AUDIT LOG
-- ============================================================================
CREATE TABLE IF NOT EXISTS expense_audit_log (
  id TEXT PRIMARY KEY,
  entity_type TEXT NOT NULL CHECK (entity_type IN ('template', 'contract', 'document', 'obligation', 'posting', 'payment')),
  entity_id TEXT NOT NULL,
  action TEXT NOT NULL,
  old_values JSONB,
  new_values JSONB,
  actor_id TEXT NOT NULL,
  actor_name TEXT,
  reason TEXT,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_expense_audit_entity ON expense_audit_log(entity_type, entity_id);
CREATE INDEX IF NOT EXISTS idx_expense_audit_created ON expense_audit_log(created_at DESC);

-- ============================================================================
-- 10. Duplicate Prevention Tracking
-- ============================================================================
CREATE TABLE IF NOT EXISTS recurring_generation_log (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  generated_document_ids TEXT[] NOT NULL DEFAULT '{}',
  status TEXT NOT NULL DEFAULT 'success' CHECK (status IN ('success', 'failed', 'partial')),
  error_message TEXT,
  generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  UNIQUE (template_id, period_start, period_end)
);

CREATE INDEX IF NOT EXISTS idx_generation_log_template ON recurring_generation_log(template_id);
CREATE INDEX IF NOT EXISTS idx_generation_log_period ON recurring_generation_log(period_start, period_end);


-- ===== Migration: 032_attendance_records.sql =====

-- Create attendance_records table for PostgreSQL
-- Stores attendance check-in/check-out records with multiple record types
CREATE TABLE IF NOT EXISTS attendance_records (
  id VARCHAR(50) PRIMARY KEY,
  employee_id VARCHAR(50) NOT NULL,
  date VARCHAR(10) NOT NULL, -- YYYY-MM-DD
  
  -- Record type: present, absent, late, sick, leave
  record_type VARCHAR(20) NOT NULL DEFAULT 'present',
  
  -- Check-in and check-out times
  check_in_time TIMESTAMP,
  check_out_time TIMESTAMP,
  
  -- Duration in hours (calculated)
  duration_hours DECIMAL(5, 2),
  
  -- Late minutes (if applicable)
  late_minutes INT DEFAULT 0,
  
  -- Device info
  device VARCHAR(30) NOT NULL DEFAULT 'web', -- web, biometric, face_recognition, mobile
  device_id VARCHAR(100), -- Biometric ID or device MAC
  ip_address VARCHAR(45),
  
  -- Notes and justification
  notes TEXT,
  justification TEXT,
  justification_type VARCHAR(30), -- sick-leave, personal-leave, permission
  
  -- Approval status
  status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, approved, rejected
  approved_by VARCHAR(50),
  approval_date TIMESTAMP,
  approval_reason TEXT,
  
  -- Metadata
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(50),
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_by VARCHAR(50),
  
  -- Foreign key
  CONSTRAINT fk_attendance_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Create indexes for common queries
CREATE INDEX IF NOT EXISTS idx_attendance_employee_date ON attendance_records(employee_id, date);
CREATE INDEX IF NOT EXISTS idx_attendance_record_type ON attendance_records(record_type);
CREATE INDEX IF NOT EXISTS idx_attendance_status ON attendance_records(status);
CREATE INDEX IF NOT EXISTS idx_attendance_created_at ON attendance_records(created_at);
CREATE INDEX IF NOT EXISTS idx_attendance_date ON attendance_records(date);

-- Create attendance_audit_log table
-- Audit trail for all attendance record changes
CREATE TABLE IF NOT EXISTS attendance_audit_log (
  id VARCHAR(50) PRIMARY KEY,
  record_id VARCHAR(50) NOT NULL,
  
  -- Action type
  action VARCHAR(30) NOT NULL, -- created, updated, approved, rejected, deleted
  
  -- Change tracking
  changed_by VARCHAR(50),
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  -- Previous and new values
  previous_value JSONB,
  new_value JSONB,
  
  -- Reason for change
  reason TEXT,
  
  -- Foreign key
  CONSTRAINT fk_audit_record FOREIGN KEY (record_id) REFERENCES attendance_records(id) ON DELETE CASCADE
);

-- Create indexes for audit log
CREATE INDEX IF NOT EXISTS idx_audit_record_id ON attendance_audit_log(record_id);
CREATE INDEX IF NOT EXISTS idx_audit_changed_at ON attendance_audit_log(changed_at);
CREATE INDEX IF NOT EXISTS idx_audit_action ON attendance_audit_log(action);

-- Create employee_biometric_mapping table
CREATE TABLE IF NOT EXISTS employee_biometric_mapping (
  id VARCHAR(50) PRIMARY KEY,
  employee_id VARCHAR(50) NOT NULL,
  employee_name VARCHAR(255) NOT NULL,
  biometric_id VARCHAR(100) NOT NULL UNIQUE,
  device_type VARCHAR(30) NOT NULL DEFAULT 'fingerprint', -- fingerprint, face, iris, other
  device_mac_address VARCHAR(17),
  is_active BOOLEAN NOT NULL DEFAULT true,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  
  -- Foreign key
  CONSTRAINT fk_biometric_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE
);

-- Create indexes for biometric mapping
CREATE INDEX IF NOT EXISTS idx_biometric_employee_id ON employee_biometric_mapping(employee_id);
CREATE INDEX IF NOT EXISTS idx_biometric_id ON employee_biometric_mapping(biometric_id);
CREATE INDEX IF NOT EXISTS idx_biometric_is_active ON employee_biometric_mapping(is_active);


-- ===== Migration: 032_recurring_expenses_scheduler.sql =====

-- Phase 2: Recurring Expenses - Background Scheduler Service
-- Tables for managing automated document generation and scheduler status

-- Table to track scheduled jobs for each recurring expense template
CREATE TABLE IF NOT EXISTS recurring_schedule_jobs (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL,
  contract_id TEXT,
  
  -- Next scheduled generation date
  next_run_date TIMESTAMPTZ NOT NULL,
  
  -- Status of the schedule
  status TEXT NOT NULL DEFAULT 'active', -- active, paused, completed, cancelled
  
  -- Retry information
  retry_count INT DEFAULT 0,
  max_retries INT DEFAULT 3,
  last_error TEXT,
  
  -- Execution tracking
  last_run_date TIMESTAMPTZ,
  total_runs INT DEFAULT 0,
  
  -- Holiday and weekend handling
  skip_weekends BOOLEAN DEFAULT false,
  skip_holidays BOOLEAN DEFAULT false,
  holiday_shift_days INT DEFAULT 0, -- shift by N days if falls on holiday
  
  -- Document generation settings
  auto_create_obligation BOOLEAN DEFAULT false,
  obligation_basis TEXT DEFAULT 'accrual', -- cash, accrual, prepaid
  auto_create_posting BOOLEAN DEFAULT false,
  
  -- Audit trail
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by TEXT,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  -- Raw/extensible field for future features
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  
  -- Foreign keys
  CONSTRAINT fk_template FOREIGN KEY (template_id) REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  CONSTRAINT fk_contract FOREIGN KEY (contract_id) REFERENCES recurring_expense_contracts(id) ON DELETE SET NULL
);

CREATE INDEX idx_schedule_jobs_next_run ON recurring_schedule_jobs(next_run_date) WHERE status = 'active';
CREATE INDEX idx_schedule_jobs_template ON recurring_schedule_jobs(template_id);
CREATE INDEX idx_schedule_jobs_contract ON recurring_schedule_jobs(contract_id);
CREATE INDEX idx_schedule_jobs_status ON recurring_schedule_jobs(status);

-- Table to log all background job executions
CREATE TABLE IF NOT EXISTS background_job_logs (
  id TEXT PRIMARY KEY,
  job_id TEXT NOT NULL,
  
  -- What was executed
  job_type TEXT NOT NULL, -- 'generate_document', 'apply_escalation', 'process_prepaid_amortization', etc.
  
  -- Execution details
  started_at TIMESTAMPTZ NOT NULL,
  completed_at TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'pending', -- pending, running, success, failed, retry
  
  -- Result tracking
  generated_documents INT DEFAULT 0,
  failed_documents INT DEFAULT 0,
  
  -- Error handling
  error_message TEXT,
  error_stack TEXT,
  
  -- Metadata
  created_by TEXT, -- System or user ID if manual trigger
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  
  -- Foreign key
  CONSTRAINT fk_job FOREIGN KEY (job_id) REFERENCES recurring_schedule_jobs(id) ON DELETE CASCADE
);

CREATE INDEX idx_job_logs_job_id ON background_job_logs(job_id);
CREATE INDEX idx_job_logs_status ON background_job_logs(status);
CREATE INDEX idx_job_logs_started_at ON background_job_logs(started_at DESC);

-- Table to prevent duplicate document generation
CREATE TABLE IF NOT EXISTS recurring_generation_duplicates (
  id TEXT PRIMARY KEY,
  job_id TEXT NOT NULL,
  template_id TEXT NOT NULL,
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  
  -- Generation attempt info
  generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  attempts INT DEFAULT 1,
  
  -- To prevent race conditions
  generated_document_id TEXT,
  
  CONSTRAINT fk_job_dup FOREIGN KEY (job_id) REFERENCES recurring_schedule_jobs(id) ON DELETE CASCADE,
  CONSTRAINT fk_template_dup FOREIGN KEY (template_id) REFERENCES recurring_expense_templates(id) ON DELETE CASCADE
);

CREATE UNIQUE INDEX idx_unique_generation ON recurring_generation_duplicates(job_id, template_id, period_start, period_end);

-- Table for escalation rule execution tracking
CREATE TABLE IF NOT EXISTS escalation_executions (
  id TEXT PRIMARY KEY,
  template_id TEXT NOT NULL,
  contract_id TEXT,
  
  -- What was escalated
  escalation_type TEXT NOT NULL, -- 'fixed_percent', 'indexed', 'tiered'
  escalation_rule_id TEXT,
  
  -- When it happened
  execution_date DATE NOT NULL,
  new_amount DECIMAL(15,2) NOT NULL,
  previous_amount DECIMAL(15,2),
  increase_percent DECIMAL(5,2),
  
  -- Result
  status TEXT NOT NULL DEFAULT 'completed', -- completed, pending, failed
  error_message TEXT,
  
  -- Audit
  created_by TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  
  CONSTRAINT fk_esc_template FOREIGN KEY (template_id) REFERENCES recurring_expense_templates(id) ON DELETE CASCADE,
  CONSTRAINT fk_esc_contract FOREIGN KEY (contract_id) REFERENCES recurring_expense_contracts(id) ON DELETE SET NULL
);

CREATE INDEX idx_escalation_date ON escalation_executions(execution_date);
CREATE INDEX idx_escalation_template ON escalation_executions(template_id);
CREATE INDEX idx_escalation_status ON escalation_executions(status);

-- Scheduler status table (single row)
CREATE TABLE IF NOT EXISTS scheduler_status (
  id TEXT PRIMARY KEY DEFAULT 'scheduler-main',
  
  -- Overall scheduler state
  is_running BOOLEAN DEFAULT false,
  is_healthy BOOLEAN DEFAULT true,
  
  -- Last execution info
  last_run_at TIMESTAMPTZ,
  last_check_at TIMESTAMPTZ,
  next_check_at TIMESTAMPTZ,
  
  -- Statistics
  total_jobs INT DEFAULT 0,
  active_jobs INT DEFAULT 0,
  completed_jobs INT DEFAULT 0,
  failed_jobs INT DEFAULT 0,
  
  -- Health metrics
  average_job_duration_ms INT,
  last_error_message TEXT,
  error_count INT DEFAULT 0,
  
  -- Configuration
  check_interval_seconds INT DEFAULT 300, -- Run every 5 minutes
  enable_auto_generation BOOLEAN DEFAULT true,
  enable_escalation BOOLEAN DEFAULT true,
  enable_amortization BOOLEAN DEFAULT true,
  
  -- Audit
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  raw JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- Insert initial scheduler status
INSERT INTO scheduler_status (id, is_running, is_healthy)
VALUES ('scheduler-main', false, true)
ON CONFLICT (id) DO NOTHING;

-- Indexes for performance optimization
CREATE INDEX idx_recurring_jobs_active ON recurring_schedule_jobs(status, next_run_date) WHERE status = 'active';

-- Grant permissions (if needed for specific roles)
-- GRANT SELECT, INSERT, UPDATE ON recurring_schedule_jobs TO app_user;
-- GRANT SELECT, INSERT ON background_job_logs TO app_user;
-- GRANT SELECT ON scheduler_status TO app_user;


-- ===== Migration: 033_attendance_correction_requests.sql =====

CREATE TABLE IF NOT EXISTS attendance_correction_requests (
  id TEXT PRIMARY KEY,
  record_id TEXT NOT NULL REFERENCES attendance_records(id) ON DELETE CASCADE,
  employee_id TEXT NOT NULL REFERENCES employees(id) ON DELETE CASCADE,
  reason TEXT NOT NULL,
  requested_record_type TEXT CHECK (requested_record_type IN ('present', 'absent', 'late', 'sick', 'leave')),
  requested_check_in_time TIMESTAMPTZ,
  requested_check_out_time TIMESTAMPTZ,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
  reviewed_by TEXT,
  reviewed_at TIMESTAMPTZ,
  review_reason TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_att_corr_employee_id ON attendance_correction_requests(employee_id);
CREATE INDEX IF NOT EXISTS idx_att_corr_status ON attendance_correction_requests(status);
CREATE INDEX IF NOT EXISTS idx_att_corr_created_at ON attendance_correction_requests(created_at);
CREATE INDEX IF NOT EXISTS idx_att_corr_record_id ON attendance_correction_requests(record_id);


-- ===== Migration: 034_biometric_architecture_layers.sql =====

-- Professional biometric architecture: Devices / Enrollment / Attendance Logs

CREATE TABLE IF NOT EXISTS biometric_devices (
  id VARCHAR(50) PRIMARY KEY,
  device_id VARCHAR(100) NOT NULL,
  device_name VARCHAR(255) NOT NULL,
  brand VARCHAR(100),
  model VARCHAR(100),
  serial_number VARCHAR(150) NOT NULL,
  ip_address VARCHAR(45) NOT NULL,
  port INT NOT NULL DEFAULT 4370,
  communication_key VARCHAR(255),
  branch_id VARCHAR(50),
  timezone VARCHAR(80) NOT NULL DEFAULT 'Asia/Riyadh',
  provider_type VARCHAR(50) NOT NULL, -- zkteco, suprema, hikvision, anviz, tcpip, cloud_api, push_sdk
  sync_mode VARCHAR(20) NOT NULL DEFAULT 'pull', -- push, pull
  status VARCHAR(20) NOT NULL DEFAULT 'active', -- active, inactive, maintenance, offline
  last_sync_at TIMESTAMPTZ,
  firmware_version VARCHAR(100),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT uq_biometric_devices_device_id UNIQUE (device_id),
  CONSTRAINT uq_biometric_devices_serial_number UNIQUE (serial_number),
  CONSTRAINT uq_biometric_devices_endpoint_provider UNIQUE (ip_address, port, provider_type),
  CONSTRAINT chk_biometric_devices_sync_mode CHECK (sync_mode IN ('push', 'pull')),
  CONSTRAINT chk_biometric_devices_status CHECK (status IN ('active', 'inactive', 'maintenance', 'offline'))
);

CREATE INDEX IF NOT EXISTS idx_biometric_devices_branch_id ON biometric_devices(branch_id);
CREATE INDEX IF NOT EXISTS idx_biometric_devices_provider_type ON biometric_devices(provider_type);
CREATE INDEX IF NOT EXISTS idx_biometric_devices_status ON biometric_devices(status);
CREATE INDEX IF NOT EXISTS idx_biometric_devices_last_sync_at ON biometric_devices(last_sync_at);

CREATE TABLE IF NOT EXISTS biometric_enrollments (
  id VARCHAR(50) PRIMARY KEY,
  employee_id VARCHAR(50) NOT NULL,
  device_id VARCHAR(50) NOT NULL,
  biometric_user_id VARCHAR(120) NOT NULL,
  card_number VARCHAR(120),
  enrollment_status VARCHAR(20) NOT NULL DEFAULT 'active', -- active, pending, suspended, revoked
  enrolled_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_biometric_enrollments_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE CASCADE,
  CONSTRAINT fk_biometric_enrollments_device FOREIGN KEY (device_id) REFERENCES biometric_devices(id) ON DELETE CASCADE,
  CONSTRAINT uq_biometric_enrollments_device_user UNIQUE (device_id, biometric_user_id),
  CONSTRAINT uq_biometric_enrollments_device_card UNIQUE (device_id, card_number),
  CONSTRAINT chk_biometric_enrollments_status CHECK (enrollment_status IN ('active', 'pending', 'suspended', 'revoked'))
);

CREATE INDEX IF NOT EXISTS idx_biometric_enrollments_employee_id ON biometric_enrollments(employee_id);
CREATE INDEX IF NOT EXISTS idx_biometric_enrollments_device_id ON biometric_enrollments(device_id);
CREATE INDEX IF NOT EXISTS idx_biometric_enrollments_status ON biometric_enrollments(enrollment_status);
CREATE INDEX IF NOT EXISTS idx_biometric_enrollments_enrolled_at ON biometric_enrollments(enrolled_at);

CREATE TABLE IF NOT EXISTS attendance_device_logs (
  id VARCHAR(50) PRIMARY KEY,
  device_id VARCHAR(50) NOT NULL,
  enrollment_id VARCHAR(50),
  employee_id VARCHAR(50),
  log_time TIMESTAMPTZ NOT NULL,
  raw_payload JSONB,
  processed_record_id VARCHAR(50),
  source_type VARCHAR(50) NOT NULL DEFAULT 'device', -- device, sdk_push, sdk_pull, cloud_api, mobile
  punch_type VARCHAR(30), -- check_in, check_out, break_in, break_out, unknown
  sync_status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, processed, failed, duplicate, retried
  duplicate_key VARCHAR(255),
  is_duplicate BOOLEAN NOT NULL DEFAULT FALSE,
  manual_adjustment BOOLEAN NOT NULL DEFAULT FALSE,
  manual_adjusted_by VARCHAR(50),
  manual_adjustment_reason TEXT,
  retry_count INT NOT NULL DEFAULT 0,
  audit_trail JSONB,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

  CONSTRAINT fk_attendance_device_logs_device FOREIGN KEY (device_id) REFERENCES biometric_devices(id) ON DELETE CASCADE,
  CONSTRAINT fk_attendance_device_logs_enrollment FOREIGN KEY (enrollment_id) REFERENCES biometric_enrollments(id) ON DELETE SET NULL,
  CONSTRAINT fk_attendance_device_logs_employee FOREIGN KEY (employee_id) REFERENCES employees(id) ON DELETE SET NULL,
  CONSTRAINT fk_attendance_device_logs_record FOREIGN KEY (processed_record_id) REFERENCES attendance_records(id) ON DELETE SET NULL,
  CONSTRAINT chk_attendance_device_logs_sync_status CHECK (sync_status IN ('pending', 'processed', 'failed', 'duplicate', 'retried'))
);

CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_device_id ON attendance_device_logs(device_id);
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_employee_id ON attendance_device_logs(employee_id);
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_log_time ON attendance_device_logs(log_time DESC);
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_sync_status ON attendance_device_logs(sync_status);
CREATE INDEX IF NOT EXISTS idx_attendance_device_logs_duplicate_key ON attendance_device_logs(duplicate_key);


-- ===== Migration: 035_restaurant_pos_tables.sql =====

-- Restaurant POS foundation tables: areas, tables, sessions, and table events

CREATE TABLE IF NOT EXISTS restaurant_areas (
  id TEXT PRIMARY KEY,
  area_code TEXT,
  name TEXT NOT NULL,
  display_order INTEGER NOT NULL DEFAULT 0,
  color_hex TEXT,
  inactive BOOLEAN NOT NULL DEFAULT FALSE,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS restaurant_tables (
  id TEXT PRIMARY KEY,
  table_code TEXT,
  name TEXT NOT NULL,
  area_id TEXT,
  seats_count INTEGER NOT NULL DEFAULT 4,
  status TEXT NOT NULL DEFAULT 'available',
  inactive BOOLEAN NOT NULL DEFAULT FALSE,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_restaurant_tables_area FOREIGN KEY (area_id) REFERENCES restaurant_areas(id) ON DELETE SET NULL,
  CONSTRAINT chk_restaurant_tables_status CHECK (status IN ('available', 'occupied', 'reserved', 'cleaning', 'out_of_service'))
);

CREATE TABLE IF NOT EXISTS restaurant_order_sessions (
  id TEXT PRIMARY KEY,
  session_number TEXT NOT NULL,
  channel TEXT NOT NULL DEFAULT 'dine_in',
  area_id TEXT,
  table_id TEXT,
  invoice_id TEXT,
  guest_count INTEGER NOT NULL DEFAULT 1,
  status TEXT NOT NULL DEFAULT 'open',
  started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  closed_at TIMESTAMPTZ,
  raw JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_restaurant_sessions_area FOREIGN KEY (area_id) REFERENCES restaurant_areas(id) ON DELETE SET NULL,
  CONSTRAINT fk_restaurant_sessions_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL,
  CONSTRAINT fk_restaurant_sessions_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL,
  CONSTRAINT chk_restaurant_sessions_channel CHECK (channel IN ('dine_in', 'takeaway', 'delivery')),
  CONSTRAINT chk_restaurant_sessions_status CHECK (status IN ('open', 'in_preparation', 'ready', 'served', 'partially_paid', 'closed', 'cancelled'))
);

CREATE TABLE IF NOT EXISTS restaurant_table_events (
  id TEXT PRIMARY KEY,
  session_id TEXT NOT NULL,
  table_id TEXT,
  event_type TEXT NOT NULL,
  event_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  actor_name TEXT,
  actor_id TEXT,
  reason TEXT,
  payload JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  CONSTRAINT fk_restaurant_table_events_session FOREIGN KEY (session_id) REFERENCES restaurant_order_sessions(id) ON DELETE CASCADE,
  CONSTRAINT fk_restaurant_table_events_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL,
  CONSTRAINT chk_restaurant_table_events_type CHECK (event_type IN ('session_opened', 'table_assigned', 'table_merged', 'item_transferred', 'bill_split', 'partial_checkout', 'session_closed', 'session_cancelled'))
);

CREATE UNIQUE INDEX IF NOT EXISTS uq_restaurant_areas_name ON restaurant_areas(name);
CREATE UNIQUE INDEX IF NOT EXISTS uq_restaurant_tables_name ON restaurant_tables(name);
CREATE UNIQUE INDEX IF NOT EXISTS uq_restaurant_sessions_number ON restaurant_order_sessions(session_number);

CREATE INDEX IF NOT EXISTS idx_restaurant_tables_area_status ON restaurant_tables(area_id, status);
CREATE INDEX IF NOT EXISTS idx_restaurant_sessions_table_status ON restaurant_order_sessions(table_id, status);
CREATE INDEX IF NOT EXISTS idx_restaurant_sessions_channel_status ON restaurant_order_sessions(channel, status);
CREATE INDEX IF NOT EXISTS idx_restaurant_table_events_session_at ON restaurant_table_events(session_id, event_at DESC);


COMMIT;