-- =========================================================
-- Premium App Subscription Bot — Full Database Schema
-- Import this once via phpMyAdmin (cPanel) before using the system
-- =========================================================

SET FOREIGN_KEY_CHECKS=0;

-- ---------- Website / Bot Users ----------
CREATE TABLE IF NOT EXISTS users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(150) DEFAULT NULL,
  phone VARCHAR(30) DEFAULT NULL,
  password VARCHAR(255) DEFAULT NULL,
  telegram_id BIGINT DEFAULT NULL UNIQUE,
  telegram_username VARCHAR(100) DEFAULT NULL,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  status ENUM('active','banned') NOT NULL DEFAULT 'active',
  link_code VARCHAR(20) DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_telegram (telegram_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Admins ----------
CREATE TABLE IF NOT EXISTS admins (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(60) NOT NULL UNIQUE,
  password VARCHAR(255) NOT NULL,
  name VARCHAR(120) DEFAULT NULL,
  role ENUM('super','staff') NOT NULL DEFAULT 'staff',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- NOTE: No default admin is seeded here. After importing this file, open
-- /admin/setup.php ONE TIME in your browser to create your real admin
-- account with a properly hashed password. That script deletes itself
-- after creating the first admin.

-- ---------- Settings (key-value) ----------
CREATE TABLE IF NOT EXISTS settings (
  setting_key VARCHAR(80) PRIMARY KEY,
  setting_value TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO settings (setting_key,setting_value) VALUES
('app_name','DigitalStore'),
('bot_token',''),
('bot_username',''),
('admin_chat_id',''),
('telegram_bot_secret', SUBSTRING(MD5(RAND()),1,32)),
('bkash_enabled','1'),('bkash_number','01700000000'),
('nagad_enabled','1'),('nagad_number','01700000000'),
('rocket_enabled','0'),('rocket_number','01700000000'),
('boompay_enabled','0'),('boompay_api_key',''),
('site_url','https://example.com'),
('support_username','yourusername')
ON DUPLICATE KEY UPDATE setting_key=setting_key;

-- ---------- Product Categories ----------
CREATE TABLE IF NOT EXISTS categories (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  emoji VARCHAR(10) DEFAULT '📦',
  sort_order INT DEFAULT 0,
  status ENUM('active','hidden') DEFAULT 'active'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Products (subscription key/code reselling) ----------
CREATE TABLE IF NOT EXISTS products (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  category_id INT UNSIGNED DEFAULT NULL,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  price DECIMAL(12,2) NOT NULL,
  duration_label VARCHAR(50) DEFAULT NULL,   -- e.g. "1 Month", "Lifetime"
  delivery_type ENUM('auto_stock','manual') NOT NULL DEFAULT 'auto_stock',
  image VARCHAR(255) DEFAULT NULL,
  status ENUM('active','hidden') NOT NULL DEFAULT 'active',
  sort_order INT DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Stock keys/codes for auto_stock products ----------
CREATE TABLE IF NOT EXISTS product_keys (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  product_id INT UNSIGNED NOT NULL,
  key_data TEXT NOT NULL,          -- the actual subscription key/code/credentials
  status ENUM('available','sold') NOT NULL DEFAULT 'available',
  sold_order_id INT UNSIGNED DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
  INDEX idx_status (product_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Orders ----------
CREATE TABLE IF NOT EXISTS orders (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_ref VARCHAR(30) NOT NULL UNIQUE,
  user_id INT UNSIGNED NOT NULL,
  product_id INT UNSIGNED NOT NULL,
  price DECIMAL(12,2) NOT NULL,
  delivered_key TEXT DEFAULT NULL,
  status ENUM('pending','completed','failed','refunded') NOT NULL DEFAULT 'pending',
  source ENUM('bot','website') NOT NULL DEFAULT 'bot',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Transactions (wallet add-money, manual + auto gateway) ----------
CREATE TABLE IF NOT EXISTS transactions (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  transaction_id VARCHAR(100) DEFAULT NULL UNIQUE,
  order_ref VARCHAR(30) DEFAULT NULL,
  amount DECIMAL(12,2) NOT NULL,
  type ENUM('credit','debit') NOT NULL,
  method VARCHAR(30) NOT NULL,
  note VARCHAR(255) DEFAULT NULL,
  status ENUM('pending','completed','failed','cancelled') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_order_ref (order_ref)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Support Tickets ----------
CREATE TABLE IF NOT EXISTS support_tickets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id INT UNSIGNED NOT NULL,
  subject VARCHAR(200) DEFAULT 'সাধারণ সাপোর্ট',
  status ENUM('open','answered','closed') NOT NULL DEFAULT 'open',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_messages (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT UNSIGNED NOT NULL,
  sender ENUM('user','admin') NOT NULL,
  message TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Broadcast / direct message queue (admin -> bot users) ----------
CREATE TABLE IF NOT EXISTS message_queue (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  target_type ENUM('all','single') NOT NULL,
  user_id INT UNSIGNED DEFAULT NULL,
  message TEXT NOT NULL,
  status ENUM('pending','processing','done') NOT NULL DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS message_queue_recipients (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  queue_id INT UNSIGNED NOT NULL,
  user_id INT UNSIGNED NOT NULL,
  sent TINYINT(1) NOT NULL DEFAULT 0,
  FOREIGN KEY (queue_id) REFERENCES message_queue(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Bot conversation state (since bot is stateless webhook) ----------
CREATE TABLE IF NOT EXISTS bot_state (
  telegram_id BIGINT PRIMARY KEY,
  state VARCHAR(60) DEFAULT NULL,
  payload TEXT DEFAULT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

SET FOREIGN_KEY_CHECKS=1;