-- -----------------------------------------------------
--  Database: sherif_cloud
--  Schema + seed untuk Sherif Cloud
-- -----------------------------------------------------

CREATE DATABASE IF NOT EXISTS `sherif_cloud`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `sherif_cloud`;

-- -----------------------------------------------------
--  Tabel: users
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(191) NOT NULL UNIQUE,
  `password_hash` VARCHAR(255) NOT NULL,
  `is_admin` TINYINT(1) NOT NULL DEFAULT 0,
  `pterodactyl_id` INT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
--  Tabel: packages
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `packages` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `price` DECIMAL(10,2) NOT NULL DEFAULT 0,
  `memory` INT NOT NULL,
  `disk` INT NOT NULL,
  `is_free` TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed paket default: Free, Basic, Pro

INSERT INTO `packages` (`name`, `slug`, `price`, `memory`, `disk`, `is_free`) VALUES
  ('Free',  'free',  0,     256, 1024, 1),
  ('Basic', 'basic', 15000, 512,  2048, 0),
  ('Pro',   'pro',   30000, 1024, 4096, 0)
ON DUPLICATE KEY UPDATE
  `name` = VALUES(`name`),
  `price` = VALUES(`price`),
  `memory` = VALUES(`memory`),
  `disk` = VALUES(`disk`),
  `is_free` = VALUES(`is_free`);

-- -----------------------------------------------------
--  Tabel: servers
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `servers` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `package_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `pterodactyl_id` INT NULL,
  `identifier` VARCHAR(32) NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'deploying',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`package_id`) REFERENCES `packages`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
--  Tabel: payments (untuk integrasi Pakasir)
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `payments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `package_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `provider` VARCHAR(50) NOT NULL,
  `external_id` VARCHAR(191) NULL,
  `redirect_url` VARCHAR(191) NULL,
  `reference` VARCHAR(191) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`package_id`) REFERENCES `packages`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
--  Tabel: vps_plans
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `vps_plans` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `slug` VARCHAR(100) NOT NULL UNIQUE,
  `price` DECIMAL(10,2) NOT NULL,
  `size_slug` VARCHAR(50) NOT NULL,
  `memory` INT NOT NULL,
  `disk` INT NOT NULL,
  `cpu` INT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `vps_plans` (`name`, `slug`, `price`, `size_slug`, `memory`, `disk`, `cpu`) VALUES
  ('VPS Pelajar 1', 'vps-student-1', 35000, 's-1vcpu-1gb', 1024, 25, 1),
  ('VPS Pelajar 2', 'vps-student-2', 55000, 's-1vcpu-2gb', 2048, 50, 1),
  ('VPS Pelajar 3', 'vps-student-3', 85000, 's-2vcpu-2gb', 2048, 60, 2),
  ('VPS Menengah', 'vps-mid-4', 135000, 's-2vcpu-4gb', 4096, 80, 2),
  ('VPS Bisnis', 'vps-biz-8', 245000, 's-4vcpu-8gb', 8192, 160, 4),
  ('VPS Max 16GB', 'vps-max-16', 445000, 's-8vcpu-16gb', 16384, 320, 8)
ON DUPLICATE KEY UPDATE
  `name` = VALUES(`name`),
  `price` = VALUES(`price`),
  `size_slug` = VALUES(`size_slug`),
  `memory` = VALUES(`memory`),
  `disk` = VALUES(`disk`),
  `cpu` = VALUES(`cpu`);

-- -----------------------------------------------------
--  Tabel: vps_servers
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `vps_servers` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `vps_plan_id` INT UNSIGNED NOT NULL,
  `droplet_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(150) NOT NULL,
  `ip_address` VARCHAR(45) NULL,
  `status` VARCHAR(50) NOT NULL DEFAULT 'provisioning',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vps_plan_id`) REFERENCES `vps_plans`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
--  Tabel: vps_payments (pembayaran VPS via Pakasir)
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS `vps_payments` (
  `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT UNSIGNED NOT NULL,
  `vps_plan_id` INT UNSIGNED NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `provider` VARCHAR(50) NOT NULL,
  `external_id` VARCHAR(191) NULL,
  `redirect_url` VARCHAR(191) NULL,
  `reference` VARCHAR(191) NOT NULL,
  `server_name` VARCHAR(150) NOT NULL,
  `image_slug` VARCHAR(100) NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`vps_plan_id`) REFERENCES `vps_plans`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- -----------------------------------------------------
--  Opsional: buat akun admin awal (ganti email & password)
--  Password: ganti 'password-admin-anda' lalu generate hash via PHP:
--  password_hash('password-admin-anda', PASSWORD_BCRYPT)
--  dan isi hasilnya di kolom password_hash di bawah ini.
-- -----------------------------------------------------

-- INSERT INTO `users` (`name`, `email`, `password_hash`, `is_admin`)
-- VALUES ('Admin Sherif', 'admin@contoh.com', '$2y$10$HASH_DISINI', 1);
