-- ============================================================
-- AI Auto Reply Marketing - Full Database Schema
-- Target: MySQL 5.7+ / MariaDB / cPanel phpMyAdmin
-- Stack: Core PHP + MySQL (no framework, no Composer)
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+05:30';

-- ------------------------------------------------------------
-- 1. Users Table (auth + license + device lock combined, matches source ZIP pattern)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `full_name` VARCHAR(150) DEFAULT '',
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) DEFAULT '',
  `phone_number` VARCHAR(50) DEFAULT '',
  `business_name` VARCHAR(150) DEFAULT '',
  `gst_number` VARCHAR(50) DEFAULT '',
  `logo_url` VARCHAR(500) DEFAULT '',
  `photo_url` VARCHAR(500) DEFAULT '',
  `address` VARCHAR(255) DEFAULT '',
  `website_url` VARCHAR(255) DEFAULT '',
  `role` ENUM('USER','ADMIN','SUPER_ADMIN') DEFAULT 'USER',
  `license_key` VARCHAR(100) DEFAULT 'TRIAL-2DAYS',
  `validity_days` INT DEFAULT 2,
  `activated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `is_blocked` TINYINT(1) DEFAULT 0,
  `is_email_verified` TINYINT(1) DEFAULT 1,
  `verification_token` VARCHAR(255) DEFAULT NULL,
  `reset_token` VARCHAR(255) DEFAULT NULL,
  `reset_token_expiry` BIGINT DEFAULT NULL,
  `active_device_id` VARCHAR(255) DEFAULT NULL,
  `last_login` TIMESTAMP NULL DEFAULT NULL,
  `referred_by_code` VARCHAR(100) DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_users_email` (`email`),
  INDEX `idx_users_status` (`is_blocked`, `validity_days`),
  INDEX `idx_users_role` (`role`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `users` (`id`, `full_name`, `email`, `password_hash`, `role`, `license_key`, `validity_days`, `is_blocked`, `is_email_verified`)
VALUES (1, 'Master Administrator', 'admin@gmail.com', '$2y$10$PNrJlYILLSREgNKS4UsMcuUvkviJc1DNyNSYV8OY8m32bvri0zKR.', 'SUPER_ADMIN', 'VIP-UNLIMITED', 3650, 0, 1);
-- Default login: admin@gmail.com / Admin@123 — hash verified with password_verify() before shipping.
-- CHANGE THIS PASSWORD after your first login.

-- ------------------------------------------------------------
-- 2. Email OTPs (registration verification)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `email_otps` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL,
  `otp` VARCHAR(20) NOT NULL,
  `expires_at` DATETIME NOT NULL,
  `is_used` TINYINT(1) DEFAULT 0,
  `attempts` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_email_otp` (`email`, `is_used`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 3. Password Resets (forgot-password OTP, separate from registration OTP)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `password_resets` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL,
  `otp` VARCHAR(20) NOT NULL,
  `token` VARCHAR(255) DEFAULT NULL,
  `expires_at` DATETIME NOT NULL,
  `is_used` TINYINT(1) DEFAULT 0,
  `attempts` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_pwd_reset_lookup` (`email`, `otp`, `is_used`, `expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 4. SMTP Settings (single-row config)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `smtp_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `smtp_host` VARCHAR(255) NOT NULL DEFAULT '',
  `smtp_port` INT NOT NULL DEFAULT 465,
  `smtp_encryption` ENUM('SSL','TLS','NONE') DEFAULT 'SSL',
  `smtp_username` VARCHAR(255) NOT NULL DEFAULT '',
  `smtp_password` VARCHAR(255) NOT NULL DEFAULT '',
  `sender_name` VARCHAR(255) NOT NULL DEFAULT 'AI Auto Reply Marketing',
  `sender_email` VARCHAR(255) NOT NULL DEFAULT '',
  `is_active` TINYINT(1) DEFAULT 1,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `smtp_settings` (`id`, `smtp_host`, `smtp_port`, `smtp_username`, `sender_name`, `sender_email`)
VALUES (1, 'mail.yourdomain.com', 465, 'support@yourdomain.com', 'AI Auto Reply Support', 'support@yourdomain.com');

-- ------------------------------------------------------------
-- 5. Subscriptions / Pricing Plans
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `subscriptions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `plan_name` VARCHAR(150) NOT NULL,
  `price` DECIMAL(10,2) NOT NULL,
  `validity_days` INT NOT NULL,
  `description` TEXT,
  `is_active` TINYINT(1) DEFAULT 1,
  `display_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `subscriptions` (`id`, `plan_name`, `price`, `validity_days`, `description`, `display_order`) VALUES
(1, '1 Month Pro Plan', 199.00, 30, '30 Days Full Access + Call Auto Reply + WhatsApp Marketing Templates', 1),
(2, '6 Month Growth Plan', 499.00, 180, '180 Days Unlimited Marketing & Broadcast Auto Response', 2),
(3, '1 Year VIP Plan', 899.00, 365, '365 Days Premium VIP Access + Priority Support', 3);

-- ------------------------------------------------------------
-- 6. App Settings (single-row config, legal pages, referral, support info)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `app_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `app_name` VARCHAR(150) DEFAULT 'AI Auto Reply Marketing',
  `support_email` VARCHAR(191) DEFAULT '',
  `support_whatsapp` VARCHAR(50) DEFAULT '',
  `support_upi_id` VARCHAR(100) DEFAULT '',
  `privacy_policy` LONGTEXT,
  `terms` LONGTEXT,
  `about_us` LONGTEXT,
  `contact_info` LONGTEXT,
  `announcement` TEXT,
  `facebook_url` VARCHAR(255) DEFAULT '',
  `instagram_url` VARCHAR(255) DEFAULT '',
  `youtube_url` VARCHAR(255) DEFAULT '',
  `telegram_url` VARCHAR(255) DEFAULT '',
  `whatsapp_url` VARCHAR(255) DEFAULT '',
  `linkedin_url` VARCHAR(255) DEFAULT '',
  `twitter_url` VARCHAR(255) DEFAULT '',
  `referral_commission` DECIMAL(10,2) DEFAULT 100.00,
  `default_trial_days` INT DEFAULT 2,
  `gemini_api_key` VARCHAR(255) DEFAULT '',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `app_settings` (`id`, `app_name`, `support_email`, `support_whatsapp`, `privacy_policy`, `terms`, `about_us`, `contact_info`)
VALUES (1, 'AI Auto Reply Marketing', 'support@yourdomain.com', '910000000000',
'Privacy Policy for AI Auto Reply Marketing. We respect your privacy and only use call/message access for your configured auto replies.',
'Terms and Conditions for AI Auto Reply Marketing. Automated replies are sent as configured by the user.',
'AI Auto Reply Marketing is an automated customer engagement platform for calls, SMS, and WhatsApp.',
'Contact support via Email or WhatsApp for assistance or custom plan renewals.');

-- ------------------------------------------------------------
-- 7. Audit Logs (admin action tracking)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `admin_email` VARCHAR(191) DEFAULT 'SYSTEM',
  `action` VARCHAR(150) NOT NULL,
  `target` VARCHAR(191) DEFAULT '',
  `details` TEXT,
  `ip_address` VARCHAR(64) DEFAULT '',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_audit_email` (`admin_email`),
  INDEX `idx_audit_action` (`action`),
  INDEX `idx_audit_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 8. Templates (auto-reply message templates)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `templates` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_email` VARCHAR(191) DEFAULT '',
  `title` VARCHAR(255) NOT NULL,
  `message_text` TEXT NOT NULL,
  `whatsapp_message_text` TEXT DEFAULT NULL,
  `event_type` VARCHAR(50) DEFAULT 'MISSED_CALL',
  `channel` VARCHAR(50) DEFAULT 'SIM_SMS',
  `category` VARCHAR(100) DEFAULT 'General',
  `media_type` VARCHAR(30) DEFAULT 'IMAGE',
  `link_url` VARCHAR(255) DEFAULT '',
  `attachment_url` VARCHAR(500) DEFAULT '',
  `call_button_number` VARCHAR(50) DEFAULT '',
  `location_text` VARCHAR(255) DEFAULT '',
  `notes` VARCHAR(255) DEFAULT '',
  `is_favorite` TINYINT(1) DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `is_global` TINYINT(1) DEFAULT 0 COMMENT 'admin-created preset visible to all users',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_user_template` (`user_email`),
  INDEX `idx_template_global` (`is_global`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 9. Call Logs
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `call_logs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_email` VARCHAR(191) DEFAULT '',
  `phone_number` VARCHAR(100) NOT NULL,
  `caller_name` VARCHAR(150) DEFAULT '',
  `call_type` VARCHAR(30) DEFAULT 'MISSED_CALL',
  `status` VARCHAR(100) DEFAULT 'SMS Sent',
  `channel_used` VARCHAR(50) DEFAULT 'SIM_SMS',
  `template_title` VARCHAR(255) DEFAULT '',
  `message_content` TEXT,
  `event_timestamp` BIGINT NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user_log` (`user_email`),
  INDEX `idx_log_status` (`status`),
  INDEX `idx_log_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 10. Blacklist
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `blacklists` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_email` VARCHAR(191) DEFAULT '',
  `phone_number` VARCHAR(100) NOT NULL,
  `contact_name` VARCHAR(150) DEFAULT '',
  `reason` VARCHAR(255) DEFAULT '',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user_blacklist` (`user_email`, `phone_number`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 11. Campaigns (marketing broadcast)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `campaigns` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_email` VARCHAR(191) DEFAULT '',
  `title` VARCHAR(255) NOT NULL,
  `channel` VARCHAR(50) DEFAULT 'WHATSAPP_BROADCAST',
  `message_template` TEXT NOT NULL,
  `total_contacts` INT DEFAULT 0,
  `sent_count` INT DEFAULT 0,
  `status` VARCHAR(30) DEFAULT 'DRAFT',
  `media_url` VARCHAR(500) DEFAULT '',
  `scheduled_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_user_campaign` (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 12. Contacts
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `contacts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_email` VARCHAR(191) DEFAULT '',
  `name` VARCHAR(150) DEFAULT '',
  `phone_number` VARCHAR(100) NOT NULL,
  `whatsapp_number` VARCHAR(100) DEFAULT '',
  `email` VARCHAR(191) DEFAULT '',
  `company` VARCHAR(150) DEFAULT '',
  `tags` VARCHAR(150) DEFAULT 'Lead',
  `notes` VARCHAR(255) DEFAULT '',
  `status` VARCHAR(30) DEFAULT 'ACTIVE',
  `group_name` VARCHAR(150) DEFAULT 'Default',
  `is_blacklisted` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user_contact` (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 13. WhatsApp Chat Auto-Reply Rules
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `whatsapp_chat_rules` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_email` VARCHAR(191) DEFAULT '',
  `received_keywords` VARCHAR(500) NOT NULL,
  `reply_message` TEXT NOT NULL,
  `match_type` VARCHAR(30) DEFAULT 'CONTAINS',
  `target_app` VARCHAR(30) DEFAULT 'ALL',
  `is_enabled` TINYINT(1) DEFAULT 1,
  `is_welcome_message` TINYINT(1) DEFAULT 0,
  `reply_delay_seconds` INT DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_user_rule` (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 14. Payment Gateways (admin config, secrets never sent to Flutter)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_gateways` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `gateway_name` VARCHAR(50) NOT NULL UNIQUE,
  `is_active` TINYINT(1) DEFAULT 0,
  `mode` ENUM('TEST','LIVE') DEFAULT 'TEST',
  `key_id` VARCHAR(255) DEFAULT '',
  `key_secret` VARCHAR(255) DEFAULT '',
  `webhook_secret` VARCHAR(255) DEFAULT '',
  `merchant_id` VARCHAR(255) DEFAULT '',
  `salt_key` VARCHAR(255) DEFAULT '',
  `salt_index` INT DEFAULT 1,
  `upi_id` VARCHAR(100) DEFAULT '',
  `upi_name` VARCHAR(150) DEFAULT '',
  `qr_image_url` VARCHAR(500) DEFAULT '',
  `instructions` TEXT,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `payment_gateways` (`id`, `gateway_name`, `is_active`, `mode`, `upi_id`, `upi_name`, `instructions`) VALUES
(1, 'RAZORPAY', 0, 'TEST', '', 'AI Auto Reply Marketing', 'Pay via Cards/Netbanking/UPI through Razorpay checkout.'),
(2, 'PHONEPE', 0, 'TEST', '', 'AI Auto Reply Marketing', 'Pay directly with PhonePe / BHIM UPI.'),
(3, 'MANUAL_UPI', 1, 'LIVE', 'yourid@upi', 'AI Auto Reply Marketing', 'Scan UPI QR code, pay the exact amount, and enter the 12-digit UTR/Ref No.');

-- ------------------------------------------------------------
-- 15. Payment Transactions
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `payment_transactions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_email` VARCHAR(191) NOT NULL,
  `plan_id` INT DEFAULT NULL,
  `plan_name` VARCHAR(150) NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `currency` VARCHAR(10) DEFAULT 'INR',
  `gateway` VARCHAR(50) NOT NULL,
  `order_id` VARCHAR(191) NOT NULL,
  `payment_id` VARCHAR(191) DEFAULT NULL,
  `signature` TEXT DEFAULT NULL,
  `status` ENUM('PENDING','SUCCESS','FAILED','VERIFIED','REJECTED','REFUNDED') DEFAULT 'PENDING',
  `validity_days_added` INT DEFAULT 0,
  `utr_number` VARCHAR(100) DEFAULT NULL,
  `metadata` TEXT DEFAULT NULL,
  `verified_by_admin` VARCHAR(191) DEFAULT NULL,
  `verified_at` TIMESTAMP NULL DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_txn_user` (`user_email`),
  INDEX `idx_txn_order` (`order_id`),
  INDEX `idx_txn_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 16. Banners
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `banners` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT,
  `image_url` VARCHAR(500) NOT NULL DEFAULT '',
  `button_text` VARCHAR(100) DEFAULT 'Learn More',
  `button_url` VARCHAR(500) DEFAULT '',
  `start_date` DATE DEFAULT NULL,
  `end_date` DATE DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `display_order` INT DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_banner_status` (`is_active`, `display_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 17. Notifications (admin broadcast / targeted, with per-user read tracking)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `target_user_email` VARCHAR(191) DEFAULT NULL COMMENT 'NULL = broadcast to all users',
  `action_url` VARCHAR(500) DEFAULT '',
  `created_by` VARCHAR(191) DEFAULT 'SYSTEM',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_notif_target` (`target_user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notification_reads` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `notification_id` INT NOT NULL,
  `user_email` VARCHAR(191) NOT NULL,
  `read_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uniq_notif_user` (`notification_id`, `user_email`),
  INDEX `idx_read_user` (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 18. Admin Sessions (simple token store, avoids needing JWT library)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admin_sessions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `admin_id` INT NOT NULL,
  `token_hash` VARCHAR(255) NOT NULL,
  `ip_address` VARCHAR(64) DEFAULT '',
  `user_agent` VARCHAR(255) DEFAULT '',
  `expires_at` TIMESTAMP NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_session_token` (`token_hash`),
  INDEX `idx_session_admin` (`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------
-- 19. Admin Login Attempts (brute-force protection)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `admin_login_attempts` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `email` VARCHAR(191) NOT NULL,
  `ip_address` VARCHAR(64) DEFAULT '',
  `success` TINYINT(1) DEFAULT 0,
  `attempted_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX `idx_attempt_lookup` (`email`, `attempted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;