-- =====================================================
-- Rubis KTO - OOP System Migration
-- Run this entire file in phpMyAdmin or MySQL CLI
-- =====================================================

-- Rate limiter (login attempts tracking)
CREATE TABLE IF NOT EXISTS `d_login_attempts` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ip_address` VARCHAR(45) NOT NULL,
    `attempted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `username_tried` VARCHAR(100) DEFAULT '',
    `user_agent` VARCHAR(500) DEFAULT '',
    INDEX `idx_ip_time` (`ip_address`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User report delivery preferences
CREATE TABLE IF NOT EXISTS `d_user_report_preferences` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `report_key` VARCHAR(100) NOT NULL COMMENT 'e.g. fuel_sales, profit_loss',
    `frequency` ENUM('daily', 'weekly', 'monthly') NOT NULL DEFAULT 'weekly',
    `day_of_week` TINYINT UNSIGNED DEFAULT NULL COMMENT '0=Sun, 1=Mon, ... 6=Sat',
    `day_of_month` TINYINT UNSIGNED DEFAULT NULL COMMENT '1-31',
    `format` ENUM('pdf', 'csv', 'excel') NOT NULL DEFAULT 'pdf',
    `recipients` VARCHAR(500) DEFAULT NULL COMMENT 'Comma-separated email addresses',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_user_report` (`user_id`, `report_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Mail logs
CREATE TABLE IF NOT EXISTS `d_mail_logs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `recipient` VARCHAR(200) NOT NULL,
    `subject` VARCHAR(500) NOT NULL,
    `body_preview` VARCHAR(200) DEFAULT NULL,
    `status` ENUM('sent', 'failed', 'queued') NOT NULL DEFAULT 'queued',
    `error` TEXT,
    `sent_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_status` (`status`),
    INDEX `idx_sent_at` (`sent_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Job queue (async email delivery, etc.)
CREATE TABLE IF NOT EXISTS `d_jobs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `queue` VARCHAR(100) NOT NULL DEFAULT 'default',
    `payload` LONGTEXT NOT NULL,
    `attempts` TINYINT UNSIGNED NOT NULL DEFAULT 0,
    `reserved_at` TIMESTAMP NULL,
    `available_at` TIMESTAMP NOT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_queue` (`queue`),
    INDEX `idx_available` (`available_at`),
    INDEX `idx_reserved` (`reserved_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Failed jobs
CREATE TABLE IF NOT EXISTS `d_failed_jobs` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `queue` VARCHAR(100) NOT NULL,
    `payload` LONGTEXT NOT NULL,
    `exception` TEXT NOT NULL,
    `failed_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User onboarding tour
CREATE TABLE IF NOT EXISTS `d_user_onboarding` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `tour_step` INT UNSIGNED NOT NULL DEFAULT 0,
    `completed_at` TIMESTAMP NULL,
    `skipped_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Cron jobs scheduler
DROP TABLE IF EXISTS `cron_log`;
DROP TABLE IF EXISTS `cron_jobs`;

CREATE TABLE `cron_jobs` (
    `job_id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `job_key` VARCHAR(100) NOT NULL UNIQUE,
    `title` VARCHAR(200) NOT NULL,
    `description` TEXT,
    `script_path` VARCHAR(500) NOT NULL,
    `schedule` VARCHAR(100) NOT NULL COMMENT '5-field cron expression',
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `timeout` INT UNSIGNED NOT NULL DEFAULT 300,
    `max_consecutive_failures` INT UNSIGNED NOT NULL DEFAULT 3,
    `notify_email` VARCHAR(200) DEFAULT NULL,
    `retention_days` INT UNSIGNED DEFAULT 90,
    `max_logs` INT UNSIGNED DEFAULT 1000,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_active` (`is_active`),
    INDEX `idx_key` (`job_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `cron_log` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `job_id` INT UNSIGNED NOT NULL,
    `status` ENUM('running', 'success', 'failed', 'timeout') NOT NULL DEFAULT 'running',
    `started_at` TIMESTAMP NULL,
    `finished_at` TIMESTAMP NULL,
    `output` MEDIUMTEXT,
    `error` TEXT,
    `pid` INT UNSIGNED DEFAULT NULL,
    INDEX `idx_job_status` (`job_id`, `status`),
    INDEX `idx_started` (`started_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed default cron jobs
INSERT INTO `cron_jobs` (`job_key`, `title`, `description`, `script_path`, `schedule`, `is_active`, `timeout`, `max_consecutive_failures`, `retention_days`, `max_logs`) VALUES
('cache-purge', 'Cache Purge', 'Delete expired cache files daily at 3 AM', 'cache-purge.php', '0 3 * * *', 1, 120, 3, 30, 100),
('report-delivery', 'Report Delivery', 'Send scheduled reports daily at 6 AM', 'report-delivery.php', '0 6 * * *', 1, 300, 3, 30, 100),
('session-cleanup', 'Session Cleanup', 'Delete expired sessions hourly', 'session-cleanup.php', '0 * * * *', 1, 60, 3, 7, 500),
('activity-log-archive', 'Activity Log Archive', 'Archive activity logs older than 90 days', 'activity-log-archive.php', '0 2 * * 0', 1, 300, 3, 90, 50)
ON DUPLICATE KEY UPDATE `title` = VALUES(`title`);