SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS `settings` (
  `key`   VARCHAR(80)  NOT NULL,
  `value` TEXT         NULL,
  PRIMARY KEY (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `users` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id`    VARCHAR(64)     NOT NULL,
  `username`   VARCHAR(190)    NOT NULL DEFAULT '',
  `first_name` VARCHAR(190)    NOT NULL DEFAULT '',
  `last_name`  VARCHAR(190)    NOT NULL DEFAULT '',
  `last_seen`  DATETIME        NOT NULL,
  `created_at` DATETIME        NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `chat_id` (`chat_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `plans` (
  `id`                   INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `plan_key`             VARCHAR(80)   NOT NULL,
  `name`                 VARCHAR(190)  NOT NULL,
  `description`          TEXT          NULL,
  `price`                BIGINT        NOT NULL DEFAULT 0,
  `traffic_gb`           DECIMAL(12,2) NOT NULL DEFAULT 0,
  `days`                 INT           NOT NULL DEFAULT 30,
  `start_after_first_use` TINYINT(1)   NOT NULL DEFAULT 1,
  `enabled_new`          TINYINT(1)    NOT NULL DEFAULT 1,
  `enabled_renew`        TINYINT(1)    NOT NULL DEFAULT 1,
  `badge_enabled`        TINYINT(1)    NOT NULL DEFAULT 0,
  `badge_text`           VARCHAR(100)  NOT NULL DEFAULT '🔥 فروش ویژه',
  `badge_color`          VARCHAR(20)   NOT NULL DEFAULT '#e53935',
  `sort_order`           INT           NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `plan_key` (`plan_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `orders` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id`          VARCHAR(64)     NOT NULL DEFAULT '',
  `username`         VARCHAR(190)    NOT NULL DEFAULT '',
  `first_name`       VARCHAR(190)    NOT NULL DEFAULT '',
  `type`             VARCHAR(20)     NOT NULL DEFAULT 'new',
  `service_id`       BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `plan_id`          VARCHAR(80)     NOT NULL DEFAULT '',
  `plan_name`        VARCHAR(190)    NOT NULL DEFAULT '',
  `price`            BIGINT          NOT NULL DEFAULT 0,
  `unique_amount`    BIGINT          NOT NULL DEFAULT 0,
  `status`           VARCHAR(30)     NOT NULL DEFAULT 'pending',
  `paid_amount_rial` BIGINT          NOT NULL DEFAULT 0,
  `paid_at`          DATETIME        NULL,
  `reserved_until`   INT UNSIGNED    NOT NULL DEFAULT 0,
  `processing_lock`  VARCHAR(64)     NOT NULL DEFAULT '',
  `delivery_lock`    VARCHAR(64)     NOT NULL DEFAULT '',
  `token_hash`       VARCHAR(255)    NOT NULL DEFAULT '',
  `error_text`       TEXT            NULL,
  `created_at`       DATETIME        NOT NULL,
  `updated_at`       DATETIME        NOT NULL,
  PRIMARY KEY (`id`),
  KEY `chat_id` (`chat_id`),
  KEY `unique_amount` (`unique_amount`),
  KEY `status` (`status`),
  KEY `service_id` (`service_id`),
  KEY `reserved_until` (`reserved_until`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `services` (
  `id`                   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `chat_id`              VARCHAR(64)     NOT NULL DEFAULT '',
  `client_email`         VARCHAR(190)    NOT NULL DEFAULT '',
  `client_uuid`          VARCHAR(190)    NOT NULL DEFAULT '',
  `inbound_id`           BIGINT          NOT NULL DEFAULT 0,
  `plan_name`            VARCHAR(190)    NOT NULL DEFAULT '',
  `traffic_gb`           DECIMAL(12,2)   NOT NULL DEFAULT 0,
  `duration_days`        INT             NOT NULL DEFAULT 0,
  `expiry_at`            DATETIME        NULL,
  `start_after_first_use` TINYINT(1)     NOT NULL DEFAULT 1,
  `subscription_url`     TEXT            NULL,
  `vless_config`         TEXT            NULL,
  `enabled`              TINYINT(1)      NOT NULL DEFAULT 1,
  `created_at`           DATETIME        NOT NULL,
  `updated_at`           DATETIME        NOT NULL,
  PRIMARY KEY (`id`),
  KEY `chat_id` (`chat_id`),
  KEY `client_uuid` (`client_uuid`),
  KEY `client_email` (`client_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `tutorials` (
  `id`          INT UNSIGNED  NOT NULL AUTO_INCREMENT,
  `title`       VARCHAR(190)  NOT NULL,
  `platform`    VARCHAR(80)   NOT NULL DEFAULT '',
  `description` TEXT          NULL,
  `url`         TEXT          NULL,
  `image_url`   TEXT          NULL,
  `enabled`     TINYINT(1)    NOT NULL DEFAULT 1,
  `sort_order`  INT           NOT NULL DEFAULT 0,
  `created_at`  DATETIME      NOT NULL,
  `updated_at`  DATETIME      NOT NULL,
  PRIMARY KEY (`id`),
  KEY `enabled` (`enabled`),
  KEY `sort_order` (`sort_order`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `webhook_logs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `source`     VARCHAR(40)     NOT NULL DEFAULT 'sms',
  `payload`    TEXT            NULL,
  `result`     VARCHAR(80)     NULL,
  `order_id`   BIGINT UNSIGNED NULL,
  `created_at` DATETIME        NOT NULL,
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default settings
INSERT IGNORE INTO `settings` (`key`, `value`) VALUES
('bot_username', ''),
('channel', ''),
('channel_join_required', '0'),
('payment_minutes', '20'),
('payment_card', ''),
('card_holder', ''),
('bank', ''),
('support_url', 'https://t.me/iransafe_net'),
('tutorial_url', 'https://iransafe.net/blog'),
('welcome_logo', ''),
('welcome_title', 'به ایرانسیف خوش آمدید 👋'),
('welcome_text', 'خرید و مدیریت سرویس ایرانسیف را از همین‌جا انجام دهید.'),
('welcome_channel_text', '📢 کانال ایرانسیف'),
('welcome_channel_url', ''),
('webapp_enabled', '1'),
('show_vless', '1'),
('sanaei_inbound_remark', '@iransafe'),
('unique_min', '1'),
('unique_max', '999');

SET FOREIGN_KEY_CHECKS = 1;