-- ==============================================================================
-- महाराष्ट्र महानगरपालिका / नगरपरिषद संकुल व मालमत्ता महसूल व्यवस्थापन प्रणाली
-- MAHARASHTRA MUNICIPAL PROPERTY & REVENUE MANAGEMENT ERP PLATFORM (MahaMuniciProp)
-- Target RDBMS: MySQL 8.x (InnoDB, utf8mb4_unicode_ci, JSON, Spatial GIS)
-- Compatibility: Laravel 13 / PHP 8.5
-- ==============================================================================

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `audit_logs`;
DROP TABLE IF EXISTS `ai_anomalies_alerts`;
DROP TABLE IF EXISTS `defaulter_risk_scores`;
DROP TABLE IF EXISTS `dcb_monthly_summaries`;
DROP TABLE IF EXISTS `hall_bookings`;
DROP TABLE IF EXISTS `citizen_notifications`;
DROP TABLE IF EXISTS `service_applications`;
DROP TABLE IF EXISTS `maintenance_slas`;
DROP TABLE IF EXISTS `material_expenses`;
DROP TABLE IF EXISTS `work_executions`;
DROP TABLE IF EXISTS `estimates_approvals`;
DROP TABLE IF EXISTS `maintenance_work_orders`;
DROP TABLE IF EXISTS `maintenance_tickets`;
DROP TABLE IF EXISTS `contractors`;
DROP TABLE IF EXISTS `statutory_notices`;
DROP TABLE IF EXISTS `arrears_ledgers`;
DROP TABLE IF EXISTS `bank_reconciliations`;
DROP TABLE IF EXISTS `online_transactions`;
DROP TABLE IF EXISTS `payment_collections`;
DROP TABLE IF EXISTS `payment_receipts`;
DROP TABLE IF EXISTS `penalty_calculations`;
DROP TABLE IF EXISTS `miscellaneous_demands`;
DROP TABLE IF EXISTS `demand_bills`;
DROP TABLE IF EXISTS `demand_batches`;
DROP TABLE IF EXISTS `revenue_heads`;
DROP TABLE IF EXISTS `rent_rates`;
DROP TABLE IF EXISTS `lease_terminations`;
DROP TABLE IF EXISTS `agreement_documents`;
DROP TABLE IF EXISTS `lease_premiums`;
DROP TABLE IF EXISTS `security_deposits`;
DROP TABLE IF EXISTS `lease_renewals`;
DROP TABLE IF EXISTS `lease_approvals`;
DROP TABLE IF EXISTS `lease_agreements`;
DROP TABLE IF EXISTS `unauthorized_occupancies`;
DROP TABLE IF EXISTS `business_use_changes`;
DROP TABLE IF EXISTS `tenancy_mutations`;
DROP TABLE IF EXISTS `occupancies`;
DROP TABLE IF EXISTS `co_tenants`;
DROP TABLE IF EXISTS `tenant_kyc_documents`;
DROP TABLE IF EXISTS `tenants`;
DROP TABLE IF EXISTS `property_inspections`;
DROP TABLE IF EXISTS `asset_qr_codes`;
DROP TABLE IF EXISTS `property_gis_layers`;
DROP TABLE IF EXISTS `property_documents`;
DROP TABLE IF EXISTS `property_classifications`;
DROP TABLE IF EXISTS `units_galas`;
DROP TABLE IF EXISTS `floors`;
DROP TABLE IF EXISTS `buildings`;
DROP TABLE IF EXISTS `complexes`;
DROP TABLE IF EXISTS `properties_land`;
DROP TABLE IF EXISTS `document_templates`;
DROP TABLE IF EXISTS `financial_years`;
DROP TABLE IF EXISTS `workflow_definitions`;
DROP TABLE IF EXISTS `roles_permissions`;
DROP TABLE IF EXISTS `users`;
DROP TABLE IF EXISTS `designations`;
DROP TABLE IF EXISTS `departments`;
DROP TABLE IF EXISTS `wards`;
DROP TABLE IF EXISTS `zones`;
DROP TABLE IF EXISTS `ulb_bodies`;
SET FOREIGN_KEY_CHECKS = 1;

-- ------------------------------------------------------------------------------
-- 1. A. ADMINISTRATION & MASTERS (Modules 1 - 10)
-- ------------------------------------------------------------------------------

-- Mod 01: Municipal Corporation / Council Master
CREATE TABLE `ulb_bodies` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(20) NOT NULL UNIQUE,
    `name_en` VARCHAR(150) NOT NULL,
    `name_mr` VARCHAR(150) NOT NULL,
    `ulb_type` ENUM('MUNICIPAL_CORP_A', 'MUNICIPAL_CORP_B', 'MUNICIPAL_CORP_C', 'MUNICIPAL_CORP_D', 'COUNCIL_CLASS_A', 'COUNCIL_CLASS_B', 'COUNCIL_CLASS_C', 'NAGAR_PANCHAYAT') NOT NULL DEFAULT 'MUNICIPAL_CORP_C',
    `district` VARCHAR(60) NOT NULL,
    `division` VARCHAR(60) NOT NULL,
    `established_year` YEAR NULL,
    `address` TEXT NULL,
    `commissioner_name` VARCHAR(100) NULL,
    `email` VARCHAR(100) NULL,
    `phone` VARCHAR(30) NULL,
    `logo_url` VARCHAR(255) NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 03: Zone / Prabhag Master
CREATE TABLE `zones` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `zone_no` INT NOT NULL,
    `name_en` VARCHAR(100) NOT NULL,
    `name_mr` VARCHAR(100) NOT NULL,
    `office_address` TEXT NULL,
    `ward_officer_name` VARCHAR(100) NULL,
    `phone` VARCHAR(30) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_zones_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 02: Ward Master
