/*
 TechStackBase – Audit Logs v2
 Version: v1.0.0
 Purpose: Immutable, tamper-evident audit ledger

 DESIGN PRINCIPLES
 -----------------
 • Append-only (NO updates, NO deletes)
 • Hash-chained (prev_hash → hash)
 • HMAC signed (server secret)
 • Forensic-grade integrity verification
 • High-volume safe (indexed)
*/

/* ============================================================
   AUDIT LOGS TABLE
   ============================================================ */

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    /* Actor context */
    user_id BIGINT UNSIGNED NULL,
    company_id BIGINT UNSIGNED NULL,

    /* Event classification */
    module_code VARCHAR(64) NOT NULL,
    action VARCHAR(64) NOT NULL,

    /* Optional structured payload (JSON) */
    payload JSON NULL,

    /* Request metadata */
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(255) NOT NULL,

    /* Tamper-evident chain */
    prev_hash CHAR(64) NULL,
    hash CHAR(64) NOT NULL,

    /* Timestamp */
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    /* Indexes for performance */
    INDEX idx_audit_user (user_id),
    INDEX idx_audit_company (company_id),
    INDEX idx_audit_module (module_code),
    INDEX idx_audit_action (action),
    INDEX idx_audit_created (created_at),

    /* Foreign keys (soft integrity – no cascades) */
    CONSTRAINT fk_audit_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_audit_company
        FOREIGN KEY (company_id) REFERENCES companies(id)
        ON DELETE SET NULL
) ENGINE=InnoDB
  ROW_FORMAT=DYNAMIC
  COMMENT='Immutable audit ledger (hash chained)';

/* ============================================================
   SECURITY HARDENING (OPTIONAL BUT RECOMMENDED)
   ============================================================ */

/*
 IMPORTANT:
 These triggers PREVENT updates/deletes at the DB level.
 Only INSERTs are allowed.
*/

DELIMITER $$

CREATE TRIGGER audit_logs_no_update
BEFORE UPDATE ON audit_logs
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Audit logs are immutable';
END$$

CREATE TRIGGER audit_logs_no_delete
BEFORE DELETE ON audit_logs
FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Audit logs are immutable';
END$$

DELIMITER ;

/* ============================================================
   NOTES
   ============================================================

 • prev_hash of the first record MUST be NULL
 • hash = HMAC_SHA256(
       SHA256(canonical_row_data),
       SERVER_SECRET
   )
 • Verification is done in audit/verify.php
 • Any tampering breaks the chain immediately
 • Triggers protect against accidental admin edits

 ============================================================ */
