41 lines
1.6 KiB
PL/PgSQL
41 lines
1.6 KiB
PL/PgSQL
-- Create leave_balances table
|
|
CREATE TABLE public.leave_balances (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
employee_id UUID REFERENCES public.employees(id) ON DELETE CASCADE UNIQUE NOT NULL,
|
|
total_days DECIMAL(5,2) DEFAULT 0 NOT NULL, -- Total accrued days
|
|
used_days DECIMAL(5,2) DEFAULT 0 NOT NULL, -- Successfully used days
|
|
pending_days DECIMAL(5,2) DEFAULT 0 NOT NULL, -- Days currently in pending status
|
|
remaining_days DECIMAL(5,2) GENERATED ALWAYS AS (total_days - used_days) STORED,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
|
|
);
|
|
|
|
-- RLS for leave_balances
|
|
ALTER TABLE public.leave_balances ENABLE ROW LEVEL SECURITY;
|
|
|
|
CREATE POLICY "Users can view their own leave balance"
|
|
ON public.leave_balances
|
|
FOR SELECT TO authenticated
|
|
USING (
|
|
employee_id IN (
|
|
SELECT id FROM public.employees WHERE user_id = auth.uid()
|
|
)
|
|
);
|
|
|
|
-- Trigger to create a balance record when a new employee is added
|
|
CREATE OR REPLACE FUNCTION public.handle_new_employee_balance()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
INSERT INTO public.leave_balances (employee_id, total_days)
|
|
VALUES (NEW.id, 14); -- Defaulting to 14 days per year
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
CREATE TRIGGER on_employee_created
|
|
AFTER INSERT ON public.employees
|
|
FOR EACH ROW EXECUTE PROCEDURE public.handle_new_employee_balance();
|
|
|
|
-- Trigger updated_at for leave_balances
|
|
CREATE TRIGGER update_leave_balances_modtime BEFORE UPDATE ON public.leave_balances FOR EACH ROW EXECUTE PROCEDURE update_updated_at_column();
|