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


-- ------------------------------------------------------------
-- 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);

-- ------------------------------------------------------------
-- 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;

-- ------------------------------------------------------------
-- 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;

-- ------------------------------------------------------------
-- 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;

-- ------------------------------------------------------------
-- 21. User Settings (per-user automation preferences — master switch,
--     cooldown, SIM, WhatsApp toggles, working hours)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `user_settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_email` VARCHAR(191) NOT NULL UNIQUE,
  `is_auto_reply_enabled` TINYINT(1) DEFAULT 1,
  `is_cooldown_enabled` TINYINT(1) DEFAULT 1,
  `cooldown_hours` INT DEFAULT 24,
  `selected_sim_slot` TINYINT DEFAULT 0 COMMENT '0=Auto,1=SIM1,2=SIM2',
  `enable_sms` TINYINT(1) DEFAULT 1,
  `enable_whatsapp_normal` TINYINT(1) DEFAULT 1,
  `enable_whatsapp_business` TINYINT(1) DEFAULT 1,
  `enable_whatsapp_groups` TINYINT(1) DEFAULT 0,
  `enable_whatsapp_individual` TINYINT(1) DEFAULT 1,
  `whatsapp_reply_delay_seconds` INT DEFAULT 2,
  `whatsapp_group_exclude_keywords` VARCHAR(500) DEFAULT '',
  `working_hours_only` TINYINT(1) DEFAULT 0,
  `working_start_hour` TINYINT DEFAULT 9,
  `working_end_hour` TINYINT DEFAULT 18,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX `idx_user_settings_email` (`user_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
