CREATE TABLE plans (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(80) NOT NULL,
  price DECIMAL(12,2) NOT NULL DEFAULT 0,
  days INT NOT NULL DEFAULT 30,
  brand_limit INT NOT NULL DEFAULT 1,
  wallet_limit INT NOT NULL DEFAULT 5,
  device_limit INT NOT NULL DEFAULT 1,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  plan_id INT UNSIGNED NULL,
  plan_expires DATETIME NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (plan_id) REFERENCES plans(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE brands (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uid INT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  brand_key VARCHAR(80) NOT NULL UNIQUE,
  logo VARCHAR(255) DEFAULT '',
  messenger_support VARCHAR(255) DEFAULT '',
  whatsapp_number VARCHAR(30) DEFAULT '',
  support_mail VARCHAR(190) DEFAULT '',
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(uid) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE wallets (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uid INT UNSIGNED NOT NULL,
  brand_id INT UNSIGNED NULL,
  type VARCHAR(40) NOT NULL,
  number VARCHAR(40) NOT NULL,
  label VARCHAR(120) DEFAULT '',
  category ENUM('mobile','bank','international') NOT NULL DEFAULT 'mobile',
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(uid) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(brand_id) REFERENCES brands(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE devices (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uid INT UNSIGNED NOT NULL,
  device_key VARCHAR(120) NOT NULL UNIQUE,
  device_ip VARCHAR(64) DEFAULT '',
  address VARCHAR(120) DEFAULT '',
  last_seen DATETIME NULL,
  status TINYINT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY(uid) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE invoices (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uid INT UNSIGNED NOT NULL,
  brand_id INT UNSIGNED NOT NULL,
  amount DECIMAL(12,2) NOT NULL,
  transaction_id VARCHAR(80) NOT NULL UNIQUE,
  cus_name VARCHAR(120) DEFAULT '',
  cus_email VARCHAR(190) DEFAULT '',
  success_url TEXT,
  cancel_url TEXT,
  webhook_url TEXT,
  metadata LONGTEXT,
  status ENUM('PENDING','COMPLETED','CANCELLED','ERROR') NOT NULL DEFAULT 'PENDING',
  wallet_type VARCHAR(40) DEFAULT '',
  sms_id VARCHAR(80) DEFAULT '',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  FOREIGN KEY(uid) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY(brand_id) REFERENCES brands(id) ON DELETE CASCADE,
  INDEX(status), INDEX(uid), INDEX(wallet_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sms_data (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uid INT UNSIGNED NOT NULL,
  device_key VARCHAR(120) NOT NULL,
  address VARCHAR(120) DEFAULT '',
  message TEXT NOT NULL,
  trx_id VARCHAR(80) DEFAULT '',
  amount DECIMAL(12,2) DEFAULT NULL,
  processed TINYINT NOT NULL DEFAULT 0,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_sms(device_key, message(191), received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE settings (
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  uid INT UNSIGNED NULL,
  skey VARCHAR(80) NOT NULL,
  svalue TEXT,
  UNIQUE KEY uniq_setting(uid,skey)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO plans(name,price,days,brand_limit,wallet_limit,device_limit) VALUES
('Basic',0,30,1,3,1),('Pro',0,30,3,10,2);