-- =========================================================
-- NID Platform — সম্পূর্ণ ডাটাবেজ ইনস্টল স্ক্রিপ্ট
-- phpMyAdmin → আপনার ডাটাবেজ সিলেক্ট → SQL ট্যাব → পেস্ট → Go
-- একবারই চালাবেন।
-- =========================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ---------- ইউজার ----------
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  phone VARCHAR(30) DEFAULT NULL,
  password_hash VARCHAR(255) NOT NULL,
  role ENUM('user','admin') NOT NULL DEFAULT 'user',
  status ENUM('active','banned') NOT NULL DEFAULT 'active',
  balance DECIMAL(12,2) NOT NULL DEFAULT 0,
  total_recharge DECIMAL(12,2) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- API ----------
CREATE TABLE IF NOT EXISTS apis (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  url_template TEXT NOT NULL,
  api_key VARCHAR(255) DEFAULT NULL,
  api_id VARCHAR(255) DEFAULT NULL,
  method ENUM('GET','POST') NOT NULL DEFAULT 'GET',
  active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- সার্ভিস ----------
CREATE TABLE IF NOT EXISTS services (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  description TEXT DEFAULT NULL,
  api_id INT NULL,
  kind ENUM('api','custom') NOT NULL DEFAULT 'api',
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  active TINYINT(1) NOT NULL DEFAULT 1,
  pinned TINYINT(1) NOT NULL DEFAULT 0,
  status ENUM('open','closed','hidden') NOT NULL DEFAULT 'open',
  auto_access_link TEXT NULL,
  position INT NOT NULL DEFAULT 0,
  image VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (api_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- কাস্টম সার্ভিসের ফর্ম ফিল্ড ----------
CREATE TABLE IF NOT EXISTS service_fields (
  id INT AUTO_INCREMENT PRIMARY KEY,
  service_id INT NOT NULL,
  position INT NOT NULL DEFAULT 0,
  label VARCHAR(255) NOT NULL,
  field_type ENUM('text','select') NOT NULL DEFAULT 'text',
  placeholder VARCHAR(255) DEFAULT NULL,
  required TINYINT(1) NOT NULL DEFAULT 0,
  options_json TEXT NULL,
  INDEX (service_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- অর্ডার ----------
CREATE TABLE IF NOT EXISTS orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  service_id INT NULL,
  api_id INT NULL,
  nid VARCHAR(50) NULL,
  dob VARCHAR(20) NULL,
  response_json LONGTEXT NULL,
  custom_data LONGTEXT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  cached TINYINT(1) NOT NULL DEFAULT 0,
  note TEXT NULL,
  delivery_type ENUM('none','text','link','pdf','file') NOT NULL DEFAULT 'none',
  delivery_content LONGTEXT NULL,
  delivery_file VARCHAR(255) NULL,
  cancel_reason TEXT NULL,
  download_count INT NOT NULL DEFAULT 0,
  copy_count INT NOT NULL DEFAULT 0,
  user_notified TINYINT(1) NOT NULL DEFAULT 0,
  completed_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id), INDEX (service_id), INDEX (status), INDEX (nid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- NID ক্যাশ (ডুপ্লিকেট সার্চে টাকা কাটে না) ----------
CREATE TABLE IF NOT EXISTS nid_cache (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nid VARCHAR(50) NOT NULL,
  dob VARCHAR(20) NOT NULL,
  response_json LONGTEXT NULL,
  api_id INT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_nid_dob (nid, dob)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- রিচার্জ মেথড ----------
CREATE TABLE IF NOT EXISTS recharge_methods (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  number VARCHAR(60) DEFAULT NULL,
  type VARCHAR(40) DEFAULT NULL,
  instruction TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- রিচার্জ রিকোয়েস্ট ----------
CREATE TABLE IF NOT EXISTS recharges (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  method VARCHAR(120) DEFAULT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  txn_id VARCHAR(120) DEFAULT NULL,
  sender_number VARCHAR(60) DEFAULT NULL,
  status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
  admin_note TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id), INDEX (status), INDEX (txn_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ট্রানজেকশন লগ ----------
CREATE TABLE IF NOT EXISTS transactions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  type VARCHAR(40) NOT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  ref_id VARCHAR(64) NULL,
  note VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- নোটিশ ----------
CREATE TABLE IF NOT EXISTS notices (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(190) NOT NULL,
  body TEXT NULL,
  active TINYINT(1) NOT NULL DEFAULT 1,
  popup TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- সেটিংস ----------
CREATE TABLE IF NOT EXISTS settings (
  `key` VARCHAR(120) NOT NULL PRIMARY KEY,
  `value` TEXT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- Remember-me (৩০ দিন লগইন) ----------
CREATE TABLE IF NOT EXISTS remember_tokens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  selector VARCHAR(32) NOT NULL UNIQUE,
  token_hash VARCHAR(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------- ইউজার একটিভিটি ----------
CREATE TABLE IF NOT EXISTS user_activity (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  session_id VARCHAR(128) NOT NULL,
  ip VARCHAR(64) DEFAULT NULL,
  country VARCHAR(80) DEFAULT NULL,
  region VARCHAR(80) DEFAULT NULL,
  city VARCHAR(80) DEFAULT NULL,
  user_agent TEXT,
  browser VARCHAR(64) DEFAULT NULL,
  browser_version VARCHAR(32) DEFAULT NULL,
  os VARCHAR(64) DEFAULT NULL,
  device VARCHAR(32) DEFAULT NULL,
  login_at DATETIME DEFAULT NULL,
  last_seen_at DATETIME DEFAULT NULL,
  INDEX (user_id), INDEX (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =========================================================
-- ডিফল্ট ডাটা
-- =========================================================

-- ডিফল্ট সেটিংস
INSERT INTO settings(`key`,`value`) VALUES
 ('site_name','My Website'),
 ('contact',''),
 ('min_recharge','50'),
 ('telegram_link',''),
 ('whatsapp_link',''),
 ('telegram_bot_token',''),
 ('telegram_chat_id',''),
 ('auto_nid_price','0'),
 ('auto_nid_notice','')
ON DUPLICATE KEY UPDATE `value`=`value`;

-- "অটো NID মেক" এর hidden anchor সার্ভিস
INSERT INTO services(name,description,kind,price,active,status,position)
SELECT 'অটো NID মেক','Auto NID card generator','custom',0,0,'hidden',0
WHERE NOT EXISTS (SELECT 1 FROM services WHERE name='অটো NID মেক');

-- ডিফল্ট এডমিন — Email: admin@example.com | Password: admin123
-- ⚠️ লগইনের পরপরই Profile থেকে পাসওয়ার্ড পরিবর্তন করুন।
INSERT INTO users(name,email,phone,password_hash,role,status,balance)
SELECT 'Administrator','admin@example.com','','$2y$10$siMZAZUk0ZRyrJAoueTEzur8rjUTp0R4lyBuMpEk4gBSKiYslgSb.','admin','active',0
WHERE NOT EXISTS (SELECT 1 FROM users WHERE email='admin@example.com');

SET FOREIGN_KEY_CHECKS = 1;
