-- Activity Log table for user activity tracking and revert capability
-- Run this migration before deploying the new code

CREATE TABLE IF NOT EXISTS activity_log (
    id           character(26) PRIMARY KEY DEFAULT generate_ulid(),
    table_name   character varying(100) NOT NULL,
    record_id    character(26) NOT NULL,
    action       character varying(20) NOT NULL,
    old_data     jsonb,
    new_data     jsonb,
    cascade_data jsonb,
    user_id      character(26) NOT NULL,
    user_name    character varying(255) NOT NULL,
    merchant_id  character(26),
    created_on   timestamp without time zone DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_activity_log_table_name ON activity_log(table_name);
CREATE INDEX IF NOT EXISTS idx_activity_log_record_id ON activity_log(record_id);
CREATE INDEX IF NOT EXISTS idx_activity_log_user_id ON activity_log(user_id);
CREATE INDEX IF NOT EXISTS idx_activity_log_created_on ON activity_log(created_on);
CREATE INDEX IF NOT EXISTS idx_activity_log_action ON activity_log(action);