-- Pdfboxi.in MySQL / MariaDB schema for phpMyAdmin
-- Create an empty database first, select it, then Import this file.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS users (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 first_name VARCHAR(190) NOT NULL,last_name VARCHAR(190) NOT NULL,email VARCHAR(190) NOT NULL UNIQUE,
 dob VARCHAR(190) NOT NULL,pass_hash VARCHAR(255) NOT NULL,verified INT NOT NULL DEFAULT 0,
 created_at INT NOT NULL,last_login INT NULL,ip VARCHAR(45) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS otps (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) NOT NULL,purpose VARCHAR(190) NOT NULL,
 code_hash VARCHAR(255) NOT NULL,expires_at INT NOT NULL,attempts INT NOT NULL DEFAULT 0,created_at INT NOT NULL,
 KEY idx_otps_email(email,purpose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS rate_limit (
 k VARCHAR(190) NOT NULL PRIMARY KEY,cnt INT NOT NULL,reset_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS orders (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,order_id VARCHAR(190) NOT NULL UNIQUE,user_id INT NOT NULL,
 plan VARCHAR(190) NOT NULL,amount_paise INT NOT NULL,status VARCHAR(190) NOT NULL,payment_id VARCHAR(190) NULL,
 created_at INT NOT NULL,paid_at INT NULL,coupon_code VARCHAR(190) NULL,discount_paise INT NOT NULL DEFAULT 0,gateway VARCHAR(30) NOT NULL DEFAULT 'razorpay',gateway_order_id VARCHAR(190) NULL,
 KEY idx_orders_user(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS subscriptions (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,plan VARCHAR(190) NOT NULL,
 order_id VARCHAR(190) NOT NULL UNIQUE,starts_at INT NOT NULL,ends_at INT NOT NULL,cancelled INT NOT NULL DEFAULT 0,
 created_at INT NOT NULL,KEY idx_subs_user(user_id,ends_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS usage_log (
 k VARCHAR(190) NOT NULL,day VARCHAR(20) NOT NULL,cnt INT NOT NULL DEFAULT 0,PRIMARY KEY(k,day)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS admin_users (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,email VARCHAR(190) NOT NULL UNIQUE,pass_hash VARCHAR(255) NOT NULL,
 created_at INT NOT NULL,last_login INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS site_settings (
 setting_key VARCHAR(190) NOT NULL PRIMARY KEY,setting_value LONGTEXT NOT NULL,updated_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS site_tools (
 tool_id VARCHAR(190) NOT NULL PRIMARY KEY,enabled INT NOT NULL DEFAULT 1,updated_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS admin_logs (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,admin_email VARCHAR(190) NOT NULL,action VARCHAR(190) NOT NULL,
 details TEXT NULL,created_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS coupons (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,code VARCHAR(190) NOT NULL UNIQUE,discount_type VARCHAR(30) NOT NULL,
 discount_value INT NOT NULL,active INT NOT NULL DEFAULT 1,expires_at INT NULL,max_uses INT NULL,
 used_count INT NOT NULL DEFAULT 0,created_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS user_status (
 user_id INT NOT NULL PRIMARY KEY,blocked INT NOT NULL DEFAULT 0,updated_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS scheduled_jobs (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,job_type VARCHAR(190) NOT NULL,payload LONGTEXT NOT NULL,
 run_at INT NOT NULL,status VARCHAR(30) NOT NULL DEFAULT 'pending',created_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS reminder_log (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,user_id INT NOT NULL,subscription_id INT NOT NULL,
 days_before INT NOT NULL,sent_at INT NOT NULL,UNIQUE KEY uq_reminder(user_id,subscription_id,days_before)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS email_campaigns (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,subject VARCHAR(190) NOT NULL,body LONGTEXT NOT NULL,
 recipient_type VARCHAR(30) NOT NULL,recipient_value VARCHAR(190) NULL,scheduled_at INT NULL,
 status VARCHAR(30) NOT NULL,created_at INT NOT NULL,sent_at INT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tool_registry (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,slug VARCHAR(190) NOT NULL UNIQUE,title VARCHAR(190) NOT NULL,
 url TEXT NOT NULL,category VARCHAR(190) NULL,active INT NOT NULL DEFAULT 1,created_at INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;