-- Company salary slips — run once in Supabase SQL editor

ALTER TABLE users
  ADD COLUMN IF NOT EXISTS cnic text,
  ADD COLUMN IF NOT EXISTS designation text;

CREATE TABLE IF NOT EXISTS salary_slips (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  employee_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_by uuid NOT NULL REFERENCES users(id),
  slip_date date NOT NULL,
  pay_slip_month char(7) NOT NULL,
  employee_name text NOT NULL,
  cnic text,
  designation text,
  basic_salary numeric(14, 2) NOT NULL DEFAULT 0,
  funds numeric(14, 2) NOT NULL DEFAULT 0,
  others numeric(14, 2) NOT NULL DEFAULT 0,
  bonus_day_extra numeric(14, 2) NOT NULL DEFAULT 0,
  arrears numeric(14, 2) NOT NULL DEFAULT 0,
  other_bonuses numeric(14, 2) NOT NULL DEFAULT 0,
  loans numeric(14, 2) NOT NULL DEFAULT 0,
  income_tax numeric(14, 2) NOT NULL DEFAULT 0,
  professional_tax numeric(14, 2) NOT NULL DEFAULT 0,
  eobi numeric(14, 2) NOT NULL DEFAULT 0,
  other_deductions numeric(14, 2) NOT NULL DEFAULT 0,
  comments text,
  gross_salary numeric(14, 2) NOT NULL DEFAULT 0,
  total_salary numeric(14, 2) NOT NULL DEFAULT 0,
  total_deduction numeric(14, 2) NOT NULL DEFAULT 0,
  take_home numeric(14, 2) NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_salary_slips_employee_id ON salary_slips(employee_id);
CREATE INDEX IF NOT EXISTS idx_salary_slips_pay_slip_month ON salary_slips(pay_slip_month);
CREATE INDEX IF NOT EXISTS idx_salary_slips_created_at ON salary_slips(created_at DESC);