-- ===============================================
-- SaniBook Audit Trail Migration
-- Date: 2026-02-14
-- Description: Creates audit_log table for tracking all admin actions
-- ===============================================

USE `sanibook_vol1`;

-- Audit Log Table
CREATE TABLE IF NOT EXISTS `audit_log` (
    `id` INT NOT NULL AUTO_INCREMENT,
    `action_type` VARCHAR(50) NOT NULL COMMENT 'user_edit, subscription_change, order_approved, order_rejected, token_adjustment, transaction_refund, password_change',
    `target_table` VARCHAR(50) DEFAULT NULL COMMENT 'e.g. users, user_subscriptions, token_orders',
    `target_id` INT DEFAULT NULL COMMENT 'ID of the affected row',
    `user_id` INT DEFAULT NULL COMMENT 'User being affected',
    `admin_id` INT DEFAULT NULL COMMENT 'Admin who performed the action',
    `old_values` JSON DEFAULT NULL COMMENT 'Previous state snapshot',
    `new_values` JSON DEFAULT NULL COMMENT 'New state snapshot',
    `note` TEXT DEFAULT NULL COMMENT 'Required for refunds, optional for others',
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_action_type` (`action_type`),
    KEY `idx_target` (`target_table`, `target_id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_admin` (`admin_id`),
    KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add profile enhancement columns to users table (safe with IF NOT EXISTS pattern)
-- These columns may already exist from earlier manual work; ALTER IGNORE handles gracefully.

-- Phone number
ALTER TABLE `users` ADD COLUMN `phone` VARCHAR(20) DEFAULT NULL AFTER `email`;

-- Bio
ALTER TABLE `users` ADD COLUMN `bio` TEXT DEFAULT NULL AFTER `phone`;

-- Categories association (JSON array of category IDs)
ALTER TABLE `users` ADD COLUMN `categories` JSON DEFAULT NULL AFTER `bio`;

-- Password reset fields
ALTER TABLE `users` ADD COLUMN `password_reset_token` VARCHAR(64) DEFAULT NULL;
ALTER TABLE `users` ADD COLUMN `password_reset_expires` DATETIME DEFAULT NULL;

-- Ensure essential columns exist (skip errors if already present)
-- Run these individually; they will error harmlessly if columns already exist:
-- ALTER TABLE `users` ADD COLUMN `role` VARCHAR(20) DEFAULT 'user' AFTER `password`;
-- ALTER TABLE `users` ADD COLUMN `is_verified` TINYINT(1) DEFAULT 0;
-- ALTER TABLE `users` ADD COLUMN `is_enterprise` TINYINT(1) DEFAULT 0;
-- ALTER TABLE `users` ADD COLUMN `county` VARCHAR(100) DEFAULT NULL;
-- ALTER TABLE `users` ADD COLUMN `sub_county` VARCHAR(100) DEFAULT NULL;
-- ALTER TABLE `users` ADD COLUMN `mobile` VARCHAR(20) DEFAULT NULL;
