-- AR BOT HOST MySQL schema
-- Create the database in your hosting panel first, then select/import this file.
-- The bot will also CREATE TABLE IF NOT EXISTS automatically on first start.

CREATE TABLE IF NOT EXISTS subscriptions (
  user_id BIGINT PRIMARY KEY,
  plan_name VARCHAR(255),
  expiry VARCHAR(64)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_files (
  user_id BIGINT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_type VARCHAR(32),
  PRIMARY KEY (user_id, file_name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS active_users (
  user_id BIGINT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS admins (
  user_id BIGINT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS plans (
  plan_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  file_limit INT,
  price VARCHAR(64),
  duration INT,
  buy_link TEXT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bot_settings (
  `key` VARCHAR(255) PRIMARY KEY,
  `value` TEXT
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS user_profiles (
  user_id BIGINT PRIMARY KEY,
  name VARCHAR(255),
  joined VARCHAR(64)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS bot_meta (
  user_id BIGINT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  bot_name VARCHAR(255),
  description TEXT,
  entry_file VARCHAR(255),
  auto_restart INT DEFAULT 0,
  auto_start INT DEFAULT 0,
  PRIMARY KEY(user_id, file_name)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vps_plans (
  plan_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  price VARCHAR(64),
  duration VARCHAR(64),
  ram VARCHAR(128),
  cpu VARCHAR(128),
  storage VARCHAR(128),
  bandwidth VARCHAR(128),
  bot_limit INT,
  auto_restart INT,
  features TEXT,
  description TEXT,
  button_text VARCHAR(255),
  badge VARCHAR(255)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS balances (
  user_id BIGINT PRIMARY KEY,
  balance DECIMAL(18,2) DEFAULT 0
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS transactions (
  tx_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT,
  kind VARCHAR(64),
  amount DECIMAL(18,2),
  status VARCHAR(64),
  reference VARCHAR(255),
  created VARCHAR(64),
  INDEX(user_id),
  INDEX(reference)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vouchers (
  voucher_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  code VARCHAR(128) UNIQUE NOT NULL,
  amount DECIMAL(18,2) NOT NULL,
  used_by BIGINT NULL,
  used_at VARCHAR(64) NULL,
  created_at VARCHAR(64) NOT NULL,
  INDEX(used_by)
) ENGINE=InnoDB;