-- Create a joined audit log view: audits + audit_logs (+ audit_status name).
-- Use this view to see each log entry with full audit details (name, location, status, times, etc.).
--
-- audit_logs table may use either:
--   (A) status, status_changed_at  (used by the app repository; older typo was "starus")
--   (B) action, created_at         (as in db_schema.sql)
-- This migration uses (A). If your table has (B) only, replace the SELECT list with:
--   al.action AS action,
--   al.created_at AS status_changed_at

DROP VIEW IF EXISTS audit_log_joined;

CREATE VIEW audit_log_joined AS
SELECT
    al.id AS log_id,
    a.audit_id,
    a.auditor_id,
    a.audit_name,
    a.location,
    a.audit_status_id,
    ast.audit_status_name,
    a.created_at AS audit_created_at,
    a.observed_at,
    a.country_id,
    a.department_id,
    a.start_time,
    a.end_time,
    al.user_id,
    al.`status` AS action,
    al.status_changed_at
FROM audit_logs al
INNER JOIN audits a ON a.audit_id = al.audit_id
LEFT JOIN audit_status ast ON a.audit_status_id = ast.audit_status_id;

-- If your audit_logs has "action" and "created_at" instead of "status" and "status_changed_at", run this instead:
--
-- DROP VIEW IF EXISTS audit_log_joined;
-- CREATE VIEW audit_log_joined AS
-- SELECT
--     al.id AS log_id,
--     a.audit_id,
--     a.auditor_id,
--     a.audit_name,
--     a.location,
--     a.audit_status_id,
--     ast.audit_status_name,
--     a.created_at AS audit_created_at,
--     a.observed_at,
--     a.country_id,
--     a.department_id,
--     a.start_time,
--     a.end_time,
--     al.user_id,
--     al.action,
--     al.created_at AS status_changed_at
-- FROM audit_logs al
-- INNER JOIN audits a ON a.audit_id = al.audit_id
-- LEFT JOIN audit_status ast ON a.audit_status_id = ast.audit_status_id;
