
-- 1. Departments table
CREATE TABLE public.departments (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL UNIQUE,
  name_bn text NOT NULL DEFAULT '',
  description text NOT NULL DEFAULT '',
  status text NOT NULL DEFAULT 'active',
  deleted_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE public.departments ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff view departments" ON public.departments FOR SELECT TO authenticated USING (public.is_staff(auth.uid()));
CREATE POLICY "Admins insert departments" ON public.departments FOR INSERT TO authenticated WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins update departments" ON public.departments FOR UPDATE TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins delete departments" ON public.departments FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER update_departments_updated_at BEFORE UPDATE ON public.departments FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

INSERT INTO public.departments (name, name_bn) VALUES
  ('Sales','বিক্রয়'),
  ('Accounts','হিসাব'),
  ('Warehouse','গুদাম'),
  ('Support','সহায়তা'),
  ('Marketing','বিপণন'),
  ('Management','ব্যবস্থাপনা'),
  ('Delivery','ডেলিভারি')
ON CONFLICT (name) DO NOTHING;

-- 2. Extra staff columns
ALTER TABLE public.staff
  ADD COLUMN IF NOT EXISTS staff_id text UNIQUE,
  ADD COLUMN IF NOT EXISTS department_id uuid REFERENCES public.departments(id) ON DELETE SET NULL,
  ADD COLUMN IF NOT EXISTS username text;

CREATE SEQUENCE IF NOT EXISTS public.staff_id_seq START 1;

CREATE OR REPLACE FUNCTION public.generate_staff_id()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = public
AS $$
BEGIN
  IF NEW.staff_id IS NULL OR NEW.staff_id = '' THEN
    NEW.staff_id := 'EMP-' || LPAD(nextval('public.staff_id_seq')::text, 6, '0');
  END IF;
  RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS staff_generate_id ON public.staff;
CREATE TRIGGER staff_generate_id BEFORE INSERT ON public.staff FOR EACH ROW EXECUTE FUNCTION public.generate_staff_id();

-- backfill existing
UPDATE public.staff SET staff_id = 'EMP-' || LPAD(nextval('public.staff_id_seq')::text, 6, '0')
WHERE staff_id IS NULL OR staff_id = '';

-- 3. Attendance
CREATE TABLE public.staff_attendance (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  staff_id uuid NOT NULL REFERENCES public.staff(id) ON DELETE CASCADE,
  date date NOT NULL DEFAULT current_date,
  check_in timestamptz,
  check_out timestamptz,
  status text NOT NULL DEFAULT 'present',
  late_minutes integer NOT NULL DEFAULT 0,
  note text NOT NULL DEFAULT '',
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(staff_id, date)
);
CREATE INDEX idx_staff_attendance_date ON public.staff_attendance(date);
CREATE INDEX idx_staff_attendance_staff ON public.staff_attendance(staff_id);
ALTER TABLE public.staff_attendance ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff view attendance" ON public.staff_attendance FOR SELECT TO authenticated USING (public.is_staff(auth.uid()));
CREATE POLICY "Admins manage attendance ins" ON public.staff_attendance FOR INSERT TO authenticated WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins manage attendance upd" ON public.staff_attendance FOR UPDATE TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins manage attendance del" ON public.staff_attendance FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER update_staff_attendance_updated_at BEFORE UPDATE ON public.staff_attendance FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

-- 4. Payroll
CREATE TABLE public.staff_payroll (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  staff_id uuid NOT NULL REFERENCES public.staff(id) ON DELETE CASCADE,
  month date NOT NULL,
  basic_salary numeric NOT NULL DEFAULT 0,
  bonus numeric NOT NULL DEFAULT 0,
  commission numeric NOT NULL DEFAULT 0,
  advance numeric NOT NULL DEFAULT 0,
  deduction numeric NOT NULL DEFAULT 0,
  net_payable numeric NOT NULL DEFAULT 0,
  paid_amount numeric NOT NULL DEFAULT 0,
  status text NOT NULL DEFAULT 'pending',
  payment_date date,
  note text NOT NULL DEFAULT '',
  created_by uuid,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(staff_id, month)
);
CREATE INDEX idx_staff_payroll_month ON public.staff_payroll(month);
CREATE INDEX idx_staff_payroll_staff ON public.staff_payroll(staff_id);
ALTER TABLE public.staff_payroll ENABLE ROW LEVEL SECURITY;
CREATE POLICY "Staff view payroll" ON public.staff_payroll FOR SELECT TO authenticated USING (public.is_staff(auth.uid()));
CREATE POLICY "Admins payroll ins" ON public.staff_payroll FOR INSERT TO authenticated WITH CHECK (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins payroll upd" ON public.staff_payroll FOR UPDATE TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins payroll del" ON public.staff_payroll FOR DELETE TO authenticated USING (public.has_role(auth.uid(),'admin'));
CREATE TRIGGER update_staff_payroll_updated_at BEFORE UPDATE ON public.staff_payroll FOR EACH ROW EXECUTE FUNCTION public.update_updated_at_column();

CREATE OR REPLACE FUNCTION public.staff_payroll_calc_fn()
RETURNS trigger
LANGUAGE plpgsql
SET search_path = public
AS $$
BEGIN
  NEW.net_payable := COALESCE(NEW.basic_salary,0) + COALESCE(NEW.bonus,0) + COALESCE(NEW.commission,0) - COALESCE(NEW.advance,0) - COALESCE(NEW.deduction,0);
  IF COALESCE(NEW.paid_amount,0) <= 0 THEN
    NEW.status := 'pending';
  ELSIF COALESCE(NEW.paid_amount,0) >= NEW.net_payable AND NEW.net_payable > 0 THEN
    NEW.status := 'paid';
  ELSE
    NEW.status := 'partial';
  END IF;
  RETURN NEW;
END;
$$;
CREATE TRIGGER staff_payroll_calc BEFORE INSERT OR UPDATE ON public.staff_payroll FOR EACH ROW EXECUTE FUNCTION public.staff_payroll_calc_fn();

-- 5. Storage bucket for staff photos
INSERT INTO storage.buckets (id, name, public) VALUES ('staff-photos','staff-photos', true)
ON CONFLICT (id) DO NOTHING;

CREATE POLICY "Staff photos public read" ON storage.objects FOR SELECT USING (bucket_id = 'staff-photos');
CREATE POLICY "Admins upload staff photos" ON storage.objects FOR INSERT TO authenticated WITH CHECK (bucket_id = 'staff-photos' AND public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins update staff photos" ON storage.objects FOR UPDATE TO authenticated USING (bucket_id = 'staff-photos' AND public.has_role(auth.uid(),'admin'));
CREATE POLICY "Admins delete staff photos" ON storage.objects FOR DELETE TO authenticated USING (bucket_id = 'staff-photos' AND public.has_role(auth.uid(),'admin'));
