-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               8.0.30 - MySQL Community Server - GPL
-- Server OS:                    Win64
-- HeidiSQL Version:             12.1.0.6537
-- --------------------------------------------------------

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;


-- Dumping database structure for newdb
CREATE DATABASE IF NOT EXISTS `newdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;
USE `newdb`;

-- Dumping structure for table newdb.admin
CREATE TABLE IF NOT EXISTS `admin` (
  `id_admin` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `nama` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `no_telefon` varchar(20) DEFAULT NULL,
  `password` varchar(255) NOT NULL,
  `profile_picture` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_admin`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table newdb.admin: ~1 rows (approximately)
DELETE FROM `admin`;
INSERT INTO `admin` (`id_admin`, `username`, `nama`, `email`, `no_telefon`, `password`, `profile_picture`) VALUES
	(1, 'nabil', 'Nabil', 'nabilkudin44@gmail.com', '0193120022', '44', NULL);

-- Dumping structure for table newdb.aduan
CREATE TABLE IF NOT EXISTS `aduan` (
  `id_aduan` int NOT NULL AUTO_INCREMENT,
  `id_penyewa` int NOT NULL,
  `tajuk` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `aduan` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `status` enum('Baru','Dalam Proses','Selesai') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Baru',
  `tarikh` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_aduan`),
  KEY `idx_aduan_penyewa` (`id_penyewa`),
  CONSTRAINT `aduan_ibfk_1` FOREIGN KEY (`id_penyewa`) REFERENCES `penyewa` (`id_penyewa`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table newdb.aduan: ~0 rows (approximately)
DELETE FROM `aduan`;

-- Dumping structure for table newdb.bayaran
CREATE TABLE IF NOT EXISTS `bayaran` (
  `id_bayaran` int NOT NULL AUTO_INCREMENT,
  `id_sewa` int DEFAULT NULL,
  `bulan` varchar(20) DEFAULT NULL,
  `tahun` int DEFAULT NULL,
  `tarikh_bayar` datetime DEFAULT NULL,
  `jumlah` decimal(10,2) DEFAULT NULL,
  `status` enum('Lunas','Belum Lunas','Pending') DEFAULT 'Pending',
  `bill_code` varchar(50) DEFAULT NULL,
  `ref_no` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id_bayaran`),
  KEY `id_sewa` (`id_sewa`),
  CONSTRAINT `bayaran_ibfk_1` FOREIGN KEY (`id_sewa`) REFERENCES `sewa` (`id_sewa`)
) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table newdb.bayaran: ~0 rows (approximately)
DELETE FROM `bayaran`;

