58 lines
2.1 KiB
SQL
58 lines
2.1 KiB
SQL
|
-- Phase 7 Hardening: Constraints and Indexes
|
||
|
|
|
||
|
|
-- 1. Unique Active Payroll Run
|
||
|
|
-- Prevents duplicate runs for same Period, Ministry, OrgUnit, RunType unless older ones are Closed/Cancelled
|
||
|
|
CREATE UNIQUE INDEX IF NOT EXISTS idx_payroll_run_active_unique
|
||
|
|
ON payroll_run (period_id, ministry_id, org_unit_id, run_type)
|
||
|
|
WHERE status NOT IN ('CLOSED', 'CANCELLED');
|
||
|
|
|
||
|
|
-- 2. Contract Date Validation
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'check_contract_dates') THEN
|
||
|
|
ALTER TABLE agent_contract ADD CONSTRAINT check_contract_dates CHECK (start_date <= end_date OR end_date IS NULL);
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- 3. Performance Indexes
|
||
|
|
-- For Batch Fetching Absences (Check table existence first to avoid error if feature disabled)
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'absence') THEN
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_absence_agent_dates ON absence (agent_id, start_date, end_date);
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- For Batch Fetching Attendance
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'attendance_record') THEN
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_attendance_agent_date ON attendance_record (agent_id, date);
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- For Batch Fetching Budget Lines
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_budget_line_org_econ ON budget_line (org_unit_id, economic_class);
|
||
|
|
|
||
|
|
-- For Batch Fetching Contracts
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_agent_contract_agent_period ON agent_contract (agent_id, start_date, end_date);
|
||
|
|
|
||
|
|
-- 4. Date Integrity Constraints (P15)
|
||
|
|
-- Payroll Period Dates
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'chk_period_dates') THEN
|
||
|
|
ALTER TABLE payroll_period ADD CONSTRAINT chk_period_dates CHECK (start_date <= end_date);
|
||
|
|
END IF;
|
||
|
|
END $$;
|
||
|
|
|
||
|
|
-- Absence Dates
|
||
|
|
DO $$
|
||
|
|
BEGIN
|
||
|
|
IF EXISTS (SELECT FROM pg_tables WHERE schemaname = 'public' AND tablename = 'absence') THEN
|
||
|
|
IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'chk_absence_dates') THEN
|
||
|
|
ALTER TABLE absence ADD CONSTRAINT chk_absence_dates CHECK (start_date <= end_date);
|
||
|
|
END IF;
|
||
|
|
END IF;
|
||
|
|
END $$;
|