Cash Advance Employee Payroll Deductions

-- ═══════════════════════════════════════
-- CASH ADVANCE MODULE — Table Setup
-- Run once in Supabase SQL Editor
-- Does NOT touch any existing tables
-- ═══════════════════════════════════════

-- 1. Employee Cash Advance Records
CREATE TABLE IF NOT EXISTS cash_advances (
  id              bigserial PRIMARY KEY,
  employee_name   text        NOT NULL,
  department      text,
  granted_date    date        NOT NULL DEFAULT CURRENT_DATE,
  amount          numeric(12,2) NOT NULL CHECK (amount > 0),
  monthly_deduction numeric(12,2) NOT NULL CHECK (monthly_deduction > 0),
  remaining_balance numeric(12,2) NOT NULL,
  status          text        NOT NULL DEFAULT 'Active'
                  CHECK (status IN ('Active','Fully Paid')),
  remarks         text,
  created_at      timestamptz NOT NULL DEFAULT now()
);

-- 2. Payroll + Deduction History
CREATE TABLE IF NOT EXISTS ca_payroll_history (
  id              bigserial PRIMARY KEY,
  cash_advance_id bigint      REFERENCES cash_advances(id) ON DELETE SET NULL,
  employee_name   text        NOT NULL,
  payroll_period  text        NOT NULL,          -- e.g. "2026-07"
  gross_salary    numeric(12,2) NOT NULL,
  ca_deduction    numeric(12,2) NOT NULL DEFAULT 0,
  other_deductions numeric(12,2) NOT NULL DEFAULT 0,
  allowances      numeric(12,2) NOT NULL DEFAULT 0,
  net_pay         numeric(12,2) NOT NULL,
  balance_before  numeric(12,2) NOT NULL DEFAULT 0,
  balance_after   numeric(12,2) NOT NULL DEFAULT 0,
  processed_by    text,
  remarks         text,
  processed_at    timestamptz NOT NULL DEFAULT now()
);

-- 3. Daily Payroll Records
CREATE TABLE IF NOT EXISTS ca_daily_payroll (
  id               bigserial PRIMARY KEY,
  cash_advance_id  bigint    REFERENCES cash_advances(id) ON DELETE SET NULL,
  employee_name    text      NOT NULL,
  payroll_date     date      NOT NULL,
  daily_rate       numeric(12,2) NOT NULL,
  days_worked      numeric(6,2)  NOT NULL DEFAULT 0,
  overtime_hours   numeric(6,2)  NOT NULL DEFAULT 0,
  late_hours       numeric(6,2)  NOT NULL DEFAULT 0,
  absent_days      numeric(6,2)  NOT NULL DEFAULT 0,
  basic_pay        numeric(12,2) NOT NULL DEFAULT 0,
  ot_pay           numeric(12,2) NOT NULL DEFAULT 0,
  late_deduction   numeric(12,2) NOT NULL DEFAULT 0,
  absent_deduction numeric(12,2) NOT NULL DEFAULT 0,
  allowances       numeric(12,2) NOT NULL DEFAULT 0,
  gross_pay        numeric(12,2) NOT NULL,
  ca_deduction     numeric(12,2) NOT NULL DEFAULT 0,
  other_deductions numeric(12,2) NOT NULL DEFAULT 0,
  net_pay          numeric(12,2) NOT NULL,
  balance_before   numeric(12,2) NOT NULL DEFAULT 0,
  balance_after    numeric(12,2) NOT NULL DEFAULT 0,
  processed_by     text,
  remarks          text,
  processed_at     timestamptz NOT NULL DEFAULT now()
);

-- 4. Indexes for performance
CREATE INDEX IF NOT EXISTS idx_ca_employee   ON cash_advances(employee_name);
CREATE INDEX IF NOT EXISTS idx_ca_status     ON cash_advances(status);
CREATE INDEX IF NOT EXISTS idx_caph_emp      ON ca_payroll_history(employee_name);
CREATE INDEX IF NOT EXISTS idx_caph_period   ON ca_payroll_history(payroll_period);
CREATE INDEX IF NOT EXISTS idx_caph_ca_id    ON ca_payroll_history(cash_advance_id);
CREATE INDEX IF NOT EXISTS idx_cadp_emp      ON ca_daily_payroll(employee_name);
CREATE INDEX IF NOT EXISTS idx_cadp_date     ON ca_daily_payroll(payroll_date);
CREATE INDEX IF NOT EXISTS idx_cadp_ca_id    ON ca_daily_payroll(cash_advance_id);

-- 5. Enable Row Level Security
ALTER TABLE cash_advances       ENABLE ROW LEVEL SECURITY;
ALTER TABLE ca_payroll_history  ENABLE ROW LEVEL SECURITY;
ALTER TABLE ca_daily_payroll    ENABLE ROW LEVEL SECURITY;

-- 6. Open RLS policies (DROP first to avoid "already exists" error on re-run)
DROP POLICY IF EXISTS "ca_open"    ON cash_advances;
DROP POLICY IF EXISTS "caph_open"  ON ca_payroll_history;
DROP POLICY IF EXISTS "cadp_open"  ON ca_daily_payroll;

CREATE POLICY "ca_open"    ON cash_advances      FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "caph_open"  ON ca_payroll_history FOR ALL USING (true) WITH CHECK (true);
CREATE POLICY "cadp_open"  ON ca_daily_payroll   FOR ALL USING (true) WITH CHECK (true);