-- ==========================================================
--  دیتابیس ربات مدیریت و فروش هاست
--  این فایل را در phpMyAdmin هاست خود Import کنید
-- ==========================================================

SET NAMES utf8mb4;
SET time_zone = '+00:00';

-- ----------------------------
-- جدول کاربران
-- ----------------------------
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `telegram_id` BIGINT NOT NULL,
    `username` VARCHAR(64) NULL,
    `full_name` VARCHAR(191) NULL,
    `balance` DECIMAL(15,0) NOT NULL DEFAULT 0,
    `is_blocked` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uniq_telegram_id` (`telegram_id`),
    KEY `idx_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول پلن‌های هاست
-- ----------------------------
CREATE TABLE IF NOT EXISTS `plans` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(191) NOT NULL,
    `specs` TEXT NULL,
    `price` DECIMAL(15,0) NOT NULL DEFAULT 0,
    `duration_days` INT UNSIGNED NOT NULL DEFAULT 30,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول سفارش‌ها (درخواست خرید پلن یا شارژ کیف پول)
-- ----------------------------
CREATE TABLE IF NOT EXISTS `orders` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `plan_id` INT UNSIGNED NULL,
    `amount` DECIMAL(15,0) NOT NULL DEFAULT 0,
    `payment_method` ENUM('wallet','card') NOT NULL DEFAULT 'card',
    `receipt_text` VARCHAR(191) NULL,
    `receipt_file_id` VARCHAR(255) NULL,
    `status` ENUM('pending','awaiting_review','paid','completed','rejected') NOT NULL DEFAULT 'pending',
    `admin_note` TEXT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_plan` (`plan_id`),
    KEY `idx_status` (`status`),
    CONSTRAINT `fk_orders_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_orders_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول سرویس‌های هاست ثبت‌شده برای مشتریان (به‌صورت کاملا دستی توسط ادمین)
-- ----------------------------
CREATE TABLE IF NOT EXISTS `services` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `order_id` INT UNSIGNED NULL,
    `plan_id` INT UNSIGNED NULL,
    `domain` VARCHAR(191) NULL,
    `host_username` VARCHAR(191) NULL,
    `host_password` VARCHAR(191) NULL,
    `ip_address` VARCHAR(64) NULL,
    `control_panel_url` VARCHAR(255) NULL,
    `extra_info` TEXT NULL,
    `start_date` DATE NULL,
    `expire_date` DATE NULL,
    `status` ENUM('active','suspended','expired') NOT NULL DEFAULT 'active',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_order` (`order_id`),
    KEY `idx_plan` (`plan_id`),
    KEY `idx_status` (`status`),
    CONSTRAINT `fk_services_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_services_order` FOREIGN KEY (`order_id`) REFERENCES `orders`(`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_services_plan` FOREIGN KEY (`plan_id`) REFERENCES `plans`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول تراکنش‌های کیف پول
-- ----------------------------
CREATE TABLE IF NOT EXISTS `transactions` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `type` ENUM('deposit','purchase','refund','admin_adjust') NOT NULL,
    `amount` DECIMAL(15,0) NOT NULL,
    `description` VARCHAR(255) NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_type` (`type`),
    CONSTRAINT `fk_transactions_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول تیکت‌های پشتیبانی
-- ----------------------------
CREATE TABLE IF NOT EXISTS `tickets` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED NOT NULL,
    `subject` VARCHAR(191) NOT NULL,
    `status` ENUM('open','answered','closed') NOT NULL DEFAULT 'open',
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_user` (`user_id`),
    KEY `idx_status` (`status`),
    CONSTRAINT `fk_tickets_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول پیام‌های تیکت
-- ----------------------------
CREATE TABLE IF NOT EXISTS `ticket_messages` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ticket_id` INT UNSIGNED NOT NULL,
    `sender` ENUM('user','admin') NOT NULL,
    `message` TEXT NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    KEY `idx_ticket` (`ticket_id`),
    CONSTRAINT `fk_ticket_messages_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول وضعیت مکالمه کاربران (برای مراحل چندمرحله‌ای مثل افزودن پلن یا هاست)
-- ----------------------------
CREATE TABLE IF NOT EXISTS `user_states` (
    `telegram_id` BIGINT NOT NULL,
    `step` VARCHAR(64) NULL,
    `data` TEXT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول تنظیمات کلی (شماره کارت، نام صاحب کارت و ...)
-- ----------------------------
CREATE TABLE IF NOT EXISTS `settings` (
    `key` VARCHAR(64) NOT NULL,
    `value` TEXT NULL,
    PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- جدول لاگ‌ها (اختیاری، برای ثبت رویدادها)
-- ----------------------------
CREATE TABLE IF NOT EXISTS `logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `telegram_id` BIGINT NULL,
    `action` VARCHAR(64) NOT NULL,
    `details` TEXT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ----------------------------
-- مقادیر پیش‌فرض تنظیمات پرداخت (بعدا از داخل ربات قابل تغییر است)
-- ----------------------------
INSERT INTO `settings` (`key`, `value`) VALUES
    ('card_number', 'ثبت نشده'),
    ('card_holder', 'ثبت نشده')
ON DUPLICATE KEY UPDATE `key` = `key`;