-- 1. Create Public Holidays Table
CREATE TABLE IF NOT EXISTS public.public_holidays (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    date DATE NOT NULL UNIQUE,
    name TEXT NOT NULL,
    is_recurring BOOLEAN DEFAULT false, -- If true, year is ignored (e.g., Oct 29)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Seed some recurring Turkish public holidays
INSERT INTO public.public_holidays (date, name, is_recurring) VALUES
('2024-01-01', 'Yılbaşı', true),
('2024-04-23', 'Ulusal Egemenlik ve Çocuk Bayramı', true),
('2024-05-01', 'Emek ve Dayanışma Günü', true),
('2024-05-19', 'Atatürk''ü Anma, Gençlik ve Spor Bayramı', true),
('2024-07-15', 'Demokrasi ve Milli Birlik Günü', true),
('2024-08-30', 'Zafer Bayramı', true),
('2024-10-29', 'Cumhuriyet Bayramı', true)
ON CONFLICT (date) DO NOTHING;

-- 2. Create Leave Types Table
CREATE TABLE IF NOT EXISTS public.leave_types (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    is_paid BOOLEAN DEFAULT true,
    requires_approval BOOLEAN DEFAULT true,
    is_deductible BOOLEAN DEFAULT true, -- If true, reduces standard balance
    only_admin_can_create BOOLEAN DEFAULT false, -- If true, only managers/admins can initiate
    color_code TEXT,
    display_order INTEGER DEFAULT 99, -- Sort order for UI
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Add column if it doesn't exist (for cases where table already existed)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='leave_types' AND column_name='display_order') THEN
        ALTER TABLE public.leave_types ADD COLUMN display_order INTEGER DEFAULT 99;
    END IF;
END $$;

-- Seed Leave Types with display_order
INSERT INTO public.leave_types (name, description, is_paid, requires_approval, is_deductible, only_admin_can_create, color_code, display_order) VALUES
('Yıllık İzin', 'Kıdeme bağlı hakedilen ücretli izin', true, true, true, false, '#4CAF50', 1),
('Mazeret İzni', 'Özel durumlar için kısa süreli izinler', true, true, true, false, '#FF9800', 2),
('Hastalık İzni', 'Raporlu olunan günler', true, true, false, false, '#F44336', 3),
('Evlilik İzni', 'Evlilik durumunda verilen 3 günlük yasal izin', true, true, false, false, '#E91E63', 4),
('Vefat İzni', 'Birinci derece yakın vefatı durumunda verilen 3 günlük yasal izin', true, true, false, false, '#607D8B', 5),
('İdari İzin', 'Yönetim tarafından verilen idari izin', true, true, false, true, '#2196F3', 6),
('İcap', 'Nöbet/İcap görevleri için verilen izin', true, true, false, true, '#9C27B0', 7)
ON CONFLICT (name) DO UPDATE SET display_order = EXCLUDED.display_order;

-- 3. Modify Leave Requests Table
-- First, add columns (handle case where they might already exist if re-run)
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='leave_requests' AND column_name='leave_type_id') THEN
        ALTER TABLE public.leave_requests ADD COLUMN leave_type_id UUID REFERENCES public.leave_types(id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='leave_requests' AND column_name='total_days') THEN
        ALTER TABLE public.leave_requests ADD COLUMN total_days DECIMAL(5,2);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='leave_requests' AND column_name='approved_by') THEN
        ALTER TABLE public.leave_requests ADD COLUMN approved_by UUID REFERENCES auth.users(id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='leave_requests' AND column_name='approval_date') THEN
        ALTER TABLE public.leave_requests ADD COLUMN approval_date TIMESTAMP WITH TIME ZONE;
    END IF;
END $$;

