-- ============================================================
-- DATABASE PEMASUKAN — Multi Perusahaan, Multi User
-- Jalankan di phpMyAdmin (import) pada database kosong
-- ============================================================

SET NAMES utf8mb4;
SET time_zone = '+07:00';

-- Perusahaan
CREATE TABLE IF NOT EXISTS perusahaan_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama_perusahaan VARCHAR(100) NOT NULL,
  kode_perusahaan VARCHAR(10) NOT NULL,
  alamat TEXT NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_kode (kode_perusahaan)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- User + role: superadmin | admin_utama | admin | pekerja
CREATE TABLE IF NOT EXISTS users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NULL,
  mikrotik_api_id INT NULL,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(255) NOT NULL,
  nama_lengkap VARCHAR(100) NOT NULL,
  email VARCHAR(100) NULL,
  no_telp VARCHAR(25) NULL,
  role ENUM('superadmin','admin_utama','admin','pekerja') NOT NULL DEFAULT 'pekerja',
  akses_pembukuan TINYINT(1) NOT NULL DEFAULT 1,
  akses_absensi TINYINT(1) NOT NULL DEFAULT 0,
  ikut_gaji TINYINT(1) NOT NULL DEFAULT 1,
  status TINYINT(1) NOT NULL DEFAULT 1,
  fcm_token VARCHAR(255) NULL,
  last_login DATETIME NULL,
  last_activity DATETIME NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_username (username),
  KEY idx_perusahaan (perusahaan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Konfigurasi Mikrotik per perusahaan
CREATE TABLE IF NOT EXISTS mikrotik_api (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  nama VARCHAR(100) NULL,
  ip_address VARCHAR(100) NOT NULL,
  port INT NOT NULL DEFAULT 8728,
  username VARCHAR(100) NOT NULL,
  password VARCHAR(255) NOT NULL,
  is_connected TINYINT(1) NOT NULL DEFAULT 0,
  status TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_perusahaan (perusahaan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Klien
CREATE TABLE IF NOT EXISTS list_client (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  nama VARCHAR(100) NOT NULL,
  no_telp VARCHAR(30) NULL,
  pppoe VARCHAR(100) NULL,
  password_pppoe VARCHAR(100) NULL,
  profile VARCHAR(100) NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_perusahaan (perusahaan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Data instalasi (untuk status isolir)
CREATE TABLE IF NOT EXISTS installation_data (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  client_id INT NOT NULL,
  isolir_status ENUM('Tanggal','Bulanan','Nonisolir') NOT NULL DEFAULT 'Bulanan',
  tanggal_pemasangan DATE NULL,
  KEY idx_client (client_id, perusahaan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Hierarki jaringan
CREATE TABLE IF NOT EXISTS network_olt (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  nama_olt VARCHAR(100) NOT NULL,
  status TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS network_pon (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  olt_id INT NOT NULL,
  nomor_pon VARCHAR(20) NOT NULL,
  nama_pon VARCHAR(100) NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  KEY idx_olt (olt_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS network_odp (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  pon_id INT NOT NULL,
  nama_odp VARCHAR(100) NOT NULL,
  status TINYINT(1) NOT NULL DEFAULT 1,
  KEY idx_pon (pon_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pemasukan
CREATE TABLE IF NOT EXISTS pemasukan (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  user_id INT NULL,
  klien_id INT NULL,
  nominal DECIMAL(15,2) NOT NULL DEFAULT 0,
  kategori VARCHAR(50) NOT NULL,
  bulan TINYINT NOT NULL,
  tahun SMALLINT NOT NULL,
  tanggal DATE NOT NULL,
  keterangan TEXT NULL,
  pppoe VARCHAR(100) NULL,
  profile VARCHAR(100) NULL,
  olt_id INT NULL,
  pon_id INT NULL,
  odp_id INT NULL,
  sumber ENUM('manual','ipaymu') NOT NULL DEFAULT 'manual',
  metode_bayar VARCHAR(30) NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_periode (perusahaan_id, tahun, bulan),
  KEY idx_user (user_id),
  KEY idx_klien (klien_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Pengeluaran
CREATE TABLE IF NOT EXISTS pengeluaran (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  user_id INT NULL,
  nominal DECIMAL(15,2) NOT NULL DEFAULT 0,
  kategori VARCHAR(50) NOT NULL,
  bulan TINYINT NOT NULL,
  tahun SMALLINT NOT NULL,
  tanggal DATE NOT NULL,
  keterangan TEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_periode (perusahaan_id, tahun, bulan),
  KEY idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Transfer saldo antar user (SATU perusahaan saja, tidak boleh lintas perusahaan)
CREATE TABLE IF NOT EXISTS transfer (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  dari_user_id INT NOT NULL,
  ke_user_id INT NOT NULL,
  nominal DECIMAL(15,2) NOT NULL,
  keterangan VARCHAR(255) NULL,
  metode ENUM('cash','transfer_bank') NOT NULL DEFAULT 'cash',
  dokumen VARCHAR(255) NULL,
  status ENUM('pending','completed','rejected','cancelled') NOT NULL DEFAULT 'pending',
  tanggal DATE NOT NULL,
  created_by INT NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  responded_at DATETIME NULL,
  KEY idx_perusahaan (perusahaan_id),
  KEY idx_dari (dari_user_id),
  KEY idx_ke (ke_user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Splash screen (gambar pembuka, tampil maks. 2x/hari per user di dashboard)
CREATE TABLE IF NOT EXISTS splash_screens (
  id INT AUTO_INCREMENT PRIMARY KEY,
  perusahaan_id INT NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  file_size INT NOT NULL DEFAULT 0,
  width INT NOT NULL DEFAULT 0,
  height INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  uploaded_by INT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_perusahaan (perusahaan_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SEED AWAL (aman dijalankan berkali-kali — pakai INSERT IGNORE)
-- Password semua akun contoh: admin123
-- Hash dibuat dengan password_hash('admin123', PASSWORD_DEFAULT)
-- ============================================================
INSERT IGNORE INTO perusahaan_config (nama_perusahaan, kode_perusahaan) VALUES
('Perusahaan Contoh', 'PC');

INSERT IGNORE INTO users (perusahaan_id, username, password, nama_lengkap, role) VALUES
(NULL, 'superadmin', '$2b$10$yuwt4RpXadvn965qHouiY.guHKCxqyAtsa1E4HIUbNGfkBX6JyGfS', 'Super Admin', 'superadmin'),
(1, 'adminutama', '$2b$10$yuwt4RpXadvn965qHouiY.guHKCxqyAtsa1E4HIUbNGfkBX6JyGfS', 'Admin Utama', 'admin_utama'),
(1, 'admin1', '$2b$10$yuwt4RpXadvn965qHouiY.guHKCxqyAtsa1E4HIUbNGfkBX6JyGfS', 'Admin Satu', 'admin'),
(1, 'pekerja1', '$2b$10$yuwt4RpXadvn965qHouiY.guHKCxqyAtsa1E4HIUbNGfkBX6JyGfS', 'Pekerja Satu', 'pekerja');