-- Initial Database Schema

CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(255) NOT NULL,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('admin') DEFAULT 'admin',
    `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;

CREATE TABLE IF NOT EXISTS `affiliate_networks` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name` VARCHAR(100) NOT NULL,
    `network_type` VARCHAR(100) NULL,
    `tracking_affiliate_id` VARCHAR(100) NULL,
    `api_keys_json` TEXT,
    `notes` TEXT,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `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;


CREATE TABLE IF NOT EXISTS `products` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `network_id` INT NULL,
    `external_id` VARCHAR(100) NULL,
    `title` VARCHAR(255) NOT NULL,
    `category` VARCHAR(100) NULL,
    `short_description` TEXT NULL,
    `original_product_url` TEXT NOT NULL,
    `affiliate_url` TEXT NULL,
    `image_url` TEXT,
    `current_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `original_price` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `discount_percentage` DECIMAL(5,2) DEFAULT 0.00,
    `commission_rate` DECIMAL(5,2) DEFAULT 0.00,
    `estimated_commission` DECIMAL(10,2) DEFAULT 0.00,
    `status` ENUM('active', 'inactive') DEFAULT 'active',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`network_id`) REFERENCES `affiliate_networks`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `product_analysis` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL UNIQUE,
    `overall_score` INT DEFAULT 0,
    `demand_potential` INT DEFAULT 0,
    `price_attractiveness` INT DEFAULT 0,
    `discount_attractiveness` INT DEFAULT 0,
    `commission_potential` INT DEFAULT 0,
    `buyer_interest` INT DEFAULT 0,
    `competition_level` VARCHAR(50) NULL,
    `recommendation` VARCHAR(100) NULL,
    `reason` TEXT NULL,
    `risk_warning` TEXT NULL,
    `ai_eng_content` TEXT NULL,
    `ai_bn_content` TEXT NULL,
    `provider_used` VARCHAR(100) NULL,
    `model_used` VARCHAR(100) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `ai_logs` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `provider` VARCHAR(50) NULL,
    `model` VARCHAR(100) NULL,
    `status` ENUM('success', 'failed') DEFAULT 'failed',
    `error_message` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tracking_links` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `generated_aff_url` TEXT NOT NULL,
    `sub_id` VARCHAR(100) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `clicks` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `tracking_link_id` INT NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `user_agent` TEXT NULL,
    `clicked_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`tracking_link_id`) REFERENCES `tracking_links`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `network_id` INT NULL,
    `tracking_link_id` INT NULL,
    `product_id` INT NULL,
    `transaction_id` VARCHAR(100) NULL,
    `order_id` VARCHAR(100) NULL,
    `sub_id` VARCHAR(100) NULL,
    `order_amount` DECIMAL(10,2) DEFAULT 0.00,
    `commission_amount` DECIMAL(10,2) DEFAULT 0.00,
    `status` ENUM('pending', 'approved', 'rejected', 'cancelled', 'paid') DEFAULT 'pending',
    `order_date` DATETIME NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`network_id`) REFERENCES `affiliate_networks`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`tracking_link_id`) REFERENCES `tracking_links`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `idx_unique_transaction` (`network_id`, `transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `import_history` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `import_type` VARCHAR(50) NOT NULL,
    `network_id` INT NULL,
    `file_name` VARCHAR(255) NOT NULL,
    `total_rows` INT DEFAULT 0,
    `imported_rows` INT DEFAULT 0,
    `skipped_rows` INT DEFAULT 0,
    `failed_rows` INT DEFAULT 0,
    `status` VARCHAR(50) DEFAULT 'completed',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`network_id`) REFERENCES `affiliate_networks`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


CREATE TABLE IF NOT EXISTS `promotion_queue` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `product_id` INT NOT NULL,
    `tracking_link_id` INT NULL,
    `status` ENUM('queued', 'approved', 'scheduled', 'published', 'failed', 'cancelled') DEFAULT 'queued',
    `priority` INT DEFAULT 0,
    `scheduled_at` DATETIME NULL,
    `processed_at` DATETIME NULL,
    `failure_reason` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`product_id`) REFERENCES `products`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`tracking_link_id`) REFERENCES `tracking_links`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `automation_settings` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `key_name` VARCHAR(100) NOT NULL UNIQUE,
    `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `automation_settings` (`key_name`, `value`) VALUES
('enabled', '0'),
('min_score', '70'),
('require_manual_approval', '1'),
('timezone', 'Asia/Dhaka'),
('test_mode', '1');