-- ============================================================
-- Shakil's Homes — Flat Management System
-- Full Database Schema v1.0
-- MySQL 8.0+
-- ============================================================


CREATE DATABASE IF NOT EXISTS `shakils_homes`
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE `shakils_homes`;

-- ─── FLATS ───────────────────────────────────────────────────
CREATE TABLE `flats` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `flat_number` VARCHAR(20)     NOT NULL UNIQUE,
  `block`       VARCHAR(10)     DEFAULT NULL,
  `floor`       VARCHAR(20)     DEFAULT NULL,
  `bedrooms`    INT             NOT NULL DEFAULT 2,
  `bathrooms`   INT             NOT NULL DEFAULT 1,
  `size_sqft`   DECIMAL(8,2)    DEFAULT NULL,
  `description` TEXT            DEFAULT NULL,
  `status`      ENUM('vacant','occupied') NOT NULL DEFAULT 'vacant',
  `created_at`  TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── TENANTS ─────────────────────────────────────────────────
CREATE TABLE `tenants` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id`          BIGINT UNSIGNED NOT NULL,
  `flat_id`          BIGINT UNSIGNED NOT NULL,
  `monthly_rent`     DECIMAL(10,2)   NOT NULL,
  `service_charge`   DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `security_deposit` DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `lease_start`      DATE            NOT NULL,
  `lease_end`        DATE            DEFAULT NULL,
  `remarks`          TEXT            DEFAULT NULL,
  `status`           ENUM('active','inactive') NOT NULL DEFAULT 'active',
  `created_at`       TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`flat_id`) REFERENCES `flats`(`id`) ON DELETE CASCADE,
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── METER READINGS ──────────────────────────────────────────
CREATE TABLE `meter_readings` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`        BIGINT UNSIGNED NOT NULL,
  `flat_id`          BIGINT UNSIGNED NOT NULL,
  `meter_type`       ENUM('electricity','gas','water') NOT NULL DEFAULT 'electricity',
  `previous_reading` DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `current_reading`  DECIMAL(10,2)   NOT NULL,
  `units_consumed`   DECIMAL(10,2)   AS (`current_reading` - `previous_reading`) STORED,
  `unit_rate`        DECIMAL(8,2)    NOT NULL DEFAULT 8.00,
  `bill_amount`      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `reading_date`     DATE            NOT NULL,
  `reading_month`    DATE            NOT NULL,
  `photo_proof`      VARCHAR(255)    DEFAULT NULL,
  `notes`            TEXT            DEFAULT NULL,
  `recorded_by`      BIGINT UNSIGNED NOT NULL,
  `created_at`       TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`tenant_id`)   REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`flat_id`)     REFERENCES `flats`(`id`)   ON DELETE CASCADE,
  FOREIGN KEY (`recorded_by`) REFERENCES `users`(`id`),
  INDEX `idx_reading_month` (`reading_month`),
  INDEX `idx_meter_type`    (`meter_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── BILLS ───────────────────────────────────────────────────
CREATE TABLE `bills` (
  `id`               BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`        BIGINT UNSIGNED NOT NULL,
  `flat_id`          BIGINT UNSIGNED NOT NULL,
  `invoice_number`   VARCHAR(50)     NOT NULL UNIQUE,
  `rent_amount`      DECIMAL(10,2)   NOT NULL,
  `service_charge`   DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `electricity_bill` DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `gas_bill`         DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `water_bill`       DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `late_fee`         DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `other_charges`    DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `discount`         DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `total_amount`     DECIMAL(10,2)   NOT NULL,
  `bill_month`       DATE            NOT NULL,
  `due_date`         DATE            NOT NULL,
  `status`           ENUM('unpaid','paid','overdue','partial') NOT NULL DEFAULT 'unpaid',
  `paid_amount`      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
  `paid_at`          TIMESTAMP       DEFAULT NULL,
  `notes`            TEXT            DEFAULT NULL,
  `meter_reading_id` BIGINT UNSIGNED DEFAULT NULL,
  `created_at`       TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  `updated_at`       TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`tenant_id`)        REFERENCES `tenants`(`id`)        ON DELETE CASCADE,
  FOREIGN KEY (`flat_id`)          REFERENCES `flats`(`id`)          ON DELETE CASCADE,
  FOREIGN KEY (`meter_reading_id`) REFERENCES `meter_readings`(`id`) ON DELETE SET NULL,
  INDEX `idx_bill_month` (`bill_month`),
  INDEX `idx_status`     (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── PAYMENTS ────────────────────────────────────────────────
CREATE TABLE `payments` (
  `id`             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `bill_id`        BIGINT UNSIGNED NOT NULL,
  `tenant_id`      BIGINT UNSIGNED NOT NULL,
  `amount`         DECIMAL(10,2)   NOT NULL,
  `method`         ENUM('bkash','nagad','bank','cash') NOT NULL DEFAULT 'cash',
  `transaction_id` VARCHAR(100)    DEFAULT NULL,
  `sender_number`  VARCHAR(20)     DEFAULT NULL,
  `bank_name`      VARCHAR(100)    DEFAULT NULL,
  `receipt_number` VARCHAR(50)     DEFAULT NULL,
  `paid_at`        TIMESTAMP       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `status`         ENUM('pending','verified','rejected') NOT NULL DEFAULT 'pending',
  `proof_image`    VARCHAR(255)    DEFAULT NULL,
  `notes`          TEXT            DEFAULT NULL,
  `verified_by`    BIGINT UNSIGNED DEFAULT NULL,
  `verified_at`    TIMESTAMP       DEFAULT NULL,
  `created_at`     TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  `updated_at`     TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`bill_id`)     REFERENCES `bills`(`id`)   ON DELETE CASCADE,
  FOREIGN KEY (`tenant_id`)   REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`verified_by`) REFERENCES `users`(`id`)   ON DELETE SET NULL,
  INDEX `idx_method` (`method`),
  INDEX `idx_status` (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── MAINTENANCE REQUESTS ─────────────────────────────────────
CREATE TABLE `maintenance_requests` (
  `id`          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id`   BIGINT UNSIGNED NOT NULL,
  `flat_id`     BIGINT UNSIGNED NOT NULL,
  `subject`     VARCHAR(255)    NOT NULL,
  `description` TEXT            NOT NULL,
  `priority`    ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
  `status`      ENUM('pending','in_progress','resolved','rejected') NOT NULL DEFAULT 'pending',
  `image`       VARCHAR(255)    DEFAULT NULL,
  `admin_notes` TEXT            DEFAULT NULL,
  `resolved_at` TIMESTAMP       DEFAULT NULL,
  `created_at`  TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`flat_id`)   REFERENCES `flats`(`id`)   ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── NOTICES ─────────────────────────────────────────────────
CREATE TABLE `notices` (
  `id`         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `title`      VARCHAR(255)    NOT NULL,
  `body`       TEXT            NOT NULL,
  `type`       ENUM('general','urgent','maintenance','payment') NOT NULL DEFAULT 'general',
  `is_active`  TINYINT(1)      NOT NULL DEFAULT 1,
  `expires_at` DATE            DEFAULT NULL,
  `created_by` BIGINT UNSIGNED NOT NULL,
  `created_at` TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


INSERT INTO `flats` (`flat_number`, `block`, `floor`, `bedrooms`, `bathrooms`, `size_sqft`, `status`) VALUES
('A-101', 'A', 'Ground', 3, 2, 1200.00, 'occupied'),
('B-203', 'B', '2nd',    4, 2, 1600.00, 'occupied'),
('C-304', 'C', '3rd',    2, 1, 950.00,  'occupied'),
('D-401', 'D', '4th',    4, 3, 1800.00, 'vacant');

INSERT INTO `tenants` (`user_id`,`flat_id`,`monthly_rent`,`service_charge`,`security_deposit`,`lease_start`,`status`) VALUES
(2, 1, 14000.00, 2000.00, 28000.00, '2023-03-01', 'active'),
(3, 2, 18000.00, 2500.00, 36000.00, '2024-01-01', 'active'),
(4, 3, 12000.00, 1500.00, 24000.00, '2022-06-01', 'active');

INSERT INTO `meter_readings`
(`tenant_id`,`flat_id`,`meter_type`,`previous_reading`,`current_reading`,`unit_rate`,`bill_amount`,`reading_date`,`reading_month`,`recorded_by`) VALUES
(1,1,'electricity',1820.00,1950.00,8.00,1040.00,'2025-06-01','2025-06-01',1),
(2,2,'electricity',3100.00,3290.00,8.00,1520.00,'2025-06-01','2025-06-01',1),
(3,3,'electricity',2460.00,2570.00,8.00,880.00, '2025-06-01','2025-06-01',1),
(1,1,'gas',        540.00, 562.00, 12.50,275.00,'2025-06-01','2025-06-01',1),
(2,2,'gas',        890.00, 920.00, 12.50,375.00,'2025-06-01','2025-06-01',1);

INSERT INTO `bills`
(`tenant_id`,`flat_id`,`invoice_number`,`rent_amount`,`service_charge`,`electricity_bill`,`gas_bill`,`total_amount`,`bill_month`,`due_date`,`status`,`paid_amount`,`paid_at`) VALUES
(1,1,'INV-202506-0001',14000,2000,1040,275,17315,'2025-06-01','2025-06-10','paid',   17315,'2025-06-03 10:00:00'),
(2,2,'INV-202506-0002',18000,2500,1520,375,22395,'2025-06-01','2025-06-10','unpaid', 0,    NULL),
(3,3,'INV-202506-0003',12000,1500,880, 0,  14380,'2025-06-01','2025-06-05','overdue',0,    NULL);

INSERT INTO `payments`
(`bill_id`,`tenant_id`,`amount`,`method`,`transaction_id`,`sender_number`,`paid_at`,`status`,`verified_by`,`verified_at`) VALUES
(1,1,17315,'bkash','BK240601238','01712-345678','2025-06-03 10:00:00','verified',1,'2025-06-03 11:00:00');

INSERT INTO `notices` (`title`,`body`,`type`,`is_active`,`created_by`) VALUES
('Water Supply Interruption','Water supply will be off on 15 June 2025 from 9am-1pm for tank cleaning.','maintenance',1,1),
('June Rent Due Reminder','All tenants please pay June rent by 10th June. ৳500 late fee applies after due date.','payment',1,1);