-- 4. Update Leave Balances Table
-- We need to drop the unique constraint on employee_id because now it's employee_id + leave_type_id + year
ALTER TABLE public.leave_balances DROP CONSTRAINT IF EXISTS leave_balances_employee_id_key;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='leave_balances' AND column_name='leave_type_id') THEN
        ALTER TABLE public.leave_balances ADD COLUMN leave_type_id UUID REFERENCES public.leave_types(id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='leave_balances' AND column_name='year') THEN
        ALTER TABLE public.leave_balances ADD COLUMN year INTEGER DEFAULT EXTRACT(YEAR FROM CURRENT_DATE);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns WHERE table_name='leave_balances' AND column_name='accrued_days') THEN
        ALTER TABLE public.leave_balances ADD COLUMN accrued_days DECIMAL(5,2) DEFAULT 0;
    END IF;
END $$;

-- Update existing data: map annual leave type to existing balances
DO $$
DECLARE
    annual_leave_id UUID;
BEGIN
    SELECT id INTO annual_leave_id FROM public.leave_types WHERE name = 'Yıllık İzin' LIMIT 1;
    
    UPDATE public.leave_balances SET leave_type_id = annual_leave_id WHERE leave_type_id IS NULL;
    UPDATE public.leave_requests SET leave_type_id = annual_leave_id WHERE leave_type_id IS NULL;
END $$;

-- Now add NOT NULL and UNIQUE constraint
ALTER TABLE public.leave_balances ALTER COLUMN leave_type_id SET NOT NULL;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'leave_balances_employee_type_year_unique') THEN
        ALTER TABLE public.leave_balances ADD CONSTRAINT leave_balances_employee_type_year_unique UNIQUE(employee_id, leave_type_id, year);
    END IF;
END $$;

-- 5. Helper Function: Calculate Leave Days
CREATE OR REPLACE FUNCTION public.calculate_leave_days(p_start_date DATE, p_end_date DATE)
RETURNS DECIMAL AS $$
DECLARE
    curr_date DATE;
    total_days DECIMAL := 0;
    is_holiday BOOLEAN;
BEGIN
    curr_date := p_start_date;
    WHILE curr_date <= p_end_date LOOP
        -- Sunday (0) is never counts. 
        -- Saturday logic: Depends on company, but generally counts in law. 
        -- We'll exclude Sundays and Public Holidays.
        IF EXTRACT(DOW FROM curr_date) != 0 THEN
            SELECT EXISTS(
                SELECT 1 FROM public.public_holidays 
                WHERE (is_recurring AND EXTRACT(MONTH FROM date) = EXTRACT(MONTH FROM curr_date) AND EXTRACT(DAY FROM date) = EXTRACT(DAY FROM curr_date))
                OR (NOT is_recurring AND date = curr_date)
            ) INTO is_holiday;
            
            IF NOT is_holiday THEN
                total_days := total_days + 1;
            END IF;
        END IF;
        curr_date := curr_date + 1;
    END LOOP;
    RETURN total_days;
END;
$$ LANGUAGE plpgsql;

-- 6. Trigger to auto-calculate total_days on leave_requests
CREATE OR REPLACE FUNCTION public.handle_leave_request_days()
RETURNS TRIGGER AS $$
BEGIN
    NEW.total_days := public.calculate_leave_days(NEW.start_date, NEW.end_date);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS tr_calculate_leave_days ON public.leave_requests;
CREATE TRIGGER tr_calculate_leave_days
BEFORE INSERT OR UPDATE OF start_date, end_date ON public.leave_requests
FOR EACH ROW EXECUTE PROCEDURE public.handle_leave_request_days();

-- 7. Seniority based Accrual Function
CREATE OR REPLACE FUNCTION public.calculate_annual_leave_entitlement(p_employee_id UUID)
RETURNS INTEGER AS $$
DECLARE
    v_hire_date DATE;
    v_years_service INTEGER;
BEGIN
    SELECT COALESCE(start_date, hire_date, created_at::date) INTO v_hire_date 
    FROM public.employees WHERE id = p_employee_id;
    
    v_years_service := EXTRACT(YEAR FROM age(CURRENT_DATE, v_hire_date));
    
    IF v_years_service < 1 THEN RETURN 0; -- No leave in first year
    ELSIF v_years_service < 5 THEN RETURN 14;
    ELSIF v_years_service < 15 THEN RETURN 20;
    ELSE RETURN 26;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 8. Updated Balance Trigger