CREATE TABLE `wards` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `zone_id` INT UNSIGNED NOT NULL,
    `ward_no` VARCHAR(20) NOT NULL,
    `name_en` VARCHAR(100) NOT NULL,
    `name_mr` VARCHAR(100) NOT NULL,
    `prabhag_samiti` VARCHAR(100) NULL,
    `population` INT UNSIGNED NULL,
    `area_sqkm` DECIMAL(8,2) NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_wards_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_wards_zone` FOREIGN KEY (`zone_id`) REFERENCES `zones` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 04: Department Master
CREATE TABLE `departments` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `code` VARCHAR(30) NOT NULL,
    `name_en` VARCHAR(100) NOT NULL,
    `name_mr` VARCHAR(100) NOT NULL,
    `head_designation` VARCHAR(100) NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT `fk_dept_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 05: Designation Master
CREATE TABLE `designations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `department_id` INT UNSIGNED NOT NULL,
    `title_en` VARCHAR(100) NOT NULL,
    `title_mr` VARCHAR(100) NOT NULL,
    `cadre` VARCHAR(50) NULL,
    `grade_pay` VARCHAR(30) NULL,
    CONSTRAINT `fk_desig_dept` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 06: User Master
CREATE TABLE `users` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `department_id` INT UNSIGNED NULL,
    `designation_id` INT UNSIGNED NULL,
    `name` VARCHAR(100) NOT NULL,
    `username` VARCHAR(60) NOT NULL UNIQUE,
    `email` VARCHAR(100) NOT NULL,
    `phone` VARCHAR(20) NOT NULL,
    `password_hash` VARCHAR(255) NOT NULL,
    `role` ENUM('COMMISSIONER', 'DY_COMMISSIONER', 'ESTATE_OFFICER', 'REVENUE_INSPECTOR', 'ACCOUNTS_OFFICER', 'PWD_ENGINEER', 'LEGAL_OFFICER', 'CASHIER', 'CITIZEN') NOT NULL DEFAULT 'ESTATE_OFFICER',
    `status` ENUM('ACTIVE', 'INACTIVE', 'SUSPENDED') NOT NULL DEFAULT 'ACTIVE',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_users_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 07: Role & Permission Management
CREATE TABLE `roles_permissions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `role` VARCHAR(50) NOT NULL,
    `module_code` VARCHAR(50) NOT NULL,
    `can_view` TINYINT(1) NOT NULL DEFAULT 1,
    `can_create` TINYINT(1) NOT NULL DEFAULT 0,
    `can_edit` TINYINT(1) NOT NULL DEFAULT 0,
    `can_delete` TINYINT(1) NOT NULL DEFAULT 0,
    `can_approve` TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY `uk_role_module` (`role`, `module_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 08: Workflow & Approval Master
CREATE TABLE `workflow_definitions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `workflow_type` ENUM('NEW_ALLOTMENT', 'MUTATION', 'RENEWAL', 'SEALING', 'MAINTENANCE_SANCTION') NOT NULL,
    `step_number` INT NOT NULL,
    `approver_role` VARCHAR(50) NOT NULL,
    `sla_days` INT NOT NULL DEFAULT 7,
    `is_final` TINYINT(1) NOT NULL DEFAULT 0,
    CONSTRAINT `fk_wf_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 09: Financial Year Master
CREATE TABLE `financial_years` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(20) NOT NULL UNIQUE,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `is_current` TINYINT(1) NOT NULL DEFAULT 0,
    `is_closed` TINYINT(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 10: Document/Letter Template Master
CREATE TABLE `document_templates` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(50) NOT NULL UNIQUE,
    `title_mr` VARCHAR(150) NOT NULL,
    `title_en` VARCHAR(150) NOT NULL,
    `category` ENUM('BILL', 'NOTICE', 'AGREEMENT', 'RECEIPT', 'ORDER') NOT NULL,
    `header_html` TEXT NULL,
    `body_template` LONGTEXT NOT NULL,
    `footer_html` TEXT NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 2. B. PROPERTY & COMPLEX (Modules 11 - 20)
-- ------------------------------------------------------------------------------

-- Mod 11: Land/Property Master
CREATE TABLE `properties_land` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `ward_id` INT UNSIGNED NOT NULL,
    `property_code` VARCHAR(50) NOT NULL UNIQUE,
    `survey_no` VARCHAR(60) NULL,
    `cts_no` VARCHAR(60) NULL,
    `gat_no` VARCHAR(60) NULL,
    `plot_no` VARCHAR(60) NULL,
    `title_ownership` ENUM('MUNICIPAL_OWNED', 'GOVT_LEASED', 'ACQUIRED_RESERVATION', 'DONATED') NOT NULL DEFAULT 'MUNICIPAL_OWNED',
    `reservation_purpose` VARCHAR(150) NULL,
    `land_area_sqm` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `property_card_no` VARCHAR(80) NULL,
    `mutation_entry_no` VARCHAR(80) NULL,
    `property_card_url` VARCHAR(255) NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_land_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_land_ward` FOREIGN KEY (`ward_id`) REFERENCES `wards` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 12: संकुल (Complex) Master
CREATE TABLE `complexes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `ward_id` INT UNSIGNED NOT NULL,
    `land_property_id` INT UNSIGNED NULL,
    `complex_code` VARCHAR(50) NOT NULL UNIQUE,
    `name_en` VARCHAR(150) NOT NULL,
    `name_mr` VARCHAR(150) NOT NULL,
    `category` ENUM('COMMERCIAL_COMPLEX', 'HAWKER_PLAZA', 'VEG_FISH_MARKET', 'MULTIPURPOSE_HALL', 'SPORTS_COMPLEX', 'MUNICIPAL_MALL', 'OFFICE_BUILDING') NOT NULL DEFAULT 'COMMERCIAL_COMPLEX',
    `total_buildings` INT NOT NULL DEFAULT 1,
    `total_units` INT NOT NULL DEFAULT 0,
    `construction_year` YEAR NULL,
    `ready_reckoner_rate_sqm` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `gis_latitude` DECIMAL(10,8) NULL,
    `gis_longitude` DECIMAL(11,8) NULL,
    `geojson_polygon` JSON NULL,
    `qr_code_uid` VARCHAR(64) NULL,
    `photo_url` VARCHAR(255) NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_complex_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_complex_ward` FOREIGN KEY (`ward_id`) REFERENCES `wards` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_complex_land` FOREIGN KEY (`land_property_id`) REFERENCES `properties_land` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 13: Building Master
CREATE TABLE `buildings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `complex_id` INT UNSIGNED NOT NULL,
    `building_code` VARCHAR(50) NOT NULL,
    `name_en` VARCHAR(100) NOT NULL,
    `name_mr` VARCHAR(100) NOT NULL,
    `construction_type` ENUM('RCC', 'LOAD_BEARING', 'STEEL_FRAME', 'PRE_FABRICATED') NOT NULL DEFAULT 'RCC',
    `total_floors` INT NOT NULL DEFAULT 1,
    `oc_number` VARCHAR(80) NULL,
    `oc_date` DATE NULL,
    `built_up_area_sqm` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT `fk_bldg_complex` FOREIGN KEY (`complex_id`) REFERENCES `complexes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 14: Floor Master
CREATE TABLE `floors` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `building_id` INT UNSIGNED NOT NULL,
    `floor_number` INT NOT NULL DEFAULT 0,
    `floor_name_en` VARCHAR(50) NOT NULL,
    `floor_name_mr` VARCHAR(50) NOT NULL,
    `total_units` INT NOT NULL DEFAULT 0,
    `floor_plan_url` VARCHAR(255) NULL,
    CONSTRAINT `fk_floor_bldg` FOREIGN KEY (`building_id`) REFERENCES `buildings` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 15: Unit/Shop/Gala Master
CREATE TABLE `units_galas` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `ward_id` INT UNSIGNED NOT NULL,
    `complex_id` INT UNSIGNED NOT NULL,
    `building_id` INT UNSIGNED NOT NULL,
    `floor_id` INT UNSIGNED NOT NULL,
    `unit_code` VARCHAR(60) NOT NULL UNIQUE,
    `unit_number` VARCHAR(30) NOT NULL,
    `unit_name` VARCHAR(100) NULL,
    `category` ENUM('SHOP', 'OFFICE', 'STALL', 'ATM', 'BANK', 'GODOWN', 'RESTAURANT', 'COMMUNITY_HALL', 'PLOT') NOT NULL DEFAULT 'SHOP',
    `length_ft` DECIMAL(6,2) NULL,
    `width_ft` DECIMAL(6,2) NULL,
    `carpet_area_sqft` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `built_up_area_sqft` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `carpet_area_sqm` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `shutter_size` VARCHAR(40) NULL,
    `front_facing` ENUM('MAIN_ROAD', 'INTERNAL_CORRIDOR', 'CORNER', 'BACKWARD') NOT NULL DEFAULT 'INTERNAL_CORRIDOR',
    `electricity_meter_no` VARCHAR(60) NULL,
    `water_connection_no` VARCHAR(60) NULL,
    `base_ready_reckoner_rent` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `current_market_rent` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `qr_code_hash` VARCHAR(64) NULL,
    `status` ENUM('VACANT', 'OCCUPIED', 'SEALED', 'UNDER_MAINTENANCE', 'DISPUTED') NOT NULL DEFAULT 'VACANT',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_unit_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_unit_ward` FOREIGN KEY (`ward_id`) REFERENCES `wards` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_unit_complex` FOREIGN KEY (`complex_id`) REFERENCES `complexes` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_unit_building` FOREIGN KEY (`building_id`) REFERENCES `buildings` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_unit_floor` FOREIGN KEY (`floor_id`) REFERENCES `floors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 16: Property Classification Master
CREATE TABLE `property_classifications` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `category_code` VARCHAR(40) NOT NULL UNIQUE,
    `name_en` VARCHAR(100) NOT NULL,
    `name_mr` VARCHAR(100) NOT NULL,
    `multiplier` DECIMAL(4,2) NOT NULL DEFAULT 1.00,
    `tax_percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 17: Property Photo & Document Repository
CREATE TABLE `property_documents` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `entity_type` ENUM('LAND', 'COMPLEX', 'BUILDING', 'UNIT') NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `doc_type` ENUM('SANCTION_PLAN', 'PROPERTY_CARD_7_12', 'OC_CERTIFICATE', 'PHOTO_FRONT', 'PHOTO_INTERNAL', 'LEGAL_DEED') NOT NULL,
    `title` VARCHAR(150) NOT NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `uploaded_by` VARCHAR(100) NULL,
    `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 18: Property GIS / Location Mapping
CREATE TABLE `property_gis_layers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `entity_type` ENUM('COMPLEX', 'UNIT') NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `geojson` JSON NOT NULL,
    `latitude` DECIMAL(10,8) NOT NULL,
    `longitude` DECIMAL(11,8) NOT NULL,
    `boundary_area_sqm` DECIMAL(12,2) NULL,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 19: Asset QR Code Management
CREATE TABLE `asset_qr_codes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL UNIQUE,
    `qr_uuid` VARCHAR(64) NOT NULL UNIQUE,
    `public_verification_url` VARCHAR(255) NOT NULL,
    `scan_count` INT UNSIGNED NOT NULL DEFAULT 0,
    `last_scanned_at` TIMESTAMP NULL,
    CONSTRAINT `fk_qr_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 20: Property Inspection & Verification
CREATE TABLE `property_inspections` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL,
    `inspector_id` INT UNSIGNED NOT NULL,
    `inspection_date` DATE NOT NULL,
    `physical_status` ENUM('RUNNING_SMOOTHLY', 'CLOSED_SHUTTER', 'SUB_LETTED', 'UNAUTHORIZED_ALTERATION', 'STRUCTURAL_HAZARD') NOT NULL,
    `actual_business` VARCHAR(150) NULL,
    `sub_letting_detected` TINYINT(1) NOT NULL DEFAULT 0,
    `unauthorized_extension_sqft` DECIMAL(8,2) NOT NULL DEFAULT 0.00,
    `remarks` TEXT NULL,
    `photo_url` VARCHAR(255) NULL,
    `action_recommended` VARCHAR(150) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_insp_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_insp_user` FOREIGN KEY (`inspector_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 3. C. TENANT & OCCUPANCY (Modules 21 - 28)
-- ------------------------------------------------------------------------------

-- Mod 21: Tenant / Allottee Master
CREATE TABLE `tenants` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `tenant_code` VARCHAR(50) NOT NULL UNIQUE,
    `full_name_en` VARCHAR(150) NOT NULL,
    `full_name_mr` VARCHAR(150) NOT NULL,
    `father_or_husband_name` VARCHAR(150) NULL,
    `mobile` VARCHAR(20) NOT NULL,
    `alternate_mobile` VARCHAR(20) NULL,
    `email` VARCHAR(100) NULL,
    `aadhaar_no_hash` VARCHAR(64) NULL,
    `pan_no` VARCHAR(20) NULL,
    `gst_no` VARCHAR(30) NULL,
    `residential_address` TEXT NOT NULL,
    `business_entity_type` ENUM('INDIVIDUAL', 'PROPRIETORSHIP', 'PARTNERSHIP', 'PVT_LTD', 'PUBLIC_LTD', 'SHG_BACHATGAT', 'DIVYANG_QUOTA') NOT NULL DEFAULT 'INDIVIDUAL',
    `category_quota` ENUM('GENERAL', 'SC', 'ST', 'OBC', 'DIVYANG', 'WOMEN_SHG', 'EX_SERVICEMEN') NOT NULL DEFAULT 'GENERAL',
    `kyc_status` ENUM('PENDING', 'VERIFIED', 'REJECTED') NOT NULL DEFAULT 'PENDING',
    `active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_tenant_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 22: Tenant KYC
CREATE TABLE `tenant_kyc_documents` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT UNSIGNED NOT NULL,
    `doc_type` ENUM('AADHAAR', 'PAN', 'SHOP_ACT_REG', 'GST_CERTIFICATE', 'CASTE_CERTIFICATE', 'PASSPORT_PHOTO', 'PARTNERSHIP_DEED') NOT NULL,
    `doc_number` VARCHAR(80) NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `verification_status` ENUM('SUBMITTED', 'VERIFIED', 'REJECTED') NOT NULL DEFAULT 'SUBMITTED',
    `verified_by` VARCHAR(100) NULL,
    `verified_at` TIMESTAMP NULL,
    CONSTRAINT `fk_kyc_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 23: Co-tenant / Partnership Management
CREATE TABLE `co_tenants` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT UNSIGNED NOT NULL,
    `full_name` VARCHAR(150) NOT NULL,
    `relationship` VARCHAR(50) NOT NULL,
    `share_percentage` DECIMAL(5,2) NOT NULL DEFAULT 50.00,
    `aadhaar_no` VARCHAR(20) NULL,
    `is_legal_heir` TINYINT(1) NOT NULL DEFAULT 0,
    CONSTRAINT `fk_cotenant_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 24: Occupancy Management
CREATE TABLE `occupancies` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `occupancy_type` ENUM('ALLOTTEE_DIRECT', 'SUB_LICENSEE', 'LEGAL_HEIR', 'UNAUTHORIZED') NOT NULL DEFAULT 'ALLOTTEE_DIRECT',
    `possession_date` DATE NOT NULL,
    `vacated_date` DATE NULL,
    `status` ENUM('ACTIVE', 'SURRENDERED', 'EVICTED', 'SEALED') NOT NULL DEFAULT 'ACTIVE',
    CONSTRAINT `fk_occ_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_occ_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 25: Transfer / Mutation of Tenancy
CREATE TABLE `tenancy_mutations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `unit_id` INT UNSIGNED NOT NULL,
    `from_tenant_id` INT UNSIGNED NOT NULL,
    `to_tenant_id` INT UNSIGNED NOT NULL,
    `mutation_type` ENUM('LEGAL_HEIR_WARAS', 'COMMERCIAL_TRANSFER', 'DEATH_SUCCESSION', 'PARTNERSHIP_CHANGE') NOT NULL,
    `application_no` VARCHAR(60) NOT NULL UNIQUE,
    `application_date` DATE NOT NULL,
    `transfer_premium_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `standing_committee_res_no` VARCHAR(80) NULL,
    `standing_committee_res_date` DATE NULL,
    `sanction_order_no` VARCHAR(80) NULL,
    `status` ENUM('APPLIED', 'SCRUTINY', 'PAYMENT_PENDING', 'APPROVED', 'REJECTED') NOT NULL DEFAULT 'APPLIED',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_mut_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_mut_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_mut_from` FOREIGN KEY (`from_tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_mut_to` FOREIGN KEY (`to_tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 26: Change of Business / Use Management
CREATE TABLE `business_use_changes` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `previous_use` VARCHAR(100) NOT NULL,
    `proposed_use` VARCHAR(100) NOT NULL,
    `change_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `order_number` VARCHAR(80) NULL,
    `order_date` DATE NULL,
    `status` ENUM('SUBMITTED', 'APPROVED', 'REJECTED') NOT NULL DEFAULT 'SUBMITTED',
    CONSTRAINT `fk_bc_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_bc_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 27: Unauthorized Occupancy Management
CREATE TABLE `unauthorized_occupancies` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL,
    `detected_person_name` VARCHAR(150) NOT NULL,
    `contact_phone` VARCHAR(30) NULL,
    `detection_date` DATE NOT NULL,
    `violation_reason` ENUM('SUB_LETTING_WITHOUT_NOC', 'EXPIRED_LEASE_OVERSTAY', 'TRESPASS_FORCEFUL_ENTRY') NOT NULL,
    `eviction_notice_no` VARCHAR(80) NULL,
    `notice_issued_date` DATE NULL,
    `fine_penalty_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('NOTICE_ISSUED', 'HEARING_SCHEDULED', 'POLICE_EVICTION_ORDERED', 'PROPERTY_REPOSSESSED') NOT NULL DEFAULT 'NOTICE_ISSUED',
    CONSTRAINT `fk_unauth_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 4. D. LEASE / AGREEMENT (Modules 29 - 35)
-- ------------------------------------------------------------------------------

-- Mod 29: Lease / Agreement Management
CREATE TABLE `lease_agreements` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `unit_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `agreement_number` VARCHAR(60) NOT NULL UNIQUE,
    `allotment_mode` ENUM('PUBLIC_E_AUCTION', 'TENDER', 'RESERVATION_QUOTA', 'COMPASSIONATE_GROUNDS', 'TRANSFER') NOT NULL DEFAULT 'PUBLIC_E_AUCTION',
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `tenure_months` INT NOT NULL DEFAULT 11,
    `monthly_rent` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `maintenance_charge` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `water_sanitation_cess` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `gst_rate_percent` DECIMAL(5,2) NOT NULL DEFAULT 18.00,
    `escalation_rate_percent` DECIMAL(5,2) NOT NULL DEFAULT 10.00,
    `escalation_frequency_months` INT NOT NULL DEFAULT 36,
    `security_deposit_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `premium_advance_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `sub_registrar_reg_no` VARCHAR(80) NULL,
    `stamp_duty_paid` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('DRAFT', 'UNDER_SCRUTINY', 'ACTIVE', 'RENEWAL_DUE', 'EXPIRED', 'TERMINATED', 'CANCELLED') NOT NULL DEFAULT 'ACTIVE',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_lease_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_lease_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_lease_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 30: Lease Approval Workflow
CREATE TABLE `lease_approvals` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lease_id` INT UNSIGNED NOT NULL,
    `step_number` INT NOT NULL,
    `approver_role` VARCHAR(50) NOT NULL,
    `approver_user_id` INT UNSIGNED NULL,
    `action` ENUM('APPROVED', 'REJECTED', 'QUERY_RAISED') NOT NULL,
    `remarks` TEXT NULL,
    `action_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_appr_lease` FOREIGN KEY (`lease_id`) REFERENCES `lease_agreements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 31: Renewal Management
CREATE TABLE `lease_renewals` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `old_lease_id` INT UNSIGNED NOT NULL,
    `new_lease_id` INT UNSIGNED NULL,
    `application_date` DATE NOT NULL,
    `revised_monthly_rent` DECIMAL(12,2) NOT NULL,
    `escalation_applied_pct` DECIMAL(5,2) NOT NULL DEFAULT 10.00,
    `standing_committee_res_no` VARCHAR(80) NULL,
    `status` ENUM('APPLIED', 'INSPECTION_PENDING', 'APPROVED', 'REJECTED') NOT NULL DEFAULT 'APPLIED',
    CONSTRAINT `fk_ren_old_lease` FOREIGN KEY (`old_lease_id`) REFERENCES `lease_agreements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 32: Security Deposit Management
CREATE TABLE `security_deposits` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lease_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `deposit_amount` DECIMAL(12,2) NOT NULL,
    `receipt_number` VARCHAR(60) NOT NULL,
    `deposit_date` DATE NOT NULL,
    `refund_status` ENUM('HELD_ACTIVE', 'PARTIALLY_ADJUSTED', 'REFUNDED_FULL', 'FORFEITED_DEFAULT') NOT NULL DEFAULT 'HELD_ACTIVE',
    `adjusted_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `refunded_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `refund_date` DATE NULL,
    CONSTRAINT `fk_dep_lease` FOREIGN KEY (`lease_id`) REFERENCES `lease_agreements` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_dep_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 33: Premium / Advance Management
CREATE TABLE `lease_premiums` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lease_id` INT UNSIGNED NOT NULL,
    `premium_type` ENUM('E_AUCTION_HIGHEST_BID', 'TRANSFER_FEE', 'CAPITALISED_VALUE') NOT NULL,
    `total_premium_due` DECIMAL(14,2) NOT NULL,
    `amount_paid` DECIMAL(14,2) NOT NULL,
    `balance_unpaid` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `payment_receipt_no` VARCHAR(60) NULL,
    CONSTRAINT `fk_prem_lease` FOREIGN KEY (`lease_id`) REFERENCES `lease_agreements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 34: Agreement Document Management
CREATE TABLE `agreement_documents` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lease_id` INT UNSIGNED NOT NULL,
    `document_title` VARCHAR(150) NOT NULL,
    `registered_deed_pdf_url` VARCHAR(255) NOT NULL,
    `sub_registrar_office_name` VARCHAR(100) NULL,
    `registration_number` VARCHAR(80) NULL,
    `registration_date` DATE NULL,
    CONSTRAINT `fk_agr_doc_lease` FOREIGN KEY (`lease_id`) REFERENCES `lease_agreements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 35: Termination / Surrender Management
CREATE TABLE `lease_terminations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lease_id` INT UNSIGNED NOT NULL,
    `termination_type` ENUM('VOLUNTARY_SURRENDER', 'DEFAULT_NON_PAYMENT', 'ILLEGAL_SUBLETTING', 'MUNICIPAL_REDEVELOPMENT') NOT NULL,
    `order_number` VARCHAR(80) NOT NULL,
    `order_date` DATE NOT NULL,
    `effective_termination_date` DATE NOT NULL,
    `total_outstanding_at_termination` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `deposit_forfeited` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `physical_possession_regained_date` DATE NULL,
    CONSTRAINT `fk_term_lease` FOREIGN KEY (`lease_id`) REFERENCES `lease_agreements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 5. E. REVENUE & BILLING (Modules 36 - 48)
-- ------------------------------------------------------------------------------

-- Mod 36: Rent Rate Master
CREATE TABLE `rent_rates` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `ward_id` INT UNSIGNED NULL,
    `property_category` VARCHAR(50) NOT NULL,
    `rate_per_sqft_monthly` DECIMAL(8,2) NOT NULL,
    `effective_from` DATE NOT NULL,
    `effective_to` DATE NULL,
    CONSTRAINT `fk_rr_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 37: Revenue Head Master
CREATE TABLE `revenue_heads` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `code` VARCHAR(30) NOT NULL UNIQUE,
    `name_en` VARCHAR(100) NOT NULL,
    `name_mr` VARCHAR(100) NOT NULL,
    `budget_code` VARCHAR(50) NOT NULL,
    `is_taxable_gst` TINYINT(1) NOT NULL DEFAULT 0,
    `gst_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 38: Demand Generation Batch Log
CREATE TABLE `demand_batches` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `financial_year_id` INT UNSIGNED NOT NULL,
    `billing_month` TINYINT NOT NULL,
    `billing_year` SMALLINT NOT NULL,
    `total_bills_generated` INT NOT NULL DEFAULT 0,
    `total_demand_amount` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `generated_by_user_id` INT UNSIGNED NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_db_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 39: Monthly Rent Billing (Core Bill / Demand)
CREATE TABLE `demand_bills` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `demand_batch_id` INT UNSIGNED NULL,
    `unit_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `lease_id` INT UNSIGNED NOT NULL,
    `bill_number` VARCHAR(60) NOT NULL UNIQUE,
    `bill_date` DATE NOT NULL,
    `due_date` DATE NOT NULL,
    `billing_month` TINYINT NOT NULL,
    `billing_year` SMALLINT NOT NULL,
    `base_rent` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `maintenance_fee` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `water_sanitation_cess` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `gst_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    `previous_arrears_principal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `previous_arrears_interest` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `penalty_interest` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total_demand_gross` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total_paid_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `balance_due_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `payment_status` ENUM('UNPAID', 'PARTIALLY_PAID', 'PAID', 'OVERDUE') NOT NULL DEFAULT 'UNPAID',
    `upi_qr_payload` TEXT NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_bill_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_bill_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_bill_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_bill_lease` FOREIGN KEY (`lease_id`) REFERENCES `lease_agreements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 40: Other Charges / Miscellaneous Demand
CREATE TABLE `miscellaneous_demands` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `revenue_head_id` INT UNSIGNED NOT NULL,
    `charge_title` VARCHAR(150) NOT NULL,
    `amount` DECIMAL(10,2) NOT NULL,
    `demand_date` DATE NOT NULL,
    `due_date` DATE NOT NULL,
    `status` ENUM('PENDING', 'PAID', 'WAIVED') NOT NULL DEFAULT 'PENDING',
    CONSTRAINT `fk_misc_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_misc_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 41: Penalty / Interest Calculation
CREATE TABLE `penalty_calculations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `bill_id` INT UNSIGNED NOT NULL,
    `delay_days` INT NOT NULL,
    `monthly_interest_rate_pct` DECIMAL(4,2) NOT NULL DEFAULT 2.00,
    `calculated_interest` DECIMAL(10,2) NOT NULL,
    `applied_date` DATE NOT NULL,
    CONSTRAINT `fk_pen_bill` FOREIGN KEY (`bill_id`) REFERENCES `demand_bills` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 42: Receipt Management
CREATE TABLE `payment_receipts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `bill_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `unit_id` INT UNSIGNED NOT NULL,
    `receipt_number` VARCHAR(60) NOT NULL UNIQUE,
    `receipt_date` DATE NOT NULL,
    `amount_paid` DECIMAL(12,2) NOT NULL,
    `principal_settled` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `interest_settled` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `payment_mode` ENUM('CASH', 'CHEQUE', 'DD', 'POS_CARD', 'UPI_BHIM', 'NEFT_RTGS', 'ONLINE_GATEWAY') NOT NULL DEFAULT 'UPI_BHIM',
    `transaction_ref_no` VARCHAR(100) NULL,
    `bank_name` VARCHAR(100) NULL,
    `cheque_no` VARCHAR(30) NULL,
    `cheque_date` DATE NULL,
    `cheque_status` ENUM('CLEARED', 'BOUNCED', 'PENDING_CLEARING') NOT NULL DEFAULT 'CLEARED',
    `cashier_id` INT UNSIGNED NULL,
    `printed_count` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_rcpt_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_rcpt_bill` FOREIGN KEY (`bill_id`) REFERENCES `demand_bills` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_rcpt_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_rcpt_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 43: Payment Collection
CREATE TABLE `payment_collections` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `receipt_id` INT UNSIGNED NOT NULL,
    `ulb_id` INT UNSIGNED NOT NULL,
    `collection_date` DATE NOT NULL,
    `shift` ENUM('MORNING', 'EVENING', 'ONLINE_AUTO') NOT NULL DEFAULT 'ONLINE_AUTO',
    `amount` DECIMAL(12,2) NOT NULL,
    `bank_deposit_challan_no` VARCHAR(80) NULL,
    `deposited_at` DATE NULL,
    CONSTRAINT `fk_coll_rcpt` FOREIGN KEY (`receipt_id`) REFERENCES `payment_receipts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 44: Online Payment
CREATE TABLE `online_transactions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `bill_id` INT UNSIGNED NOT NULL,
    `gateway_name` ENUM('MAHAONLINE', 'RAZORPAY', 'BILLDESK', 'SBI_EPAY', 'PAYTM') NOT NULL DEFAULT 'RAZORPAY',
    `order_id` VARCHAR(100) NOT NULL UNIQUE,
    `payment_id` VARCHAR(100) NULL,
    `signature` VARCHAR(255) NULL,
    `amount` DECIMAL(12,2) NOT NULL,
    `status` ENUM('INITIATED', 'SUCCESS', 'FAILED') NOT NULL DEFAULT 'INITIATED',
    `webhook_data` JSON NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_online_bill` FOREIGN KEY (`bill_id`) REFERENCES `demand_bills` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 45: Bank / UTR Reconciliation
CREATE TABLE `bank_reconciliations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `statement_date` DATE NOT NULL,
    `bank_name` VARCHAR(100) NOT NULL,
    `bank_scroll_total` DECIMAL(14,2) NOT NULL,
    `system_collection_total` DECIMAL(14,2) NOT NULL,
    `discrepancy_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `reconciled_by` VARCHAR(100) NULL,
    `status` ENUM('MATCHED', 'DISCREPANCY_FOUND', 'RESOLVED') NOT NULL DEFAULT 'MATCHED'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 46: Arrears Management
CREATE TABLE `arrears_ledgers` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `financial_year_id` INT UNSIGNED NOT NULL,
    `opening_principal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `opening_interest` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `current_demand` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `collected_principal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `collected_interest` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `penalty_interest_levied` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `closing_principal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `closing_interest` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total_outstanding` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `aging_bucket` ENUM('CURRENT', 'DAYS_30_60', 'DAYS_61_90', 'DAYS_91_180', 'DAYS_181_365', 'OVER_1_YEAR', 'OVER_3_YEARS') NOT NULL DEFAULT 'CURRENT',
    CONSTRAINT `fk_arr_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_arr_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 47: Recovery / Demand Notice (Statutory MMCA Sec 81 / Council Act Sec 150)
CREATE TABLE `statutory_notices` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `unit_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `bill_id` INT UNSIGNED NULL,
    `notice_number` VARCHAR(60) NOT NULL UNIQUE,
    `notice_type` ENUM('SEC_81_DEMAND_7_DAYS', 'SEC_81_FINAL_DEMAND_15_DAYS', 'DISTRESS_WARRANT_ATTACHMENT', 'SHOP_SEALING_ORDER', 'EVICTION_SUMMARY') NOT NULL,
    `issue_date` DATE NOT NULL,
    `compliance_deadline` DATE NOT NULL,
    `total_dues_demanded` DECIMAL(12,2) NOT NULL,
    `legal_statute_section` VARCHAR(100) NOT NULL DEFAULT 'Section 81, MMCA 1949',
    `notice_pdf_path` VARCHAR(255) NULL,
    `served_mode` ENUM('REGISTERED_AD_POST', 'HAND_DELIVERY_SUMMONS', 'PASTED_ON_SHUTTER', 'DIGITAL_SMS_WHATSAPP') NOT NULL DEFAULT 'HAND_DELIVERY_SUMMONS',
    `served_date` DATE NULL,
    `served_to_person` VARCHAR(100) NULL,
    `status` ENUM('SERVED', 'AMICABLY_SETTLED', 'DEADLINE_EXPIRED', 'SEALED_EXECUTED') NOT NULL DEFAULT 'SERVED',
    `hearing_date` DATE NULL,
    `sealing_executed_date` DATE NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_not_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_not_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_not_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 48: Revenue Accounting & Reports (DCB / म.व.शि.)
CREATE TABLE `dcb_monthly_summaries` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `ward_id` INT UNSIGNED NOT NULL,
    `complex_id` INT UNSIGNED NULL,
    `financial_year_id` INT UNSIGNED NOT NULL,
    `summary_month` TINYINT NOT NULL,
    `summary_year` SMALLINT NOT NULL,
    `opening_arrears` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `current_demand` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `total_demand` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `collection_arrears` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `collection_current` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `total_collection` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `balance_arrears` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `balance_current` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `total_balance` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    `recovery_percentage` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_dcb_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_dcb_ward` FOREIGN KEY (`ward_id`) REFERENCES `wards` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 6. F. MAINTENANCE (Modules 49 - 57)
-- ------------------------------------------------------------------------------

-- Mod 52: Contractor Master
CREATE TABLE `contractors` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `company_name` VARCHAR(150) NOT NULL,
    `contact_person` VARCHAR(100) NOT NULL,
    `mobile` VARCHAR(20) NOT NULL,
    `pwd_class` ENUM('CLASS_I', 'CLASS_II', 'CLASS_III', 'CLASS_IV', 'CLASS_V') NOT NULL DEFAULT 'CLASS_IV',
    `pan_no` VARCHAR(20) NULL,
    `gst_no` VARCHAR(30) NULL,
    `active` TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 49: Citizen / Tenant Complaints
CREATE TABLE `maintenance_tickets` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `complex_id` INT UNSIGNED NOT NULL,
    `building_id` INT UNSIGNED NULL,
    `unit_id` INT UNSIGNED NULL,
    `ticket_number` VARCHAR(50) NOT NULL UNIQUE,
    `complainant_name` VARCHAR(100) NOT NULL,
    `complainant_mobile` VARCHAR(20) NOT NULL,
    `issue_category` ENUM('DRAINAGE_LEAKAGE', 'WATER_SUPPLY', 'ELECTRICAL_COMMON', 'SHUTTER_DAMAGE', 'STRUCTURAL_CRACK', 'GARBAGE_CLEANLINESS', 'ROOF_WATERPROOFING') NOT NULL,
    `description` TEXT NOT NULL,
    `priority` ENUM('LOW', 'MEDIUM', 'HIGH', 'EMERGENCY') NOT NULL DEFAULT 'MEDIUM',
    `photo_url` VARCHAR(255) NULL,
    `status` ENUM('LOGGED', 'INSPECTION_ASSIGNED', 'ESTIMATE_APPROVED', 'WORK_IN_PROGRESS', 'RESOLVED', 'CLOSED') NOT NULL DEFAULT 'LOGGED',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `resolved_at` TIMESTAMP NULL,
    CONSTRAINT `fk_maint_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_maint_complex` FOREIGN KEY (`complex_id`) REFERENCES `complexes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 50: Maintenance Work Orders
CREATE TABLE `maintenance_work_orders` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ticket_id` INT UNSIGNED NOT NULL,
    `work_order_no` VARCHAR(60) NOT NULL UNIQUE,
    `contractor_id` INT UNSIGNED NOT NULL,
    `junior_engineer_name` VARCHAR(100) NOT NULL,
    `sanctioned_amount` DECIMAL(12,2) NOT NULL,
    `work_start_date` DATE NOT NULL,
    `target_completion_date` DATE NOT NULL,
    `actual_completion_date` DATE NULL,
    `status` ENUM('ISSUED', 'IN_EXECUTION', 'COMPLETED', 'BILL_PAID') NOT NULL DEFAULT 'ISSUED',
    CONSTRAINT `fk_wo_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `maintenance_tickets` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_wo_contractor` FOREIGN KEY (`contractor_id`) REFERENCES `contractors` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 53: Estimate / Approval
CREATE TABLE `estimates_approvals` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ticket_id` INT UNSIGNED NOT NULL,
    `dsr_year` VARCHAR(20) NOT NULL DEFAULT '2025-26',
    `estimate_amount` DECIMAL(12,2) NOT NULL,
    `technical_sanction_no` VARCHAR(80) NULL,
    `administrative_approval_no` VARCHAR(80) NULL,
    CONSTRAINT `fk_est_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `maintenance_tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 54: Work Execution
CREATE TABLE `work_executions` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `work_order_id` INT UNSIGNED NOT NULL,
    `log_date` DATE NOT NULL,
    `progress_percentage` INT NOT NULL DEFAULT 0,
    `activity_description` TEXT NOT NULL,
    `before_photo_url` VARCHAR(255) NULL,
    `after_photo_url` VARCHAR(255) NULL,
    CONSTRAINT `fk_we_wo` FOREIGN KEY (`work_order_id`) REFERENCES `maintenance_work_orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 55: Material / Expense Tracking
CREATE TABLE `material_expenses` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `work_order_id` INT UNSIGNED NOT NULL,
    `item_name` VARCHAR(150) NOT NULL,
    `quantity` DECIMAL(8,2) NOT NULL,
    `unit` VARCHAR(30) NOT NULL,
    `rate` DECIMAL(10,2) NOT NULL,
    `total_amount` DECIMAL(10,2) NOT NULL,
    `voucher_no` VARCHAR(60) NULL,
    CONSTRAINT `fk_me_wo` FOREIGN KEY (`work_order_id`) REFERENCES `maintenance_work_orders` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 57: Maintenance SLA Dashboard
CREATE TABLE `maintenance_slas` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ticket_id` INT UNSIGNED NOT NULL,
    `standard_sla_hours` INT NOT NULL DEFAULT 72,
    `actual_turnaround_hours` INT NULL,
    `is_breached` TINYINT(1) NOT NULL DEFAULT 0,
    CONSTRAINT `fk_sla_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `maintenance_tickets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 7. G. CITIZEN SERVICES (Modules 58 - 65)
-- ------------------------------------------------------------------------------

-- Mod 59: Hall / Community Centre Booking
CREATE TABLE `hall_bookings` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `unit_id` INT UNSIGNED NOT NULL,
    `booking_reference_no` VARCHAR(60) NOT NULL UNIQUE,
    `applicant_name` VARCHAR(150) NOT NULL,
    `applicant_mobile` VARCHAR(20) NOT NULL,
    `applicant_aadhaar` VARCHAR(20) NULL,
    `purpose` ENUM('WEDDING', 'RECEPTION', 'CULTURAL_EVENT', 'PUBLIC_MEETING', 'EXHIBITION', 'BIRTHDAY') NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `slot` ENUM('FULL_DAY', 'DAY_SLOT', 'NIGHT_SLOT') NOT NULL DEFAULT 'FULL_DAY',
    `rent_amount` DECIMAL(10,2) NOT NULL,
    `cleaning_charges` DECIMAL(8,2) NOT NULL DEFAULT 1000.00,
    `security_deposit` DECIMAL(10,2) NOT NULL DEFAULT 5000.00,
    `total_payable` DECIMAL(10,2) NOT NULL,
    `payment_status` ENUM('CONFIRMED', 'CANCELLED', 'REFUNDED') NOT NULL DEFAULT 'CONFIRMED',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_hall_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_hall_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 63: Application Tracking (RTS Act)
CREATE TABLE `service_applications` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `application_no` VARCHAR(60) NOT NULL UNIQUE,
    `applicant_name` VARCHAR(150) NOT NULL,
    `applicant_mobile` VARCHAR(20) NOT NULL,
    `service_type` ENUM('TENANCY_MUTATION', 'RENT_NOC', 'BUSINESS_USE_CHANGE', 'LEASE_RENEWAL', 'DEPOSIT_REFUND') NOT NULL,
    `unit_id` INT UNSIGNED NULL,
    `submission_date` DATE NOT NULL,
    `sla_deadline_date` DATE NOT NULL,
    `current_status` ENUM('SUBMITTED', 'UNDER_SCRUTINY', 'FIELD_INSPECTION', 'APPROVED', 'REJECTED') NOT NULL DEFAULT 'SUBMITTED',
    `rejection_reason` TEXT NULL,
    `sanction_certificate_url` VARCHAR(255) NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_app_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 65: Citizen Notifications
CREATE TABLE `citizen_notifications` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `mobile` VARCHAR(20) NOT NULL,
    `notification_type` ENUM('BILL_SMS', 'RECEIPT_WHATSAPP', 'NOTICE_ALERT', 'MUTATION_UPDATE') NOT NULL,
    `message_content` TEXT NOT NULL,
    `status` ENUM('QUEUED', 'SENT', 'FAILED') NOT NULL DEFAULT 'SENT',
    `sent_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ------------------------------------------------------------------------------
-- 8. H. INTELLIGENCE & MONITORING (Modules 66 - 75)
-- ------------------------------------------------------------------------------

-- Mod 68 & 74: Defaulter Risk Scores & AI Analytics
CREATE TABLE `defaulter_risk_scores` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL,
    `tenant_id` INT UNSIGNED NOT NULL,
    `risk_score` INT NOT NULL DEFAULT 10, -- 1 to 100 (High score = high default risk)
    `risk_tier` ENUM('LOW_RISK', 'MEDIUM_RISK', 'HIGH_RISK', 'CHRONIC_DEFAULTER') NOT NULL DEFAULT 'LOW_RISK',
    `average_delay_days` INT NOT NULL DEFAULT 0,
    `consecutive_missed_months` INT NOT NULL DEFAULT 0,
    `total_arrears_balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `ai_recommendation` VARCHAR(255) NULL,
    `evaluated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_risk_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_risk_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 74: AI Analytics / Anomaly Alerts
CREATE TABLE `ai_anomalies_alerts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `unit_id` INT UNSIGNED NOT NULL,
    `anomaly_type` ENUM('UNAUTHORIZED_SUBLETTING_RISK', 'RENT_BELOW_RECKONER_LEAKAGE', 'PROLONGED_NON_OCCUPANCY', 'ABNORMAL_WATER_CONSUMPTION') NOT NULL,
    `description` TEXT NOT NULL,
    `confidence_pct` DECIMAL(5,2) NOT NULL DEFAULT 85.00,
    `detected_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `is_resolved` TINYINT(1) NOT NULL DEFAULT 0,
    CONSTRAINT `fk_anom_unit` FOREIGN KEY (`unit_id`) REFERENCES `units_galas` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Mod 75: Audit & Compliance Dashboard (CAG / Local Fund Audit Trail)
CREATE TABLE `audit_logs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NULL,
    `action` VARCHAR(50) NOT NULL,
    `entity_name` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED NOT NULL,
    `ip_address` VARCHAR(45) NULL,
    `old_values` JSON NULL,
    `new_values` JSON NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ==============================================================================
-- END OF SCHEMA
-- ==============================================================================