CREATE TABLE IF NOT EXISTS `users` (
    `id` VARCHAR(50) PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(150) NOT NULL UNIQUE,
    `phone` VARCHAR(20) DEFAULT '',
    `password_hash` VARCHAR(255) NOT NULL,
    `created_at` DATETIME NOT NULL,
    
    `fampay_gmail` VARCHAR(150) DEFAULT '',
    `fampay_app_password_encrypted` VARCHAR(255) DEFAULT '',
    `fampay_upi_id` VARCHAR(100) DEFAULT '',
    `gmail_connected` TINYINT(1) DEFAULT 0,
    `google_linked` TINYINT(1) DEFAULT 0,
    
    `api_keys` JSON,
    `reset_chances` INT DEFAULT 100,
    `support_link` VARCHAR(255) DEFAULT '',
    `avatar` MEDIUMTEXT,
    
    `plan_id` VARCHAR(50) DEFAULT 'trial',
    `plan_label` VARCHAR(100) DEFAULT '1 Day Free Trial',
    `plan_expires_at` DATETIME,
    
    `total_requests` INT DEFAULT 0,
    `total_success` INT DEFAULT 0,
    `total_failed` INT DEFAULT 0,
    `total_amount_received` DECIMAL(10,2) DEFAULT 0.00,
    
    `status` VARCHAR(20) DEFAULT 'active',
    `webhook_url` TEXT DEFAULT '',
    `reset_token` VARCHAR(255) DEFAULT NULL,
    `reset_expires` INT DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS `orders` (
    `order_id` VARCHAR(50) PRIMARY KEY,
    `type` VARCHAR(50) DEFAULT 'api_order',
    `user_id` VARCHAR(50) NOT NULL,
    `upi_id` VARCHAR(100) DEFAULT '',
    `amount` DECIMAL(10,2) NOT NULL,
    `payable_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `status` VARCHAR(20) DEFAULT 'pending',
    
    `created_at` DATETIME NOT NULL,
    `created_at_timestamp` INT NOT NULL,
    `expires_at_timestamp` INT NOT NULL,
    
    `transaction_id` VARCHAR(100) DEFAULT '',
    `utr` VARCHAR(100) DEFAULT '',
    `sender_name` VARCHAR(100) DEFAULT '',
    `payment_time_ist` VARCHAR(50) DEFAULT '',
    `redirect_url` TEXT DEFAULT '',
    
    INDEX `idx_orders_user_id` (`user_id`),
    INDEX `idx_orders_status` (`status`),
    INDEX `idx_orders_created_at_timestamp` (`created_at_timestamp`)
);



CREATE TABLE IF NOT EXISTS `admin_settings` (
    `id` INT PRIMARY KEY DEFAULT 1,
    `gmail_id` VARCHAR(150) DEFAULT '',
    `app_password_encrypted` VARCHAR(255) DEFAULT '',
    `upi_id` VARCHAR(100) DEFAULT '',
    `custom_api_price` DECIMAL(10,2) DEFAULT 100.00,
    `reset_unlock_price` DECIMAL(10,2) DEFAULT 5.00,
    `base_url` VARCHAR(255) DEFAULT '',
    `demo_amount` DECIMAL(10,2) DEFAULT 1.00,
    `smtp_host` VARCHAR(255) DEFAULT '',
    `smtp_port` INT DEFAULT 587,
    `smtp_user` VARCHAR(255) DEFAULT '',
    `smtp_pass_encrypted` VARCHAR(255) DEFAULT '',
    `smtp_encryption` VARCHAR(50) DEFAULT 'tls',
    `smtp_from_email` VARCHAR(255) DEFAULT '',
    `smtp_from_name` VARCHAR(255) DEFAULT '',
    `enable_logging` TINYINT(1) DEFAULT 1
);

-- Insert default admin settings row
INSERT IGNORE INTO `admin_settings` (`id`) VALUES (1);

CREATE TABLE IF NOT EXISTS `system_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `created_at` DATETIME NOT NULL,
    `source` VARCHAR(50) NOT NULL,
    `message` TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS `payment_links` (
    `slug` VARCHAR(100) PRIMARY KEY,
    `user_id` VARCHAR(50) NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `payable_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `status` VARCHAR(20) DEFAULT 'pending',
    `created_at` INT NOT NULL,
    `expires_at` INT NOT NULL,
    
    INDEX `idx_pl_user_id` (`user_id`),
    INDEX `idx_pl_status` (`status`),
    INDEX `idx_pl_expires_at` (`expires_at`)
);

CREATE TABLE IF NOT EXISTS `imap_locks` (
    `user_id` VARCHAR(50) PRIMARY KEY,
    `locked_at` INT NOT NULL
);

CREATE TABLE IF NOT EXISTS `cron_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `ran_at` INT NOT NULL,
    `checked` INT DEFAULT 0,
    `verified` INT DEFAULT 0
);
CREATE TABLE IF NOT EXISTS `email_templates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `template_key` VARCHAR(100) UNIQUE NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `body` TEXT NOT NULL,
    `variables` VARCHAR(255) DEFAULT ''
);

