-- ===================================================
-- ShieldPro SaaS Database Schema
-- cPanel phpMyAdmin এ ইমপোর্ট করার জন্য টেবিলসমূহ
-- ===================================================

-- 1. Plans Table
CREATE TABLE IF NOT EXISTS `plans` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(50) NOT NULL,
    `slug` VARCHAR(50) UNIQUE NOT NULL,
    `price` DECIMAL(10,2) DEFAULT 0,
    `billing_cycle` ENUM('monthly','yearly','lifetime') DEFAULT 'monthly',
    `domain_limit` INT DEFAULT 1,
    `rate_limit` INT DEFAULT 120,
    `log_retention_days` INT DEFAULT 1,
    `emergency_mode` TINYINT(1) DEFAULT 0,
    `custom_rules` TINYINT(1) DEFAULT 0,
    `is_active` TINYINT(1) DEFAULT 1,
    `sort_order` INT DEFAULT 0,
    `features` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default starter plans
INSERT IGNORE INTO `plans` (`id`, `name`, `slug`, `price`, `domain_limit`, `rate_limit`, `log_retention_days`, `emergency_mode`, `custom_rules`, `sort_order`) VALUES
(1, 'Free', 'free', 0.00, 1, 120, 1, 0, 0, 1),
(2, 'Pro', 'pro', 9.99, 5, 60, 7, 1, 1, 2),
(3, 'Business', 'business', 29.99, 20, 30, 30, 1, 1, 3);

-- 2. Users Table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `email` VARCHAR(150) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `plan_id` INT DEFAULT 1,
    `is_admin` TINYINT(1) DEFAULT 0,
    `is_active` TINYINT(1) DEFAULT 1,
    `api_key` VARCHAR(64) UNIQUE,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. Domains Table
CREATE TABLE IF NOT EXISTS `domains` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `domain` VARCHAR(255) NOT NULL,
    `agent_key` VARCHAR(64) UNIQUE NOT NULL,
    `status` ENUM('active','inactive','suspended') DEFAULT 'active',
    `cms_type` VARCHAR(50) DEFAULT 'unknown',
    `server_type` VARCHAR(50) DEFAULT 'unknown',
    `protection_active` TINYINT(1) DEFAULT 0,
    `last_seen_at` TIMESTAMP NULL,
    `custom_config` JSON,
    `scan_data` JSON,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Attack Logs Table
CREATE TABLE IF NOT EXISTS `attack_logs` (
    `id` BIGINT AUTO_INCREMENT PRIMARY KEY,
    `domain_id` INT NOT NULL,
    `ip` VARCHAR(45) NOT NULL,
    `event_type` VARCHAR(50) NOT NULL,
    `uri` VARCHAR(1000),
    `method` VARCHAR(10),
    `user_agent` VARCHAR(500),
    `detail` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_domain` (`domain_id`),
    INDEX `idx_created` (`created_at`),
    FOREIGN KEY (`domain_id`) REFERENCES `domains`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. Blocked IPs Table
CREATE TABLE IF NOT EXISTS `blocked_ips` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `domain_id` INT NOT NULL,
    `ip` VARCHAR(45) NOT NULL,
    `reason` VARCHAR(255),
    `is_permanent` TINYINT(1) DEFAULT 0,
    `blocked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `expires_at` TIMESTAMP NULL,
    `blocked_by` ENUM('auto','admin','user') DEFAULT 'auto',
    UNIQUE KEY `unique_domain_ip` (`domain_id`, `ip`),
    FOREIGN KEY (`domain_id`) REFERENCES `domains`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Whitelisted IPs Table
CREATE TABLE IF NOT EXISTS `whitelisted_ips` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `domain_id` INT NOT NULL,
    `ip` VARCHAR(45) NOT NULL,
    `note` VARCHAR(255),
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY `unique_wl_ip` (`domain_id`, `ip`),
    FOREIGN KEY (`domain_id`) REFERENCES `domains`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 7. Settings Table
CREATE TABLE IF NOT EXISTS `settings` (
    `setting_key` VARCHAR(100) PRIMARY KEY,
    `setting_value` TEXT,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `settings` (`setting_key`, `setting_value`) VALUES
('site_name', 'ShieldPro'),
('site_url', 'https://shieldpro.vercel.app'),
('api_url', 'https://api.yourdomain.com'),
('default_plan', '1'),
('registration_open', '1');