-- PORTMAN - permanent change log for payment vouchers and credit notes
-- Run ONCE in phpMyAdmin (database: the live Portman database) -> SQL tab -> paste -> Go.
-- NO DELETE: after this, a payment voucher or credit note can NEVER be deleted, from any page or from
-- phpMyAdmin. It must be CANCELLED (status = 'cancelled') so it stays on record and can be seen.
-- The change log itself can never be changed or deleted either.
-- After this, EVERY insert / change on these two tables is recorded by the database itself,
-- whichever page made it (trace.php, records_dashboard.php, border_finance_dashboard.php, the hub...).
-- Nobody can switch it off from the website. The log has no edit or delete anywhere in the system.
-- The person is taken from @app_user_id / @app_user_name, which includes/db_connect.php sets (see db_connect_add.php).
-- A change made with nobody logged in is saved with user_id 0 and shows in the hub as NO LOGIN.

CREATE TABLE IF NOT EXISTS payment_audit_log (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    tbl         VARCHAR(40)  NOT NULL,
    row_id      INT          NOT NULL,
    action      VARCHAR(10)  NOT NULL,
    user_id     INT          NOT NULL DEFAULT 0,
    user_name   VARCHAR(120) NULL,
    page        VARCHAR(120) NULL,
    ip          VARCHAR(45)  NULL,
    old_values  TEXT NULL,
    new_values  TEXT NULL,
    changed_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY k_row (tbl, row_id),
    KEY k_user (user_id),
    KEY k_time (changed_at)
);

DROP TRIGGER IF EXISTS pv_audit_ins;
DROP TRIGGER IF EXISTS pv_audit_upd;
DROP TRIGGER IF EXISTS pv_audit_del;
DROP TRIGGER IF EXISTS cn_audit_ins;
DROP TRIGGER IF EXISTS cn_audit_upd;
DROP TRIGGER IF EXISTS cn_audit_del;
DROP TRIGGER IF EXISTS pv_no_delete;
DROP TRIGGER IF EXISTS cn_no_delete;
DROP TRIGGER IF EXISTS log_no_update;
DROP TRIGGER IF EXISTS log_no_delete;
DROP TRIGGER IF EXISTS ov_no_delete;
DROP TRIGGER IF EXISTS ovh_no_change;
DROP TRIGGER IF EXISTS ovh_no_delete;

DELIMITER $$

-- ---------------- payment_vouchers ----------------
CREATE TRIGGER pv_audit_ins AFTER INSERT ON payment_vouchers FOR EACH ROW
BEGIN
    INSERT INTO payment_audit_log (tbl, row_id, action, user_id, user_name, page, ip, new_values)
    VALUES ('payment_vouchers', NEW.id, 'insert', COALESCE(@app_user_id, 0), @app_user_name, @app_page, @app_ip,
        JSON_OBJECT('voucher_number', NEW.voucher_number, 'status', NEW.status, 'amount', NEW.amount, 'currency', NEW.currency,
                    'truck_no', NEW.truck_no, 'cn_number', NEW.cn_number, 'requested_by', NEW.requested_by, 'receiver_name', NEW.receiver_name,
                    'receiver_phone', NEW.receiver_phone, 'reason', NEW.reason));
END$$

CREATE TRIGGER pv_audit_upd AFTER UPDATE ON payment_vouchers FOR EACH ROW
BEGIN
    DECLARE o TEXT; DECLARE n TEXT;
    SET o = JSON_OBJECT('status', OLD.status, 'amount', OLD.amount, 'currency', OLD.currency, 'truck_no', OLD.truck_no, 'cn_number', OLD.cn_number,
                        'requested_by', OLD.requested_by, 'approved_by_1', OLD.approved_by_1, 'approved_by_2', OLD.approved_by_2, 'approved_by_3', OLD.approved_by_3,
                        'rejected_by', OLD.rejected_by, 'credit_note_number', OLD.credit_note_number, 'receiver_name', OLD.receiver_name,
                        'receiver_phone', OLD.receiver_phone, 'reason', OLD.reason);
    SET n = JSON_OBJECT('status', NEW.status, 'amount', NEW.amount, 'currency', NEW.currency, 'truck_no', NEW.truck_no, 'cn_number', NEW.cn_number,
                        'requested_by', NEW.requested_by, 'approved_by_1', NEW.approved_by_1, 'approved_by_2', NEW.approved_by_2, 'approved_by_3', NEW.approved_by_3,
                        'rejected_by', NEW.rejected_by, 'credit_note_number', NEW.credit_note_number, 'receiver_name', NEW.receiver_name,
                        'receiver_phone', NEW.receiver_phone, 'reason', NEW.reason);
    IF NOT (o <=> n) THEN
        INSERT INTO payment_audit_log (tbl, row_id, action, user_id, user_name, page, ip, old_values, new_values)
        VALUES ('payment_vouchers', NEW.id, 'update', COALESCE(@app_user_id, 0), @app_user_name, @app_page, @app_ip, o, n);
    END IF;
