-- View: generated_payslips
-- Purpose: Provides a comprehensive view of payslips with user, org, and time period information
-- Used by: Payroll module Payslips tab

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
ORDER BY p.updatedAt DESC;

-- Grant appropriate permissions
-- GRANT SELECT ON generated_payslips TO your_app_user;
