-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Oct 02, 2025 at 05:00 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.0.30

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `isp_billing`
--

-- --------------------------------------------------------

--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `id` int(11) NOT NULL,
  `client_code` varchar(20) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `package_id` int(11) DEFAULT NULL,
  `phone` varchar(20) NOT NULL,
  `address` text NOT NULL,
  `status` enum('active','suspended','terminated') NOT NULL DEFAULT 'active',
  `registration_date` date NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `clients`
--

INSERT INTO `clients` (`id`, `client_code`, `full_name`, `package_id`, `phone`, `address`, `status`, `registration_date`, `created_at`) VALUES
(1, 'CLT2301A1', 'Andi Pratama', NULL, '081200111001', 'Jl. Melati No. 12, Jakarta', 'active', '2024-01-05', '2025-09-29 20:19:07'),
(3, 'CLT2301C3', 'Citra Lestari', NULL, '081200111003', 'Jl. Flamboyan No. 7, Surabaya', 'suspended', '2024-02-01', '2025-09-29 20:19:07'),
(4, 'CLT2301D4', 'Dewi Anggraini', NULL, '081200111004', 'Jl. Pahlawan No. 33, Semarang', 'active', '2024-02-15', '2025-09-29 20:19:07'),
(5, 'CLT2301E5', 'Eko Wibowo', NULL, '081200111005', 'Jl. Diponegoro No. 21, Medan', 'terminated', '2024-03-03', '2025-09-29 20:19:07'),
(6, 'CLT2301F6', 'Farah Kusuma', NULL, '081200111006', 'Jl. Anggrek No. 88, Yogyakarta', 'active', '2024-03-10', '2025-09-29 20:19:07'),
(7, 'CLT2301G7', 'Gilang Saputra', NULL, '081200111007', 'Jl. Hang Tuah No. 19, Palembang', 'active', '2024-03-25', '2025-09-29 20:19:07'),
(9, 'CLT2301I9', 'Imam Suryadi', NULL, '081200111009', 'Jl. Ahmad Yani No. 90, Makassar', 'active', '2024-04-18', '2025-09-29 20:19:07'),
(10, 'CLT2301J0', 'Joko Prakoso', NULL, '081200111010', 'Jl. Merdeka No. 56, Balikpapan', 'active', '2024-05-01', '2025-09-29 20:19:07'),
(12, 'C25100167A4', 'YOGA IMAWAN', NULL, '089680000165', 'Ciodeng Barat 003/007 Bojongmalaka, Baleendah', 'active', '2025-10-01', '2025-10-01 02:08:38'),
(13, 'C25100152DB', 'Bi Nining', NULL, '0897878778', 'Ciodeng', 'active', '2025-10-01', '2025-10-01 02:43:22'),
(14, 'C2510014329', 'Gugun Gunawan', NULL, '08657727373', 'Bahuan', 'active', '2025-10-01', '2025-10-01 02:46:54'),
(15, 'C25100166C4', 'Wawawa', NULL, '082240009898', 'Bandung', 'active', '2025-10-01', '2025-10-01 02:59:01'),
(16, 'C25100156A1', 'RAE', NULL, '082129292808', 'Ciodeng Barat', 'active', '2025-10-01', '2025-10-01 04:02:55'),
(17, 'C251001F335', 'WINDA', NULL, '082240009999', 'Ciodeng BARA', 'active', '2025-10-01', '2025-10-01 05:46:03');

-- --------------------------------------------------------

--
-- Table structure for table `packages`
--

