-- IkkonSewa full DB schema (import via phpMyAdmin)
CREATE DATABASE IF NOT EXISTS ikkonstore_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ikkonstore_db;

CREATE TABLE IF NOT EXISTS settings (k VARCHAR(128) PRIMARY KEY, v TEXT);
CREATE TABLE IF NOT EXISTS categories (id INT AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(64) UNIQUE, name VARCHAR(128), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE TABLE IF NOT EXISTS products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  category_id INT DEFAULT NULL,
  slug VARCHAR(64) UNIQUE,
  name VARCHAR(128),
  duration_days INT DEFAULT 30,
  price INT DEFAULT 0,
  description TEXT,
  files JSON DEFAULT NULL,
  max_files INT DEFAULT 1,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  tg_chat_id BIGINT UNIQUE,
  usn VARCHAR(64) UNIQUE,
  pw_hash VARCHAR(255),
  email VARCHAR(255),
  role ENUM('buyer','admin') DEFAULT 'buyer',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS payments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  order_id VARCHAR(128) UNIQUE,
  product_id INT,
  amount INT,
  kyta_txn_id VARCHAR(128),
  status ENUM('pending','paid','cancelled') DEFAULT 'pending',
  license_key VARCHAR(64),
  tg_chat_id BIGINT,
  user_id INT DEFAULT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expiry_date DATETIME,
  reviewed TINYINT DEFAULT 0,
  review_text TEXT
);

CREATE TABLE IF NOT EXISTS bots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  product_id INT,
  type VARCHAR(64),
  status ENUM('running','stopped','expired') DEFAULT 'stopped',
  config_path VARCHAR(255),
  expiry_date DATETIME,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS license_keys (
  id INT AUTO_INCREMENT PRIMARY KEY,
  license_key VARCHAR(128) UNIQUE NOT NULL,
  user_id INT DEFAULT NULL,
  product_id INT DEFAULT NULL,
  status ENUM('active','revoked','expired','paused') DEFAULT 'active',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  expiry_date DATETIME DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS security_snippets (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(128) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  encrypted_blob LONGTEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS submissions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  payment_id INT NOT NULL,
  user_id INT DEFAULT NULL,
  type ENUM('telegram_token','whatsapp_number') NOT NULL,
  value TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_sub (payment_id, type, value(191))
);

INSERT INTO settings (k,v) VALUES ('site_title','IkkonSewa - Rental Bot') ON DUPLICATE KEY UPDATE v=v;