-- Dumping structure for table newdb.dokumen_penyewa
CREATE TABLE IF NOT EXISTS `dokumen_penyewa` (
  `id_dokumen` int NOT NULL AUTO_INCREMENT,
  `id_penyewa` int NOT NULL,
  `ic_file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `akuan_janji_file` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `tarikh_upload` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_dokumen`),
  UNIQUE KEY `unique_dokumen_penyewa` (`id_penyewa`),
  CONSTRAINT `dokumen_penyewa_ibfk_1` FOREIGN KEY (`id_penyewa`) REFERENCES `penyewa` (`id_penyewa`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table newdb.dokumen_penyewa: ~0 rows (approximately)
DELETE FROM `dokumen_penyewa`;

-- Dumping structure for table newdb.kewangan_bulanan
CREATE TABLE IF NOT EXISTS `kewangan_bulanan` (
  `id_kewangan` int NOT NULL AUTO_INCREMENT,
  `bulan` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `tahun` int NOT NULL,
  `total_sewa_dijangka` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total_kutipan` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total_tunggakan` decimal(12,2) NOT NULL DEFAULT '0.00',
  `bilangan_penyewa` int NOT NULL DEFAULT '0',
  `bilangan_rumah_disewa` int NOT NULL DEFAULT '0',
  `tarikh_dijana` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_kewangan`),
  UNIQUE KEY `unique_bulan_tahun` (`bulan`,`tahun`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table newdb.kewangan_bulanan: ~3 rows (approximately)
DELETE FROM `kewangan_bulanan`;
INSERT INTO `kewangan_bulanan` (`id_kewangan`, `bulan`, `tahun`, `total_sewa_dijangka`, `total_kutipan`, `total_tunggakan`, `bilangan_penyewa`, `bilangan_rumah_disewa`, `tarikh_dijana`) VALUES
	(10, 'Ogos', 2026, 0.00, 0.00, 0.00, 0, 0, '2026-09-10 22:45:50'),
	(13, 'September', 2026, 1.00, 0.00, 1.00, 1, 1, '2026-09-10 22:59:09'),
	(14, 'Oktober', 2026, 3.00, 0.00, 3.00, 2, 2, '2026-09-10 22:59:52');

-- Dumping structure for table newdb.kewangan_penyewa
CREATE TABLE IF NOT EXISTS `kewangan_penyewa` (
  `id_kewangan_penyewa` int NOT NULL AUTO_INCREMENT,
  `id_kewangan` int NOT NULL,
  `id_sewa` int NOT NULL,
  `id_penyewa` int NOT NULL,
  `nama_penyewa` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `no_rumah` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `sewa_bulanan` decimal(10,2) NOT NULL DEFAULT '0.00',
  `status_bayaran` enum('Lunas','Belum Lunas','Pending') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'Belum Lunas',
  `tarikh_bayar` datetime DEFAULT NULL,
  PRIMARY KEY (`id_kewangan_penyewa`),
  UNIQUE KEY `unique_kewangan_sewa` (`id_kewangan`,`id_sewa`),
  KEY `idx_kewangan_penyewa` (`id_penyewa`),
  KEY `kewangan_penyewa_ibfk_2` (`id_sewa`),
  CONSTRAINT `kewangan_penyewa_ibfk_1` FOREIGN KEY (`id_kewangan`) REFERENCES `kewangan_bulanan` (`id_kewangan`) ON DELETE CASCADE,
  CONSTRAINT `kewangan_penyewa_ibfk_2` FOREIGN KEY (`id_sewa`) REFERENCES `sewa` (`id_sewa`) ON DELETE CASCADE,
  CONSTRAINT `kewangan_penyewa_ibfk_3` FOREIGN KEY (`id_penyewa`) REFERENCES `penyewa` (`id_penyewa`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table newdb.kewangan_penyewa: ~3 rows (approximately)
DELETE FROM `kewangan_penyewa`;
INSERT INTO `kewangan_penyewa` (`id_kewangan_penyewa`, `id_kewangan`, `id_sewa`, `id_penyewa`, `nama_penyewa`, `no_rumah`, `sewa_bulanan`, `status_bayaran`, `tarikh_bayar`) VALUES
	(35, 13, 14, 6, 'ADAM ZAHIN BIN ABDULLAH', '44F', 1.00, 'Belum Lunas', NULL),
	(36, 14, 14, 6, 'ADAM ZAHIN BIN ABDULLAH', '44F', 1.00, 'Belum Lunas', NULL),
	(37, 14, 15, 10, 'MOHAMMAD ASHRAF', '55B', 2.00, 'Belum Lunas', NULL);

-- Dumping structure for table newdb.notifikasi
CREATE TABLE IF NOT EXISTS `notifikasi` (
  `id_notif` int NOT NULL AUTO_INCREMENT,
  `id_penyewa` int DEFAULT NULL,
  `mesej` text NOT NULL,
  `jenis` enum('info','warning','success','danger') DEFAULT 'info',
  `tarikh` datetime DEFAULT CURRENT_TIMESTAMP,
  `status` enum('Belum Baca','Sudah Baca') DEFAULT 'Belum Baca',
  PRIMARY KEY (`id_notif`),
  KEY `id_penyewa` (`id_penyewa`),
  KEY `idx_notifikasi_penyewa_tarikh` (`id_penyewa`,`tarikh`),
  CONSTRAINT `notifikasi_ibfk_penyewa` FOREIGN KEY (`id_penyewa`) REFERENCES `penyewa` (`id_penyewa`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table newdb.notifikasi: ~2 rows (approximately)
DELETE FROM `notifikasi`;
INSERT INTO `notifikasi` (`id_notif`, `id_penyewa`, `mesej`, `jenis`, `tarikh`, `status`) VALUES
	(3, 6, 'Bil sewa September 2026 telah dijana. Sila semak dan buat bayaran sebelum tarikh akhir.', 'info', '2026-09-10 22:46:38', 'Belum Baca'),
	(4, 6, 'Bil sewa Oktober 2026 telah dijana. Sila semak dan buat bayaran sebelum tarikh akhir.', 'info', '2026-09-10 22:46:59', 'Belum Baca');

-- Dumping structure for table newdb.penyewa
CREATE TABLE IF NOT EXISTS `penyewa` (
  `id_penyewa` int NOT NULL AUTO_INCREMENT,
  `nama` varchar(100) NOT NULL,
  `no_ic` varchar(20) NOT NULL,
  `no_telefon` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `email` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `password` varchar(255) NOT NULL,
  `profile_picture` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id_penyewa`),
  UNIQUE KEY `no_ic` (`no_ic`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table newdb.penyewa: ~2 rows (approximately)
DELETE FROM `penyewa`;
INSERT INTO `penyewa` (`id_penyewa`, `nama`, `no_ic`, `no_telefon`, `email`, `password`, `profile_picture`) VALUES
	(6, 'ADAM ZAHIN BIN ABDULLAH', '060511031031', '0196492394', 'adamzahin511@gmail.com', '$2y$10$d7mhKca23H8Pq.DvB94gh.R.4izYekQ726VSBcerhcanXfNw4uali', NULL),
	(10, 'MOHAMMAD ASHRAF', '061121-01-0491', '0172175965', 'acap@gmail.com', '$2y$10$gwtnjxAT0zc3eCsj0NfYs.7./vlAzEzP1lBdSTWwiqiUt3pSMJf.W', NULL);

-- Dumping structure for table newdb.rumah
CREATE TABLE IF NOT EXISTS `rumah` (
  `id_rumah` int NOT NULL AUTO_INCREMENT,
  `no_rumah` varchar(20) DEFAULT NULL,
  `harga_sewa` decimal(10,2) DEFAULT NULL,
  `status` enum('Kosong','Disewa') DEFAULT 'Kosong',
  PRIMARY KEY (`id_rumah`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table newdb.rumah: ~2 rows (approximately)
DELETE FROM `rumah`;
INSERT INTO `rumah` (`id_rumah`, `no_rumah`, `harga_sewa`, `status`) VALUES
	(6, '44F', 1.00, 'Disewa'),
	(9, '55B', 2.00, 'Disewa');

-- Dumping structure for table newdb.sewa
CREATE TABLE IF NOT EXISTS `sewa` (
  `id_sewa` int NOT NULL AUTO_INCREMENT,
  `id_penyewa` int DEFAULT NULL,
  `id_rumah` int DEFAULT NULL,
  `tarikh_masuk` date DEFAULT NULL,
  `deposit` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`id_sewa`),
  KEY `id_penyewa` (`id_penyewa`),
  KEY `id_rumah` (`id_rumah`),
  CONSTRAINT `sewa_ibfk_1` FOREIGN KEY (`id_penyewa`) REFERENCES `penyewa` (`id_penyewa`),
  CONSTRAINT `sewa_ibfk_2` FOREIGN KEY (`id_rumah`) REFERENCES `rumah` (`id_rumah`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Dumping data for table newdb.sewa: ~2 rows (approximately)
DELETE FROM `sewa`;
INSERT INTO `sewa` (`id_sewa`, `id_penyewa`, `id_rumah`, `tarikh_masuk`, `deposit`) VALUES
	(14, 6, 6, '2026-09-10', 0.00),
	(15, 10, 9, '2026-10-10', 0.00);

/*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */;
/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;