END$$

CREATE TRIGGER pv_no_delete BEFORE DELETE ON payment_vouchers FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deleting a payment voucher is not allowed. Cancel it instead (status = cancelled) so it stays on record.';
END$$

-- ---------------- credit_notes ----------------
CREATE TRIGGER cn_audit_ins AFTER INSERT ON credit_notes FOR EACH ROW
BEGIN
    INSERT INTO payment_audit_log (tbl, row_id, action, user_id, user_name, page, ip, new_values)
    VALUES ('credit_notes', NEW.id, 'insert', COALESCE(@app_user_id, 0), @app_user_name, @app_page, @app_ip,
        JSON_OBJECT('credit_note_number', NEW.credit_note_number, 'payment_status', NEW.payment_status, 'amount', NEW.amount,
                    'original_amount', NEW.original_amount, 'currency', NEW.currency, 'horse_number', NEW.horse_number, 'cn_number', NEW.cn_number,
                    'assigned_agent', NEW.assigned_agent, 'recipient_name', NEW.recipient_name, 'office_created_by', NEW.office_created_by));
END$$

CREATE TRIGGER cn_audit_upd AFTER UPDATE ON credit_notes FOR EACH ROW
BEGIN
    DECLARE o TEXT; DECLARE n TEXT;
    SET o = JSON_OBJECT('payment_status', OLD.payment_status, 'amount', OLD.amount, 'original_amount', OLD.original_amount, 'currency', OLD.currency,
                        'busy_reference_number', OLD.busy_reference_number, 'assigned_agent', OLD.assigned_agent, 'horse_number', OLD.horse_number,
                        'cn_number', OLD.cn_number, 'recipient_name', OLD.recipient_name, 'recipient_phone', OLD.recipient_phone, 'office_created_by', OLD.office_created_by,
                        'cancelled_by', OLD.cancelled_by, 'cancel_reason', OLD.cancel_reason, 'restored_by', OLD.restored_by, 'restore_reason', OLD.restore_reason);
    SET n = JSON_OBJECT('payment_status', NEW.payment_status, 'amount', NEW.amount, 'original_amount', NEW.original_amount, 'currency', NEW.currency,
                        'busy_reference_number', NEW.busy_reference_number, 'assigned_agent', NEW.assigned_agent, 'horse_number', NEW.horse_number,
                        'cn_number', NEW.cn_number, 'recipient_name', NEW.recipient_name, 'recipient_phone', NEW.recipient_phone, 'office_created_by', NEW.office_created_by,
                        'cancelled_by', NEW.cancelled_by, 'cancel_reason', NEW.cancel_reason, 'restored_by', NEW.restored_by, 'restore_reason', NEW.restore_reason);
    IF NOT (o <=> n) THEN
        INSERT INTO payment_audit_log (tbl, row_id, action, user_id, user_name, page, ip, old_values, new_values)
        VALUES ('credit_notes', NEW.id, 'update', COALESCE(@app_user_id, 0), @app_user_name, @app_page, @app_ip, o, n);
    END IF;
END$$

CREATE TRIGGER cn_no_delete BEFORE DELETE ON credit_notes FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deleting a credit note is not allowed. Cancel it instead (payment_status = cancelled) so it stays on record.';
END$$

-- ---------------- the log itself: nobody can edit or remove it ----------------
CREATE TRIGGER log_no_update BEFORE UPDATE ON payment_audit_log FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The payment change log cannot be changed.';
END$$

CREATE TRIGGER log_no_delete BEFORE DELETE ON payment_audit_log FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'The payment change log cannot be deleted.';
END$$

-- ---------------- agent opening balances: cancel only, history locked ----------------
-- (records_dashboard.php creates these tables the first time it opens; open that page once BEFORE running this file)
CREATE TRIGGER ov_no_delete BEFORE DELETE ON agent_balance_overrides FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Deleting an opening balance is not allowed. Cancel it instead.';
END$$

CREATE TRIGGER ovh_no_change BEFORE UPDATE ON agent_balance_override_history FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Opening balance history cannot be changed.';
END$$

CREATE TRIGGER ovh_no_delete BEFORE DELETE ON agent_balance_override_history FOR EACH ROW
BEGIN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Opening balance history cannot be deleted.';
END$$

DELIMITER ;