-- create_tables.sql

CREATE TABLE `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL UNIQUE,
  `identity_number` VARCHAR(100) DEFAULT NULL,
  `phone_number` VARCHAR(20) DEFAULT NULL,
  `address` TEXT DEFAULT NULL,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `role` ENUM('Administrator', 'Manager', 'Reseller/Operator') NOT NULL,
  `status` ENUM('Active', 'Suspended') NOT NULL DEFAULT 'Active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `routers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `ip_address` VARCHAR(45) NOT NULL,
  `api_port` INT DEFAULT 8728,
  `api_username` VARCHAR(100) NOT NULL,
  `api_password` VARCHAR(255) NOT NULL, -- Sebaiknya dienkripsi
  `radius_secret` VARCHAR(255) NOT NULL,
  `auth_port` INT DEFAULT 1812,
  `acct_port` INT DEFAULT 1813,
  `timezone` VARCHAR(100) DEFAULT 'Asia/Jakarta',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `packages` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `speed` VARCHAR(50) NOT NULL,
  `quota` VARCHAR(50) DEFAULT 'Unlimited',
  `price` DECIMAL(10, 2) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE `customers` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(255) NOT NULL,
  `package_id` INT,
  `address` TEXT NOT NULL,
  `latitude` VARCHAR(50) DEFAULT NULL,
  `longitude` VARCHAR(50) DEFAULT NULL,
  `phone_number` VARCHAR(20) NOT NULL,
  `email` VARCHAR(255) DEFAULT NULL,
  `identity_number` VARCHAR(100) DEFAULT NULL,
  `username` VARCHAR(100) NOT NULL UNIQUE,
  `status` ENUM('Active', 'Suspended', 'Inactive') NOT NULL DEFAULT 'Active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`package_id`) REFERENCES `packages`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `odp_pop` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `type` ENUM('ODP', 'POP') NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `area` VARCHAR(255) DEFAULT NULL,
  `latitude` VARCHAR(50) NOT NULL,
  `longitude` VARCHAR(50) NOT NULL,
  `owner_id` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE `settings` (
  `setting_key` VARCHAR(100) PRIMARY KEY,
  `setting_value` TEXT NOT NULL
) ENGINE=InnoDB;

-- Insert default settings
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('google_maps_api_key', ''),
('maps_default_lat', '-6.200000'),
('maps_default_lng', '106.816666');

-- Insert default admin user
-- Password: admin
INSERT INTO `users` (`name`, `email`, `username`, `password`, `role`, `status`) VALUES
('Admin', 'admin@example.com', 'admin', '$2y$10$tZ2yL.J2zP9C5Q3A/I3G/uF9.m2P/WkY0mR9eG8a8J.g3p5c/Q8bS', 'Administrator', 'Active');