-- =====================================================================
-- Lisensi jasaSEOterdekat.com — Skema Database (MySQL 8.0)
-- =====================================================================
CREATE DATABASE IF NOT EXISTS `lisensi_jasaseoterdekat`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE `lisensi_jasaseoterdekat`;

-- ---------------------------------------------------------------------
-- Tabel users (hanya role Super Admin, tidak ada registrasi publik)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `role` ENUM('superadmin') NOT NULL DEFAULT 'superadmin',
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Tabel auth_tokens (Bearer token sederhana, tanpa dependency JWT)
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `auth_tokens` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` BIGINT UNSIGNED NOT NULL,
  `token` VARCHAR(128) NOT NULL UNIQUE,
  `expires_at` DATETIME NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  INDEX idx_token (`token`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Tabel licenses
-- status: pending  = baru didaftarkan dari aplikasi, menunggu aktivasi admin
--         active   = sudah diaktivasi admin, bisa dipakai
--         revoked  = dicabut admin, tidak bisa dipakai lagi
-- ---------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `licenses` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `hardware_id` VARCHAR(191) NOT NULL UNIQUE,
  `wa_number` VARCHAR(30) NOT NULL,
  `license_code` VARCHAR(40) NOT NULL UNIQUE,
  `status` ENUM('pending', 'active', 'revoked') NOT NULL DEFAULT 'pending',
  `note` VARCHAR(255) NULL,
  `activated_at` DATETIME NULL,
  `activated_by` BIGINT UNSIGNED NULL,
  `expires_at` DATETIME NULL COMMENT 'NULL = lifetime',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`activated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX idx_status (`status`),
  INDEX idx_wa (`wa_number`)
) ENGINE=InnoDB;

-- ---------------------------------------------------------------------
-- Seed akun Super Admin PERTAMA KALI dibuat lewat script PHP, BUKAN lewat
-- SQL ini — supaya hash password (bcrypt) dijamin valid dibuat oleh
-- password_hash() PHP di server Anda sendiri, bukan hash tebakan.
-- Jalankan setelah schema ini diimport:
--
--   php database/seed_admin.php
--
-- Lihat file database/seed_admin.php untuk detail & cara ganti username/password default.
-- ---------------------------------------------------------------------