-- Migration: Create generated_payslips view for payslip display
-- Date: 2025-11-20
-- Description: Creates a database view that joins payslips with user and aggregate data
--              to provide a comprehensive view of generated payslips with employee names,
--              organization info, and time period details.

-- Drop view if it exists
DROP VIEW IF EXISTS generated_payslips;

-- Create generated_payslips view
CREATE OR REPLACE VIEW generated_payslips AS
SELECT
    p.payslip_id,
    p.user_id,
    p.time_clock_aggregate_id,
    p.gross_pay,
    p.net_pay,
    p.isDeleted,
    p.updatedAt,
    u.full_name AS user_name,
    o.name AS org_name,
    tca.start_date,
    tca.end_date,
    tca.total_payable_minutes,
    tca.total_regular_minutes,
    tca.total_overtime_minutes,
    tca.org_id
FROM payslips p
LEFT JOIN users u ON p.user_id = u.user_id
LEFT JOIN time_clock_aggregates tca ON p.time_clock_aggregate_id = tca.time_clock_aggregate_id
LEFT JOIN orgs o ON tca.org_id = o.org_id
WHERE p.isDeleted = false;

-- Add comment to the view
COMMENT ON VIEW generated_payslips IS 'Comprehensive view of payslips with user names, organization details, and time period information for display purposes';
