-- Update admin_all_tasks view to include custom_task_id for month-based sequential task numbering

DROP VIEW IF EXISTS admin_all_tasks;

CREATE VIEW admin_all_tasks AS
SELECT 
    t.task_id,
    t.custom_task_id,
    t.request_id,
    t.assigned_staff_id,
    t.assigned_by_admin_id,
    t.scheduled_date,
    t.scheduled_time,
    t.status AS task_status,
    t.created_at AS task_created_at,
    t.updated_at AS task_updated_at,
    COALESCE(staff_user.full_name, 'Unassigned') AS staff_name,
    COALESCE(staff_user.email, '') AS staff_email,
    customer_user.full_name AS customer_name,
    customer_user.email AS customer_email,
    sr.customer_id,
    sr.service_id,
    sr.property_address,
    sr.pest_type,
    sr.severity_level,
    sr.preferred_date,
    sr.preferred_time,
    sr.status AS request_status,
    s.service_name,
    s.description AS service_description,
    p.payment_id,
    p.payment_status,
    p.payment_method,
    p.due_date AS payment_due_date
FROM tasks t
LEFT JOIN service_requests sr ON t.request_id = sr.request_id
LEFT JOIN users customer_user ON sr.customer_id = customer_user.user_id
LEFT JOIN users staff_user ON t.assigned_staff_id = staff_user.user_id
LEFT JOIN services s ON sr.service_id = s.service_id
LEFT JOIN payments p ON sr.request_id = p.request_id;