CREATE TABLE `packages` (
  `id` int(11) NOT NULL,
  `package_name` varchar(100) NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `packages`
--

INSERT INTO `packages` (`id`, `package_name`, `price`, `description`, `status`, `created_at`) VALUES
(1, 'Basic 10 Mbps', 250000.00, 'Paket internet rumahan kecepatan 10 Mbps', 'active', '2025-09-29 20:19:07'),
(2, 'Family 20 Mbps', 320000.00, 'Paket keluarga dengan kecepatan 20 Mbps', 'active', '2025-09-29 20:19:07'),
(3, 'Premium 50 Mbps', 480000.00, 'Paket premium untuk kebutuhan streaming', 'active', '2025-09-29 20:19:07'),
(4, 'Gamer 75 Mbps', 560000.00, 'Paket khusus gamer dengan latency rendah', 'active', '2025-09-29 20:19:07'),
(5, 'Business 100 Mbps', 750000.00, 'Paket bisnis dengan SLA prioritas', 'active', '2025-09-29 20:19:07'),
(6, 'Corporate 150 Mbps', 950000.00, 'Paket korporasi dengan dukungan 24/7', 'active', '2025-09-29 20:19:07'),
(7, 'Starter 5 Mbps', 180000.00, 'Paket entry level untuk pengguna baru', 'inactive', '2025-09-29 20:19:07'),
(8, 'Hospitality 80 Mbps', 640000.00, 'Paket untuk hotel dan penginapan', 'active', '2025-09-29 20:19:07'),
(9, 'Education 30 Mbps', 360000.00, 'Paket sekolah dan kampus', 'active', '2025-09-29 20:19:07'),
(10, 'Event 200 Mbps', 1500000.00, 'Paket sementara untuk event dan pameran', 'inactive', '2025-09-29 20:19:07'),
(11, 'Murah', 166000.00, 'Murah', 'active', '2025-09-30 23:32:19'),
(12, '1000mbps', 2000000.00, 'Goo', 'active', '2025-10-01 02:43:54');

-- --------------------------------------------------------

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `client_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_status` enum('pending','paid','overdue') NOT NULL DEFAULT 'pending',
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `payment_date` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payments`
--

INSERT INTO `payments` (`id`, `invoice_number`, `client_id`, `amount`, `payment_status`, `invoice_date`, `due_date`, `payment_date`, `created_at`) VALUES
(1, 'INV-202401-001', 1, 250000.00, 'paid', '2024-01-05', '2024-01-12', '2024-01-10', '2025-09-29 20:19:07'),
(3, 'INV-202402-003', 3, 320000.00, 'pending', '2024-02-01', '2024-02-08', NULL, '2025-09-29 20:19:07'),
(4, 'INV-202402-004', 4, 480000.00, 'paid', '2024-02-15', '2024-02-22', '2024-02-20', '2025-09-29 20:19:07'),
(5, 'INV-202403-005', 5, 560000.00, 'overdue', '2024-03-03', '2024-03-10', NULL, '2025-09-29 20:19:07'),
(6, 'INV-202403-006', 6, 320000.00, 'paid', '2024-03-10', '2024-03-17', '2024-03-16', '2025-09-29 20:19:07'),
(7, 'INV-202403-007', 7, 750000.00, 'pending', '2024-03-25', '2024-04-01', NULL, '2025-09-29 20:19:07'),
(9, 'INV-202404-009', 9, 480000.00, 'paid', '2024-04-18', '2024-04-25', '2024-04-24', '2025-09-29 20:19:07'),
(10, 'INV-202405-010', 10, 320000.00, 'pending', '2024-05-01', '2024-05-08', NULL, '2025-09-29 20:19:07'),
(11, 'INV-062460FF-20250929', 1, 165000.00, 'paid', '2025-09-29', '2025-09-30', '2025-09-30', '2025-09-29 20:36:29'),
(12, 'INV-218FD19C-20251001', 4, 166000.00, 'paid', '2025-10-01', '2025-10-08', '2025-10-01', '2025-10-01 02:44:19'),
(13, 'INV-C0B23FE7-20251001', 7, 165000.00, 'paid', '2025-10-01', '2025-10-08', '2025-10-01', '2025-10-01 03:18:43'),
(14, 'INV-B6FC72F8-20251001', 1, 166000.00, 'paid', '2025-10-01', '2025-10-08', '2025-10-01', '2025-10-01 03:44:18'),
(15, 'INV-B111819D-20251001', 6, 166000.00, 'paid', '2025-10-01', '2025-10-08', '2025-10-01', '2025-10-01 04:03:39'),
(16, 'INV-8DA4DE15-20251001', 3, 166000.00, 'paid', '2025-10-01', '2025-10-08', '2025-10-01', '2025-10-01 05:46:23');

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `role` enum('admin','operator') NOT NULL DEFAULT 'operator',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `full_name`, `role`, `created_at`) VALUES
(2, 'operator', '$2y$10$CzOd/8TBGGINT3twDrKEEOJAQkZEjMkTNM1iBu0DSx9oiHDKlPrdO', 'Operator Billing', 'operator', '2025-09-29 20:19:07'),
(4, 'admin', '$2y$12$eW6fi4NdIgUbYOtpBM6Hf.iXlPAQw8W9snaKsAnwo.ECC0g2abhtW', 'Administrator', 'admin', '2025-09-30 23:04:53');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `clients`
--
ALTER TABLE `clients`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `client_code` (`client_code`),
  ADD KEY `idx_clients_status` (`status`),
  ADD KEY `fk_clients_package` (`package_id`);

--
-- Indexes for table `packages`
--
ALTER TABLE `packages`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_packages_status` (`status`);

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_number` (`invoice_number`),
  ADD KEY `idx_payments_status` (`payment_status`),
  ADD KEY `idx_payments_dates` (`invoice_date`,`due_date`),
  ADD KEY `fk_payments_client` (`client_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `clients`
--
ALTER TABLE `clients`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=18;

--
-- AUTO_INCREMENT for table `packages`
--
ALTER TABLE `packages`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=17;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `clients`
--
ALTER TABLE `clients`
  ADD CONSTRAINT `fk_clients_package` FOREIGN KEY (`package_id`) REFERENCES `packages` (`id`);

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `fk_payments_client` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