-- We need to replace handle_new_employee_balance to handle new logic
CREATE OR REPLACE FUNCTION public.handle_new_employee_balance()
RETURNS TRIGGER AS $$
DECLARE
    annual_leave_id UUID;
BEGIN
    SELECT id INTO annual_leave_id FROM public.leave_types WHERE name = 'Yıllık İzin' LIMIT 1;
    
    INSERT INTO public.leave_balances (employee_id, leave_type_id, year, accrued_days, total_days)
    VALUES (NEW.id, annual_leave_id, EXTRACT(YEAR FROM CURRENT_DATE), 0, 0)
    ON CONFLICT DO NOTHING;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 9. Function to update balance based on leave request status
CREATE OR REPLACE FUNCTION public.update_leave_balance_on_status_change()
RETURNS TRIGGER AS $$
DECLARE
    v_is_deductible BOOLEAN;
    v_annual_leave_id UUID;
    v_year INTEGER;
BEGIN
    SELECT is_deductible INTO v_is_deductible FROM public.leave_types WHERE id = NEW.leave_type_id;
    
    IF v_is_deductible THEN
        v_year := EXTRACT(YEAR FROM NEW.start_date);
        
        -- Ensure balance record exists for this year/type
        INSERT INTO public.leave_balances (employee_id, leave_type_id, year)
        VALUES (NEW.employee_id, NEW.leave_type_id, v_year)
        ON CONFLICT (employee_id, leave_type_id, year) DO NOTHING;
        
        -- Update based on status
        IF NEW.status = 'approved' AND (OLD.status IS NULL OR OLD.status != 'approved') THEN
            UPDATE public.leave_balances 
            SET used_days = used_days + NEW.total_days,
                pending_days = CASE WHEN OLD.status = 'pending' THEN pending_days - NEW.total_days ELSE pending_days END
            WHERE employee_id = NEW.employee_id AND leave_type_id = NEW.leave_type_id AND year = v_year;
        ELSIF NEW.status = 'pending' AND (OLD.status IS NULL OR OLD.status != 'pending') THEN
            UPDATE public.leave_balances 
            SET pending_days = pending_days + NEW.total_days
            WHERE employee_id = NEW.employee_id AND leave_type_id = NEW.leave_type_id AND year = v_year;
        ELSIF (NEW.status = 'rejected' OR NEW.status = 'cancelled') AND OLD.status = 'pending' THEN
            UPDATE public.leave_balances 
            SET pending_days = pending_days - NEW.total_days
            WHERE employee_id = NEW.employee_id AND leave_type_id = NEW.leave_type_id AND year = v_year;
        ELSIF (NEW.status = 'cancelled') AND OLD.status = 'approved' THEN
            UPDATE public.leave_balances 
            SET used_days = used_days - NEW.total_days
            WHERE employee_id = NEW.employee_id AND leave_type_id = NEW.leave_type_id AND year = v_year;
        END IF;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 10. Update RLS for leave_balances (ensure proper access)
ALTER TABLE public.leave_balances ENABLE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS "Admins can do everything on leave_balances" ON public.leave_balances;
CREATE POLICY "Admins can do everything on leave_balances"
ON public.leave_balances
FOR ALL TO authenticated
USING (
    EXISTS (
        SELECT 1 FROM public.employees e
        JOIN public.roles r ON e.role_id = r.id
        WHERE e.user_id = auth.uid() AND r.name IN ('admin', 'manager')
    )
);

DROP POLICY IF EXISTS "Users can view their own leave balance" ON public.leave_balances;
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()
    )
);

DROP TRIGGER IF EXISTS tr_update_leave_balance ON public.leave_requests;
CREATE TRIGGER tr_update_leave_balance
AFTER INSERT OR UPDATE OF status ON public.leave_requests
FOR EACH ROW EXECUTE PROCEDURE public.update_leave_balance_on_status_change();