CREATE DATABASE IF NOT EXISTS majuin_db DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE majuin_db;

CREATE TABLE packages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  description TEXT,
  price DECIMAL(12,2) NOT NULL,
  type ENUM('broadband','dedicated') NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  full_name VARCHAR(200) NOT NULL,
  address TEXT,
  phone VARCHAR(30),
  email VARCHAR(150),
  nik VARCHAR(30),
  selfie_path VARCHAR(255),
  ktp_path VARCHAR(255),
  package_id INT,
  city VARCHAR(100),
  status ENUM('waiting','approved','rejected','active') DEFAULT 'waiting',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (package_id) REFERENCES packages(id)
);

-- Sample packages
INSERT INTO packages (name, description, price, type) VALUES
('Broadband 20 Mbps', 'Paket broadband 20 Mbps tanpa dedicated SLA', 250000.00, 'broadband'),
('Dedicated 50 Mbps', 'Dedicated connection with SLA', 1500000.00, 'dedicated');
