SET NAMES utf8mb4;

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

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  telegram_id VARCHAR(32) NOT NULL UNIQUE,
  username VARCHAR(100) NULL,
  first_name VARCHAR(190) NULL,
  last_name VARCHAR(190) NULL,
  role ENUM('admin','reseller','customer') NOT NULL DEFAULT 'customer',
  balance BIGINT NOT NULL DEFAULT 0,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  last_seen_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_users_role(role),
  INDEX idx_users_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  level ENUM('super','manager') NOT NULL DEFAULT 'manager',
  created_at DATETIME NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resellers (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL UNIQUE,
  display_name VARCHAR(190) NOT NULL,
  commission_percent DECIMAL(5,2) NOT NULL DEFAULT 0,
  status ENUM('active','blocked') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_reseller_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS plans (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  months INT NOT NULL DEFAULT 1,
  price BIGINT NOT NULL DEFAULT 0,
  is_custom TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_plan_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payment_cards (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL DEFAULT 'کارت اصلی',
  holder VARCHAR(190) NOT NULL,
  card_number VARCHAR(40) NOT NULL,
  iban VARCHAR(50) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_card_active(is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS bots (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_user_id BIGINT UNSIGNED NOT NULL,
  reseller_id BIGINT UNSIGNED NULL,
  bot_username VARCHAR(190) NULL,
  bot_name VARCHAR(190) NULL,
  bot_token_enc TEXT NOT NULL,
  webhook_secret VARCHAR(128) NOT NULL UNIQUE,
  deployment_path VARCHAR(255) NULL,
  webhook_url VARCHAR(500) NULL,
  child_db_name VARCHAR(64) NULL,
  child_db_user VARCHAR(32) NULL,
  child_db_user_host VARCHAR(255) NULL,
  child_db_provider VARCHAR(32) NULL,
  child_storage_path VARCHAR(255) NULL,
  isolation_mode VARCHAR(32) NOT NULL DEFAULT 'legacy_shared',
  isolated_at DATETIME NULL,
  plan_id BIGINT UNSIGNED NULL,
  price BIGINT NOT NULL DEFAULT 0,
  status ENUM('active','suspended','expired') NOT NULL DEFAULT 'active',
  expires_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  FOREIGN KEY(owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(reseller_id) REFERENCES resellers(id) ON DELETE SET NULL,
  FOREIGN KEY(plan_id) REFERENCES plans(id) ON DELETE SET NULL,
  INDEX idx_bot_owner(owner_user_id),
  INDEX idx_bot_reseller(reseller_id),
  INDEX idx_bot_status(status),
  INDEX idx_bot_expires(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  bot_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  plan_id BIGINT UNSIGNED NULL,
  started_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  status ENUM('active','expired','cancelled') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  FOREIGN KEY(bot_id) REFERENCES bots(id) ON DELETE CASCADE,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(plan_id) REFERENCES plans(id) ON DELETE SET NULL,
  INDEX idx_sub_user(user_id),
  INDEX idx_sub_status(status),
  INDEX idx_sub_expires(expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS transactions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  amount BIGINT NOT NULL,
  type ENUM('charge','debit','refund','manual_payment') NOT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'approved',
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_tx_user(user_id),
  INDEX idx_tx_status(status),
  INDEX idx_tx_type(type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS support_tickets (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  subject VARCHAR(190) NOT NULL,
  message TEXT NOT NULL,
  status ENUM('open','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_ticket_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_states (
  user_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  state VARCHAR(80) NOT NULL,
  payload JSON NULL,
  updated_at DATETIME NOT NULL,
  FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  actor_user_id BIGINT UNSIGNED NULL,
  action VARCHAR(100) NOT NULL,
  payload JSON NULL,
  ip VARCHAR(80) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_audit_action(action),
  INDEX idx_audit_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO plans(title, months, price, is_custom, status, created_at)
SELECT 'یک ماهه', 1, 0, 0, 'active', NOW()
WHERE NOT EXISTS (SELECT 1 FROM plans LIMIT 1);

CREATE TABLE IF NOT EXISTS required_joins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  chat_id VARCHAR(80) NOT NULL,
  invite_url VARCHAR(255) NULL,
  join_type ENUM('channel','group') NOT NULL DEFAULT 'channel',
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_required_joins_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS topup_requests (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  transaction_id BIGINT UNSIGNED NULL,
  amount BIGINT NOT NULL,
  method ENUM('card_to_card') NOT NULL DEFAULT 'card_to_card',
  receipt_type VARCHAR(30) NULL,
  receipt_file_id VARCHAR(255) NULL,
  receipt_caption TEXT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  admin_user_id BIGINT UNSIGNED NULL,
  reject_reason TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  INDEX idx_topup_user(user_id),
  INDEX idx_topup_status(status),
  INDEX idx_topup_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO settings(`key`, value, updated_at) VALUES
('topup_min_amount','200000',NOW()),
('topup_max_amount','50000000',NOW()),
('bot_min_balance','200000',NOW())
ON DUPLICATE KEY UPDATE `key`=`key`;
