CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  phone VARCHAR(32) NOT NULL UNIQUE,
  username VARCHAR(80) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  pin_hash VARCHAR(255) DEFAULT NULL,
  invite_code VARCHAR(32) NOT NULL UNIQUE,
  invited_by_code VARCHAR(32) DEFAULT NULL,
  balance DECIMAL(14,2) NOT NULL DEFAULT 0,
  usdt_balance DECIMAL(14,4) NOT NULL DEFAULT 0,
  commission DECIMAL(14,2) NOT NULL DEFAULT 0,
  selling_enabled TINYINT(1) NOT NULL DEFAULT 1,
  status TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  token CHAR(64) NOT NULL UNIQUE,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(user_id),
  CONSTRAINT fk_user_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(80) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  role VARCHAR(30) NOT NULL DEFAULT 'admin',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO admins (id, username, password_hash, role)
VALUES (1, 'admin', '240be518fabd2724ddb6f04eeb1da5967448d7e831c08c8fa822809f74c720a9', 'master');

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

INSERT IGNORE INTO settings (setting_key, setting_value) VALUES
('money_rate','1'),
('usdt_recharge_rate','90'),
('min_order_amount','100'),
('max_order_amount','50000'),
('cashback_rate','0'),
('usdt_cashback_rate','0'),
('night_bonus_enabled','0'),
('night_bonus_rate','0'),
('pay_bank_name','Demo Bank'),
('pay_account_name','DDPay Merchant'),
('pay_upi','demo@upi'),
('service_url','https://t.me/'),
('channel_url','https://t.me/');

CREATE TABLE IF NOT EXISTS ct_types (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ct_type VARCHAR(50) NOT NULL,
  name VARCHAR(100) NOT NULL,
  pay_type VARCHAR(50) NOT NULL DEFAULT 'upi',
  logo VARCHAR(255) DEFAULT '',
  min_receive_amount DECIMAL(14,2) NOT NULL DEFAULT 100,
  max_receive_amount DECIMAL(14,2) NOT NULL DEFAULT 50000,
  max_select INT NOT NULL DEFAULT 3,
  multi TINYINT(1) NOT NULL DEFAULT 1,
  need_pin TINYINT(1) NOT NULL DEFAULT 1,
  need_send_sms TINYINT(1) NOT NULL DEFAULT 0,
  guide_url VARCHAR(255) DEFAULT '',
  is_reward TINYINT(1) NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO ct_types (id, ct_type, name, pay_type, min_receive_amount, max_receive_amount, sort_order) VALUES
(1,'upi','UPI','upi',100,50000,1),
(2,'bank','Bank-INR','bank',500,100000,2),
(3,'usdt','USDT','usdt',10,10000,3),
(4,'paytm','Paytm','upi',100,50000,4),
(5,'phonepe','PhonePe','upi',100,50000,5),
(6,'freecharge','Freecharge','upi',100,50000,6);

CREATE TABLE IF NOT EXISTS wallets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type VARCHAR(50) NOT NULL DEFAULT 'upi',
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(32) DEFAULT '',
  upi VARCHAR(120) DEFAULT '',
  bank_name VARCHAR(120) DEFAULT '',
  account_name VARCHAR(120) DEFAULT '',
  ifsc VARCHAR(30) DEFAULT '',
  min_amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  max_amount DECIMAL(14,2) NOT NULL DEFAULT 50000,
  status TINYINT(1) NOT NULL DEFAULT 1,
  selling_enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(user_id),
  CONSTRAINT fk_wallets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  wallet_id INT DEFAULT NULL,
  order_no VARCHAR(64) NOT NULL UNIQUE,
  type VARCHAR(30) NOT NULL DEFAULT 'inr',
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  status VARCHAR(30) NOT NULL DEFAULT 'pending',
  voucher_url VARCHAR(255) DEFAULT '',
  remark VARCHAR(255) DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT NULL,
  INDEX(user_id),
  INDEX(status),
  CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS token_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  amount DECIMAL(14,2) NOT NULL DEFAULT 0,
  type VARCHAR(50) NOT NULL DEFAULT 'system',
  remark VARCHAR(255) DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX(user_id),
  CONSTRAINT fk_token_history_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  content TEXT NOT NULL,
  type VARCHAR(40) NOT NULL DEFAULT 'notice',
  popup TINYINT(1) NOT NULL DEFAULT 0,
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO notices (id, title, content, type, popup, enabled) VALUES
(1, 'Welcome', 'Welcome to DDPay', 'notice', 1, 1);

CREATE TABLE IF NOT EXISTS banners (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  image VARCHAR(255) NOT NULL,
  link VARCHAR(255) DEFAULT '',
  type VARCHAR(40) NOT NULL DEFAULT 'home',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(255) DEFAULT '',
  link VARCHAR(255) NOT NULL,
  logo VARCHAR(255) DEFAULT '',
  value VARCHAR(255) DEFAULT '',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO services (id, name, description, link, value, enabled) VALUES
(1, 'Telegram Support', 'Customer support', 'https://t.me/', 'https://t.me/', 1);

CREATE TABLE IF NOT EXISTS content_pages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  type VARCHAR(60) NOT NULL DEFAULT 'tutorial',
  title VARCHAR(160) NOT NULL,
  content TEXT NOT NULL,
  file_url VARCHAR(255) DEFAULT '',
  cover VARCHAR(255) DEFAULT '',
  tag VARCHAR(80) DEFAULT '',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(160) NOT NULL,
  reward DECIMAL(14,2) NOT NULL DEFAULT 0,
  channel_url VARCHAR(255) DEFAULT '',
  enabled TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;