-- schema.sql
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(120) UNIQUE,
  password_hash VARCHAR(255),
  ref_code VARCHAR(16) UNIQUE,
  referred_by INT NULL,
  referred_at DATETIME NULL,
  wallet_balance DECIMAL(12,2) DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  INDEX(referred_by)
);

CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) UNIQUE,
  password_hash VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(64) PRIMARY KEY,
  `value` TEXT
);

CREATE TABLE IF NOT EXISTS banners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  image_url VARCHAR(255),
  link_url VARCHAR(255),
  active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type ENUM('direct','social') NOT NULL,
  title VARCHAR(200) NOT NULL,
  action_label VARCHAR(50) DEFAULT 'Visit',
  url VARCHAR(255) NOT NULL,
  coins DECIMAL(10,2) DEFAULT 0,
  duration_seconds INT NULL, -- direct এর ক্ষেত্রে লাগবে
  requires_proof TINYINT(1) DEFAULT 0,
  active TINYINT(1) DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS task_completions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  task_id INT NOT NULL,
  status ENUM('pending','approved','rejected') DEFAULT 'approved',
  proof_text VARCHAR(255) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_user_task (user_id, task_id)
);

CREATE TABLE IF NOT EXISTS ad_views (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  viewed_at DATETIME NOT NULL,
  INDEX(user_id, viewed_at)
);

CREATE TABLE IF NOT EXISTS wallet_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  source VARCHAR(50),
  note VARCHAR(255),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS withdrawals (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  method VARCHAR(50),
  account_no VARCHAR(100),
  amount DECIMAL(12,2),
  status ENUM('pending','approved','cancelled') DEFAULT 'pending',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS tickets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  subject VARCHAR(200),
  message TEXT,
  status ENUM('open','closed') DEFAULT 'open',
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS ticket_replies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ticket_id INT NOT NULL,
  from_admin TINYINT(1) DEFAULT 0,
  message TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ডিফল্ট সেটিংস
INSERT IGNORE INTO settings(`key`,`value`) VALUES
('scroll_notice','সাইটে স্বাগতম! নিয়ম মেনে কাজ করুন।'),
('support_team_url','https://t.me/your_support_team'),
('support_group_url','https://t.me/your_support_group'),
('work_rules_html','<ul><li>ভুল প্রুফ দিলে ব্যান হতে পারেন</li><li>একই কাজ বারবার করবেন না</li></ul>'),
('ad_coin_reward','5'),
('ad_cooldown_minutes','5'),
('ref_percent','5'),
('ref_days','90');

-- ডিফল্ট অ্যাডমিন
INSERT IGNORE INTO admin_users(username, password_hash)
VALUES ('admin', PASSWORD('admin123'));

/* নোট: কিছু MySQL-এ PASSWORD() ডিপ্রিকেটেড। প্রয়োজনে ম্যানুয়ালি PHP দিয়ে হ্যাশ করে আপডেট দিন। */