-- ============================================================
--  💸 TIỀN VỀ SẾP ƠI 💸 - DATABASE INSTALL
--  Import file này trong phpMyAdmin trước khi chạy bot
-- ============================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ========== USERS ==========
CREATE TABLE IF NOT EXISTS `users` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `telegram_id`    BIGINT NOT NULL,
  `username`       VARCHAR(100) DEFAULT NULL,
  `full_name`      VARCHAR(255) DEFAULT NULL,
  `balance`        BIGINT NOT NULL DEFAULT 0,
  `daily_limit`    INT NOT NULL DEFAULT 5,
  `used_today`     INT NOT NULL DEFAULT 0,
  `used_date`      DATE DEFAULT NULL,
  `is_vip`         TINYINT(1) NOT NULL DEFAULT 0,
  `vip_expired_at` DATETIME DEFAULT NULL,
  `state`          VARCHAR(50) DEFAULT NULL,
  `state_data`     TEXT DEFAULT NULL,
  `total_check`    INT NOT NULL DEFAULT 0,
  `total_deposit`  BIGINT NOT NULL DEFAULT 0,
  `banned`         TINYINT(1) NOT NULL DEFAULT 0,
  `created_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_tg` (`telegram_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== PLANS ==========
CREATE TABLE IF NOT EXISTS `plans` (
  `id`           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`         VARCHAR(50) NOT NULL,
  `name`         VARCHAR(100) NOT NULL,
  `price`        BIGINT NOT NULL DEFAULT 0,
  `days`         INT NOT NULL DEFAULT 30,
  `daily_limit`  INT NOT NULL DEFAULT 5,
  `is_vip`       TINYINT(1) NOT NULL DEFAULT 0,
  `description`  TEXT,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `plans` (`code`,`name`,`price`,`days`,`daily_limit`,`is_vip`,`description`) VALUES
('FREE','Gói Mặc Định',0,0,5,0,'5 lượt check/ngày'),
('VIP_30','VIP 30 Ngày',15000,30,9999,1,'Không giới hạn check 30 ngày')
ON DUPLICATE KEY UPDATE `name`=VALUES(`name`);

-- ========== ITEMS (FB + TIKTOK) ==========
CREATE TABLE IF NOT EXISTS `items` (
  `id`           BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`      BIGINT UNSIGNED NOT NULL,
  `platform`     ENUM('facebook','tiktok') NOT NULL,
  `external_id`  VARCHAR(191) DEFAULT NULL,
  `link`         VARCHAR(500) DEFAULT NULL,
  `name`         VARCHAR(255) DEFAULT NULL,
  `note`         VARCHAR(255) DEFAULT NULL,
  `price`        BIGINT NOT NULL DEFAULT 0,
  `expire_text`  VARCHAR(50) DEFAULT NULL,
  `expire_at`    DATETIME DEFAULT NULL,
  `status`       ENUM('pending','live','die','done','cancel','deleted') NOT NULL DEFAULT 'pending',
  `last_checked` DATETIME DEFAULT NULL,
  `check_count`  INT NOT NULL DEFAULT 0,
  `last_data`    MEDIUMTEXT,
  `avatar_url`   VARCHAR(1000) DEFAULT NULL,
  `followers_count` BIGINT DEFAULT NULL,
  `following_count` BIGINT DEFAULT NULL,
  `likes_count` BIGINT DEFAULT NULL,
  `videos_count` BIGINT DEFAULT NULL,
  `last_reason` VARCHAR(500) DEFAULT NULL,
  `http_code` INT DEFAULT NULL,
  `next_check_at` DATETIME DEFAULT NULL,
  `die_at` DATETIME DEFAULT NULL,
  `last_profile_sync_at` DATETIME DEFAULT NULL,
  `created_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_platform_external` (`user_id`,`platform`,`external_id`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`),
  KEY `idx_platform_ext` (`platform`,`external_id`),
  KEY `idx_next_check` (`status`,`next_check_at`,`last_checked`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== ITEM LOGS ==========
CREATE TABLE IF NOT EXISTS `item_logs` (
  `id`                BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `item_id`           BIGINT UNSIGNED NOT NULL,
  `action_type`       VARCHAR(50) NOT NULL,
  `old_data`          MEDIUMTEXT,
  `new_data`          MEDIUMTEXT,
  `actor_telegram_id` BIGINT DEFAULT NULL,
  `note`              VARCHAR(500) DEFAULT NULL,
  `created_at`        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_item` (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== PAYMENTS (AUTO BANK) ==========
CREATE TABLE IF NOT EXISTS `payments` (
  `id`              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`         BIGINT UNSIGNED DEFAULT NULL,
  `telegram_id`     BIGINT DEFAULT NULL,
  `amount`          BIGINT NOT NULL DEFAULT 0,
  `transfer_content` VARCHAR(500) DEFAULT NULL,
  `bank_trans_id`   VARCHAR(100) DEFAULT NULL,
  `raw_json`        MEDIUMTEXT,
  `status`          ENUM('pending','done','skipped','error') NOT NULL DEFAULT 'pending',
  `note`            VARCHAR(500) DEFAULT NULL,
  `processed_at`    DATETIME DEFAULT NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_trans` (`bank_trans_id`),
  KEY `idx_status` (`status`),
  KEY `idx_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== TICKETS ==========
CREATE TABLE IF NOT EXISTS `tickets` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `code`       VARCHAR(20) NOT NULL,
  `user_id`    BIGINT UNSIGNED NOT NULL,
  `category`   VARCHAR(50) DEFAULT NULL,
  `subject`    VARCHAR(255) DEFAULT NULL,
  `status`     ENUM('open','replied','closed') NOT NULL DEFAULT 'open',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_code` (`code`),
  KEY `idx_user` (`user_id`),
  KEY `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== TICKET MESSAGES ==========
CREATE TABLE IF NOT EXISTS `ticket_messages` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ticket_id`   BIGINT UNSIGNED NOT NULL,
  `sender_type` ENUM('user','admin') NOT NULL,
  `sender_id`   BIGINT NOT NULL,
  `content`     TEXT,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_ticket` (`ticket_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== BROADCAST LOGS ==========
CREATE TABLE IF NOT EXISTS `broadcast_logs` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id`    BIGINT NOT NULL,
  `content`     TEXT,
  `success`     INT NOT NULL DEFAULT 0,
  `failed`      INT NOT NULL DEFAULT 0,
  `total`       INT NOT NULL DEFAULT 0,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== SETTINGS ==========
CREATE TABLE IF NOT EXISTS `settings` (
  `k`          VARCHAR(100) NOT NULL,
  `v`          TEXT,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `settings` (`k`,`v`) VALUES
('last_cron_bank','0'),
('last_cron_check','0'),
('bot_online','1')
ON DUPLICATE KEY UPDATE `k`=VALUES(`k`);

-- ========== CRON LOGS ==========
CREATE TABLE IF NOT EXISTS `cron_logs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tag`        VARCHAR(50) NOT NULL,
  `message`    TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tag` (`tag`),
  KEY `idx_time` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ========== ADMIN LOGS ==========
CREATE TABLE IF NOT EXISTS `admin_logs` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `admin_id`   BIGINT NOT NULL,
  `action`     VARCHAR(100) NOT NULL,
  `target_id`  BIGINT DEFAULT NULL,
  `detail`     TEXT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;