-- https://www.phpmyadmin.net/
--

START TRANSACTION;



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

--
-- Table structure for table `info_warga`
--

CREATE TABLE `info_warga` (
  `id` int(11) NOT NULL,
  `judul` varchar(200) NOT NULL,
  `deskripsi` text NOT NULL,
  `lokasi` varchar(255) NOT NULL,
  `kategori` enum('Info','Pemberitahuan') NOT NULL DEFAULT 'Info',
  `tanggal_mulai` date NOT NULL,
  `tanggal_selesai` date DEFAULT NULL,
  `foto` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `info_warga`
--

INSERT INTO `info_warga` (`id`, `judul`, `deskripsi`, `lokasi`, `kategori`, `tanggal_mulai`, `tanggal_selesai`, `foto`, `created_at`, `updated_at`) VALUES
(1, 'Pemeliharaan Jalan RT 05', 'Pengaspalan ulang jalan utama RT 05 sepanjang 500 meter untuk meningkatkan kenyamanan warga', 'Jl. Mawar RT 05 RW 02', 'Info', '2025-12-20', '2025-12-27', NULL, '2025-12-16 21:28:59', '2025-12-16 21:28:59'),
(2, 'Gotong Royong Bersama', 'Kegiatan bersih-bersih lingkungan dan penanaman pohon di area taman RW 02', 'Taman RW 02', 'Info', '2025-12-22', '2025-12-22', NULL, '2025-12-16 21:28:59', '2025-12-16 21:28:59'),
(3, 'Perbaikan Saluran Air', 'Normalisasi dan pembersihan saluran air untuk mencegah banjir di musim hujan', 'Jl. Melati RT 03 RW 02', 'Info', '2025-12-18', '2025-12-25', NULL, '2025-12-16 21:28:59', '2025-12-16 21:28:59'),
(4, 'Jalan rusak', 'adwadwadwadad', 'jl.syarif', 'Info', '2025-12-26', '2025-12-22', NULL, '2025-12-17 02:54:38', '2025-12-17 02:54:38'),
(6, 'jalan di perbaiki ', 'sedang diperbaiki warga', 'di depan masjid', 'Pemberitahuan', '2025-12-18', '2025-12-30', NULL, '2025-12-17 12:05:20', '2025-12-17 12:05:20');

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

--
-- Table structure for table `laporan`
--

CREATE TABLE `laporan` (
  `id` int(11) NOT NULL,
  `kode_laporan` varchar(20) NOT NULL,
  `nama_pelapor` varchar(100) NOT NULL,
  `no_hp` varchar(15) NOT NULL,
  `kategori` varchar(50) NOT NULL,
  `deskripsi` text NOT NULL,
  `lokasi` varchar(255) NOT NULL,
  `foto` varchar(255) DEFAULT NULL,
  `foto_blob` mediumblob DEFAULT NULL,
  `foto_mime` varchar(50) DEFAULT NULL,
  `status` enum('Diterima','Diproses','Selesai') DEFAULT 'Diterima',
  `tanggal_lapor` timestamp NOT NULL DEFAULT current_timestamp(),
  `tanggal_diterima` timestamp NULL DEFAULT NULL,
  `tanggal_diproses` timestamp NULL DEFAULT NULL,
  `tanggal_selesai` datetime DEFAULT NULL,
  `biaya` decimal(15,2) DEFAULT NULL,
  `durasi` varchar(50) DEFAULT NULL,
  `foto_after` varchar(255) DEFAULT NULL,
  `foto_after_blob` mediumblob DEFAULT NULL,
  `foto_after_mime` varchar(50) DEFAULT NULL,
  `catatan_admin` text DEFAULT NULL,
  `diterima_at` timestamp NULL DEFAULT NULL COMMENT 'Timestamp ketika status Diterima',
  `diproses_at` timestamp NULL DEFAULT NULL COMMENT 'Timestamp ketika status Diproses',
  `selesai_at` timestamp NULL DEFAULT NULL COMMENT 'Timestamp ketika status Selesai'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `laporan`
--

INSERT INTO `laporan` (`id`, `kode_laporan`, `nama_pelapor`, `no_hp`, `kategori`, `deskripsi`, `lokasi`, `foto`, `foto_blob`, `foto_mime`, `status`, `tanggal_lapor`, `tanggal_diterima`, `tanggal_diproses`, `tanggal_selesai`, `biaya`, `durasi`, `foto_after`, `foto_after_blob`, `foto_after_mime`, `catatan_admin`, `diterima_at`, `diproses_at`, `selesai_at`) VALUES
(1, 'LPR-20251201-001', 'Budi Santoso', '081234567890', 'Jalan Rusak', 'Jalan berlubang besar di depan rumah nomor 15', 'Jl. Kenanga No. 15 RT 01', NULL, NULL, NULL, 'Selesai', '2025-12-16 21:28:59', '2025-12-16 21:28:59', '2025-12-17 21:28:59', '2025-12-10 15:30:00', 2500000.00, '3 hari', NULL, NULL, NULL, NULL, '2025-12-16 21:28:59', '2025-12-17 21:28:59', '2025-12-10 08:30:00'),
(2, 'LPR-20251205-002', 'Siti Nurhaliza', '082345678901', 'Lampu Jalan', 'Lampu jalan mati sudah 1 minggu', 'Jl. Anggrek RT 02', NULL, NULL, NULL, 'Selesai', '2025-12-16 21:28:59', '2025-12-16 21:28:59', '2025-12-17 21:28:59', '2025-12-12 10:00:00', 350000.00, '1 hari', NULL, NULL, NULL, NULL, '2025-12-16 21:28:59', '2025-12-17 21:28:59', '2025-12-12 03:00:00'),
(3, 'LPR-20251210-003', 'Ahmad Hidayat', '083456789012', 'Saluran Air', 'Saluran air tersumbat sampah', 'Jl. Dahlia RT 03', NULL, NULL, NULL, 'Selesai', '2025-12-16 21:28:59', '2025-12-16 21:28:59', '2025-12-17 21:28:59', '2025-12-17 10:38:38', 50000.00, '3 hari', NULL, NULL, NULL, 'dsaasasdadads', '2025-12-16 21:28:59', '2025-12-17 21:28:59', '2025-12-17 03:38:38'),
(4, 'LPR-20251216-397', 'Akbar', '082136190863', 'Jalan Rusak', 'adhauhuiahadhakshdhauiuahaiuhcxjkhadski', 'jl.syarif', NULL, NULL, NULL, 'Selesai', '2025-12-16 21:32:34', '2025-12-16 21:32:34', '2025-12-17 21:32:34', '2025-12-17 10:04:16', 50000.00, '3 hari', NULL, NULL, NULL, 'dasadasdadas', '2025-12-16 21:32:34', '2025-12-17 21:32:34', '2025-12-17 03:04:16'),
(5, 'LPR-20251217-717', 'akbar', '0858313013', 'Saluran Air', 'dajiowjiwoajaijwaoijdaoijdawioja', 'jl.syarif', NULL, NULL, NULL, 'Selesai', '2025-12-17 02:20:45', '2025-12-17 02:20:45', '2025-12-18 02:20:45', '2025-12-17 10:00:21', NULL, NULL, NULL, NULL, NULL, 'adsadsad', '2025-12-17 02:20:45', '2025-12-18 02:20:45', '2025-12-17 03:00:21'),
(6, 'LAP-57A3DB', 'Akbar3', '0858313013', 'Lampu Jalan', 'ddasdasdsadasdasdasdasdvxzczxccxz', 'jl.syarifdada', NULL, NULL, NULL, 'Selesai', '2025-12-17 03:57:16', '2025-12-17 03:57:16', '2025-12-18 03:57:16', '2025-12-17 11:14:43', 700000.00, '3 hari', NULL, NULL, NULL, 'sajOaisaiSJIOSAOSIDJIOASJD', '2025-12-17 03:57:16', '2025-12-18 03:57:16', '2025-12-17 04:14:43'),
(7, 'LAP-202512179376', 'Akbar3', '0858313013', 'Sampah', 'dassaasasdadasdsadasdascxzcz', 'jl.syarifdadadasasd', NULL, NULL, NULL, 'Diterima', '2025-12-16 22:08:48', '2025-12-16 22:08:48', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2025-12-16 22:08:48', NULL, NULL),
(8, 'LAP-202512177036', 'Akbar3', '0858313013', 'Lampu Jalan', 'dsadasdsadadsadsadasas', 'jl.syarifdadadasasddasad', NULL, NULL, NULL, 'Diterima', '2025-12-16 22:13:04', '2025-12-16 22:13:04', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2025-12-16 22:13:04', NULL, NULL),
(9, 'LAP-202512179583', 'akbar', '0858313013', 'Saluran Air', 'casdadasdasdasdasdadsdasd', 'jl.syarifdadadasasd', NULL, NULL, NULL, 'Diterima', '2025-12-16 22:13:50', '2025-12-16 22:13:50', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2025-12-16 22:13:50', NULL, NULL),
(10, 'LAP-202512175704', 'edelyn', '081277898486', 'Pohon', 'ada pohon tumbang yang menghalangi jalan', 'jl.Salma', NULL, NULL, NULL, 'Selesai', '2025-12-16 23:46:04', '2025-12-16 23:46:04', '2025-12-17 23:46:04', '2025-12-17 12:48:23', 5000000.00, '3 hari', NULL, NULL, NULL, 'sudah di angkut pohon tumbangnya', '2025-12-16 23:46:04', '2025-12-17 23:46:04', '2025-12-17 05:48:23'),
(11, 'LAP-202512179331', 'Akbar', '0858313013', 'Jalan Rusak', '8cxjz≈ç∆z7yjyz7ycjzczyjzcjz7y7z', 'jl.syarifdada', NULL, NULL, NULL, 'Diproses', '2025-12-17 00:29:13', '2025-12-17 00:29:13', '2025-12-18 00:29:13', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2025-12-17 00:29:13', '2025-12-18 00:29:13', NULL),
(12, 'LAP-202512173360', 'Akbar', '0858313013', 'Jalan Rusak', 'jalan rusak di dekat balai kota', 'jl.syarif', NULL, NULL, NULL, 'Selesai', '2025-12-17 00:55:15', '2025-12-17 00:55:15', '2025-12-18 00:55:15', '2025-12-17 13:58:59', 5000000.00, '3 hari', NULL, NULL, NULL, 'jalan sudah diperbaiki', '2025-12-17 00:55:15', '2025-12-18 00:55:15', '2025-12-17 06:58:59'),
(13, 'LAP-202512176896', 'Akbar', '0858313013', 'Saluran Air', 'adalwjaljdawjlkajalwkjdaljaljda', 'jl.Salma', NULL, NULL, NULL, 'Diproses', '2025-12-17 02:41:39', '2025-12-17 02:41:39', '2025-12-18 02:41:39', NULL, NULL, NULL, NULL, NULL, NULL, NULL, '2025-12-17 02:41:39', '2025-12-18 02:41:39', NULL),
(14, 'LAP-202512177584', 'akbar', '0858313013', 'Lampu Jalan', 'hihiuhihiuhiuhihuuhiuhihuiuhiuhiuhiuhuihhuiuhi', 'jl.Salma', 'lap_20251217_123821_78e3e2b7.jpg', NULL, NULL, 'Selesai', '2025-12-17 05:38:21', '2025-12-17 05:38:21', '2025-12-18 05:38:21', '2025-12-17 18:39:23', 50000000.00, '3 hari', 'after_20251217_123923_8ed6a5f0.jpg', NULL, NULL, 'sudah selesai', '2025-12-17 05:38:21', '2025-12-18 05:38:21', '2025-12-17 11:39:23');
INSERT INTO `laporan` (`id`, `kode_laporan`, `nama_pelapor`, `no_hp`, `kategori`, `deskripsi`, `lokasi`, `foto`, `foto_blob`, `foto_mime`, `status`, `tanggal_lapor`, `tanggal_diterima`, `tanggal_diproses`, `tanggal_selesai`, `biaya`, `durasi`, `foto_after`, `foto_after_blob`, `foto_after_mime`, `catatan_admin`, `diterima_at`, `diproses_at`, `selesai_at`) VALUES
(15, 'LAP-202512177493', 'Akbar Pratama', '082136190863', 'Saluran Air', 'adkwadkowpaopkdapwkowpakdpaokkopdawkdpowa', 'jl auhduwaiwhd', NULL, 0x[BLOB]