92 lines
3.3 KiB
PL/PgSQL
92 lines
3.3 KiB
PL/PgSQL
-- Migration: Update reports for pay period support and snapshot labor amounts
|
|
-- Reports now cover a date range and store calculated labor_share at time of addition
|
|
|
|
-- 1. Add status enum for report workflow
|
|
CREATE TYPE report_status AS ENUM ('DRAFT', 'FINALIZED', 'PAID');
|
|
|
|
-- 2. Alter reports table: date → start_date/end_date, add status
|
|
ALTER TABLE reports
|
|
ADD COLUMN start_date DATE,
|
|
ADD COLUMN end_date DATE,
|
|
ADD COLUMN status report_status NOT NULL DEFAULT 'DRAFT';
|
|
|
|
-- Migrate existing data (use date as both start and end)
|
|
UPDATE reports SET start_date = date, end_date = date WHERE start_date IS NULL;
|
|
|
|
-- Make date columns required
|
|
ALTER TABLE reports
|
|
ALTER COLUMN start_date SET NOT NULL,
|
|
ALTER COLUMN end_date SET NOT NULL;
|
|
|
|
-- Drop old column and constraint
|
|
ALTER TABLE reports DROP CONSTRAINT IF EXISTS reports_team_profile_id_date_key;
|
|
ALTER TABLE reports DROP COLUMN date;
|
|
|
|
-- Add check: end_date >= start_date
|
|
ALTER TABLE reports ADD CONSTRAINT reports_date_range_check
|
|
CHECK (end_date >= start_date);
|
|
|
|
-- Index for querying by period
|
|
CREATE INDEX idx_reports_period ON reports(start_date, end_date);
|
|
CREATE INDEX idx_reports_status ON reports(status);
|
|
|
|
-- 3. Add labor_share snapshot to report_services
|
|
-- This stores the calculated amount at time of addition (immutable for payroll)
|
|
ALTER TABLE report_services
|
|
ADD COLUMN labor_share NUMERIC(10,2) NOT NULL DEFAULT 0.00;
|
|
|
|
-- Remove default after backfill
|
|
ALTER TABLE report_services ALTER COLUMN labor_share DROP DEFAULT;
|
|
|
|
-- 4. Add labor_share snapshot to report_projects
|
|
ALTER TABLE report_projects
|
|
ADD COLUMN labor_share NUMERIC(10,2) NOT NULL DEFAULT 0.00;
|
|
|
|
ALTER TABLE report_projects ALTER COLUMN labor_share DROP DEFAULT;
|
|
|
|
-- 5. Prevent same service in multiple reports for same team member
|
|
-- A completed service should only be paid once per team member
|
|
-- Using trigger-based approach since PostgreSQL doesn't support subqueries in index expressions
|
|
|
|
-- Create a function to check uniqueness
|
|
CREATE OR REPLACE FUNCTION check_report_service_unique()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM report_services rs
|
|
JOIN reports r ON rs.report_id = r.id
|
|
WHERE rs.service_id = NEW.service_id
|
|
AND r.team_profile_id = (SELECT team_profile_id FROM reports WHERE id = NEW.report_id)
|
|
AND rs.id IS DISTINCT FROM NEW.id
|
|
) THEN
|
|
RAISE EXCEPTION 'Service % is already in another report for this team member', NEW.service_id;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_report_service_unique
|
|
BEFORE INSERT OR UPDATE ON report_services
|
|
FOR EACH ROW EXECUTE FUNCTION check_report_service_unique();
|
|
|
|
-- Same for projects
|
|
CREATE OR REPLACE FUNCTION check_report_project_unique()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1 FROM report_projects rp
|
|
JOIN reports r ON rp.report_id = r.id
|
|
WHERE rp.project_id = NEW.project_id
|
|
AND r.team_profile_id = (SELECT team_profile_id FROM reports WHERE id = NEW.report_id)
|
|
AND rp.id IS DISTINCT FROM NEW.id
|
|
) THEN
|
|
RAISE EXCEPTION 'Project % is already in another report for this team member', NEW.project_id;
|
|
END IF;
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER trg_report_project_unique
|
|
BEFORE INSERT OR UPDATE ON report_projects
|
|
FOR EACH ROW EXECUTE FUNCTION check_report_project_unique();
|