-- ==============================================================================
-- MAHARASHTRA MUNICIPAL PROPERTY ERP - HIERARCHY, COMPLEX, UNIT, TENANT & LEASE UPGRADE
-- Fully Aligned with:
-- 1. Maharashtra Municipal Corporation Act, 1949
-- 2. Maharashtra Municipal Councils and Nagar Panchayats (Lease of Immovable Property) Rules, 2025
-- ==============================================================================

SET FOREIGN_KEY_CHECKS = 0;

-- 1. ULB LEASE RULES CONFIG TABLE (Multi-Act Rules Engine)
CREATE TABLE IF NOT EXISTS `ulb_lease_rules_configs` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `ulb_id` INT UNSIGNED NOT NULL,
    `regulatory_framework` ENUM('MMC_ACT_1949', 'COUNCILS_NAGAR_PANCHAYAT_RULES_2025') NOT NULL DEFAULT 'MMC_ACT_1949',
    `max_lease_tenure_months` INT NOT NULL DEFAULT 36,
    `dma_approval_required_above_months` INT NOT NULL DEFAULT 36,
    `default_security_deposit_months` INT NOT NULL DEFAULT 6,
    `default_escalation_type` ENUM('PERCENTAGE_ANNUAL', 'PERCENTAGE_TRIENNIAL', 'ASR_READY_RECKONER_LINKED', 'FIXED_SLAB') NOT NULL DEFAULT 'PERCENTAGE_TRIENNIAL',
    `default_escalation_value` DECIMAL(5,2) NOT NULL DEFAULT 10.00,
    `late_payment_penalty_monthly_pct` DECIMAL(4,2) NOT NULL DEFAULT 2.00,
    `reservation_quota_divyang_pct` DECIMAL(4,2) NOT NULL DEFAULT 5.00,
    `reservation_quota_shg_women_pct` DECIMAL(4,2) NOT NULL DEFAULT 10.00,
    `reservation_quota_sc_st_pct` DECIMAL(4,2) NOT NULL DEFAULT 10.00,
    `tender_minimum_publish_days` INT NOT NULL DEFAULT 15,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT `fk_rules_ulb` FOREIGN KEY (`ulb_id`) REFERENCES `ulb_bodies` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. COMPLEX MASTER (Aligning with all required fields)
DROP TABLE IF EXISTS `complex_documents`;
CREATE TABLE IF NOT EXISTS `complex_documents` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `complex_id` INT UNSIGNED NOT NULL,
    `document_category` ENUM(
        'SANCTIONED_PLAN',
        'OWNERSHIP_DOCUMENT',
        'COMPLETION_CERTIFICATE',
        'FIRE_DOCUMENTS',
        'PHOTOGRAPHS',
        'PROPERTY_CARD',
        'MEASUREMENT_SHEET',
        'VALUATION_REPORT'
    ) NOT NULL,
    `document_title` VARCHAR(150) NOT NULL,
    `document_number` VARCHAR(100) NULL,
    `issuing_authority` VARCHAR(150) NULL,
    `issue_date` DATE NULL,
    `file_path` VARCHAR(255) NOT NULL,
    `verified` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_cdoc_complex` FOREIGN KEY (`complex_id`) REFERENCES `complexes` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Alter complexes table to ensure every specified field exists
ALTER TABLE `complexes`
    ADD COLUMN IF NOT EXISTS `complex_name` VARCHAR(150) NULL AFTER `name_en`,
    ADD COLUMN IF NOT EXISTS `complex_name_marathi` VARCHAR(150) NULL AFTER `name_mr`,
    ADD COLUMN IF NOT EXISTS `property_id` INT UNSIGNED NULL AFTER `land_property_id`,
    ADD COLUMN IF NOT EXISTS `zone_id` INT UNSIGNED NULL AFTER `ward_id`,
    ADD COLUMN IF NOT EXISTS `address` TEXT NULL AFTER `zone_id`,
    ADD COLUMN IF NOT EXISTS `latitude` DECIMAL(10,8) NULL AFTER `gis_latitude`,
    ADD COLUMN IF NOT EXISTS `longitude` DECIMAL(11,8) NULL AFTER `gis_longitude`,
    ADD COLUMN IF NOT EXISTS `survey_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `plot_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `built_up_area` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `carpet_area` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `number_of_buildings` INT NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS `number_of_floors` INT NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS `ownership_type` ENUM('MUNICIPAL_OWNED', 'GOVT_LEASED', 'ACQUIRED_RESERVATION', 'DONATED') NOT NULL DEFAULT 'MUNICIPAL_OWNED',
    ADD COLUMN IF NOT EXISTS `property_status` ENUM('OPERATIONAL', 'UNDER_CONSTRUCTION', 'UNDER_MAINTENANCE', 'REDEVELOPMENT') NOT NULL DEFAULT 'OPERATIONAL',
    ADD COLUMN IF NOT EXISTS `estimated_property_value` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `book_value` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `construction_cost` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `property_tax_assessment_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `electricity_connection_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `water_connection_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `fire_noc_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `remarks` TEXT NULL,
    ADD COLUMN IF NOT EXISTS `status` ENUM('ACTIVE', 'INACTIVE', 'SEALED', 'LITIGATION') NOT NULL DEFAULT 'ACTIVE';

-- 3. UNIT / SHOP MASTER (Permanent Unit Code & exact 9 statuses)
ALTER TABLE `units_galas`
    ADD COLUMN IF NOT EXISTS `shop_no` VARCHAR(30) NULL AFTER `unit_number`,
    ADD COLUMN IF NOT EXISTS `unit_type` ENUM('SHOP', 'OFFICE', 'STALL', 'HALL', 'ATM', 'PARKING', 'OPEN_AREA', 'GODOWN', 'RESTAURANT', 'OTHER') NOT NULL DEFAULT 'SHOP' AFTER `category`,
    ADD COLUMN IF NOT EXISTS `usage_type` ENUM('COMMERCIAL_RETAIL', 'WHOLESALE', 'COMMERCIAL_OFFICE', 'BANKING_FINANCE', 'HOSPITALITY_FOOD', 'COMMUNITY_SOCIAL', 'STORAGE_WAREHOUSE') NOT NULL DEFAULT 'COMMERCIAL_RETAIL',
    ADD COLUMN IF NOT EXISTS `builtup_area` DECIMAL(10,2) NOT NULL DEFAULT 0.00 AFTER `carpet_area_sqft`,
    ADD COLUMN IF NOT EXISTS `frontage` VARCHAR(40) NULL AFTER `shutter_size`,
    ADD COLUMN IF NOT EXISTS `floor_position` ENUM('FRONT_FACING', 'CORRIDOR_INSIDE', 'CORNER_PRIME', 'REAR_BACK') NOT NULL DEFAULT 'FRONT_FACING',
    ADD COLUMN IF NOT EXISTS `electricity_meter_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `water_meter_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `current_status` ENUM('VACANT', 'OCCUPIED', 'UNDER_RENOVATION', 'UNDER_DISPUTE', 'SEALED', 'UNAUTHORIZED', 'RESERVED', 'ALLOTTED', 'TERMINATED') NOT NULL DEFAULT 'VACANT',
    ADD COLUMN IF NOT EXISTS `occupancy_status` ENUM('FREE_FOR_ALLOTMENT', 'CURRENTLY_LEASED', 'SUBLET_UNAUTHORIZED', 'LEGAL_SUIT_STAY', 'RESERVED_QUOTA') NOT NULL DEFAULT 'FREE_FOR_ALLOTMENT',
    ADD COLUMN IF NOT EXISTS `reservation_category` ENUM('GENERAL_OPEN', 'DIVYANG', 'WOMEN_SHG', 'SC_ST', 'OBC', 'EX_SERVICEMEN') NOT NULL DEFAULT 'GENERAL_OPEN',
    ADD COLUMN IF NOT EXISTS `market_rate` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `base_rent` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `security_deposit` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `premium_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `opening_date` DATE NULL,
    ADD COLUMN IF NOT EXISTS `closing_date` DATE NULL,
    ADD COLUMN IF NOT EXISTS `qr_code` VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS `latitude` DECIMAL(10,8) NULL,
    ADD COLUMN IF NOT EXISTS `longitude` DECIMAL(11,8) NULL,
    ADD COLUMN IF NOT EXISTS `remarks` TEXT NULL;

-- Modify existing status enum in units_galas to match the exact 9 statuses
ALTER TABLE `units_galas` 
    MODIFY COLUMN `status` ENUM('VACANT', 'OCCUPIED', 'UNDER_RENOVATION', 'UNDER_DISPUTE', 'SEALED', 'UNAUTHORIZED', 'RESERVED', 'ALLOTTED', 'TERMINATED') NOT NULL DEFAULT 'VACANT';

-- 4. NORMALIZED TENANT MASTER
ALTER TABLE `tenants`
    ADD COLUMN IF NOT EXISTS `tenant_no` VARCHAR(50) NULL AFTER `tenant_code`,
    ADD COLUMN IF NOT EXISTS `tenant_type` ENUM('individual', 'company', 'partnership', 'trust', 'shg_bachat_gat') NOT NULL DEFAULT 'individual' AFTER `tenant_no`,
    ADD COLUMN IF NOT EXISTS `name_marathi` VARCHAR(150) NULL AFTER `full_name_mr`,
    ADD COLUMN IF NOT EXISTS `father_name` VARCHAR(150) NULL AFTER `name_marathi`,
    ADD COLUMN IF NOT EXISTS `dob` DATE NULL,
    ADD COLUMN IF NOT EXISTS `aadhaar_last4` VARCHAR(4) NULL AFTER `aadhaar_no_hash`,
    ADD COLUMN IF NOT EXISTS `business_name` VARCHAR(150) NULL,
    ADD COLUMN IF NOT EXISTS `business_type` VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS `address` TEXT NULL,
    ADD COLUMN IF NOT EXISTS `city` VARCHAR(80) NOT NULL DEFAULT 'Chhatrapati Sambhajinagar',
    ADD COLUMN IF NOT EXISTS `district` VARCHAR(80) NOT NULL DEFAULT 'Chhatrapati Sambhajinagar',
    ADD COLUMN IF NOT EXISTS `state` VARCHAR(80) NOT NULL DEFAULT 'Maharashtra',
    ADD COLUMN IF NOT EXISTS `pincode` VARCHAR(10) NOT NULL DEFAULT '431001',
    ADD COLUMN IF NOT EXISTS `status` ENUM('ACTIVE', 'SUSPENDED', 'DEFAULTED', 'BLACKLISTED') NOT NULL DEFAULT 'ACTIVE';

-- 4.1 Tenant Addresses
DROP TABLE IF EXISTS `tenant_addresses`;
CREATE TABLE `tenant_addresses` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT UNSIGNED NOT NULL,
    `address_type` ENUM('RESIDENTIAL', 'BUSINESS_REGISTERED', 'COMMUNICATION', 'GODOWN') NOT NULL DEFAULT 'RESIDENTIAL',
    `address_line1` VARCHAR(255) NOT NULL,
    `address_line2` VARCHAR(255) NULL,
    `city` VARCHAR(80) NOT NULL,
    `district` VARCHAR(80) NOT NULL,
    `state` VARCHAR(80) NOT NULL DEFAULT 'Maharashtra',
    `pincode` VARCHAR(10) NOT NULL,
    `is_primary` TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT `fk_taddr_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.2 Tenant Contacts
DROP TABLE IF EXISTS `tenant_contacts`;
CREATE TABLE `tenant_contacts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT UNSIGNED NOT NULL,
    `contact_person_name` VARCHAR(150) NOT NULL,
    `designation` VARCHAR(100) NOT NULL,
    `mobile` VARCHAR(20) NOT NULL,
    `alternate_phone` VARCHAR(20) NULL,
    `email` VARCHAR(100) NULL,
    `is_primary` TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT `fk_tcont_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.3 Tenant Businesses
DROP TABLE IF EXISTS `tenant_businesses`;
CREATE TABLE `tenant_businesses` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT UNSIGNED NOT NULL,
    `business_name` VARCHAR(150) NOT NULL,
    `trade_license_no` VARCHAR(80) NULL,
    `business_category` VARCHAR(100) NOT NULL,
    `nature_of_goods_services` TEXT NULL,
    `annual_turnover_declared` DECIMAL(14,2) NULL,
    CONSTRAINT `fk_tbiz_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.4 Tenant Bank Accounts
DROP TABLE IF EXISTS `tenant_bank_accounts`;
CREATE TABLE `tenant_bank_accounts` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT UNSIGNED NOT NULL,
    `bank_name` VARCHAR(100) NOT NULL,
    `branch_name` VARCHAR(100) NOT NULL,
    `account_no_masked` VARCHAR(30) NOT NULL,
    `ifsc_code` VARCHAR(20) NOT NULL,
    `account_type` ENUM('CURRENT', 'SAVINGS', 'OVERDRAFT') NOT NULL DEFAULT 'CURRENT',
    `is_verified` TINYINT(1) NOT NULL DEFAULT 1,
    CONSTRAINT `fk_tbank_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4.5 Tenant Authorized Persons
DROP TABLE IF EXISTS `tenant_authorized_persons`;
CREATE TABLE `tenant_authorized_persons` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `tenant_id` INT UNSIGNED NOT NULL,
    `person_name` VARCHAR(150) NOT NULL,
    `designation` VARCHAR(100) NOT NULL,
    `pan_no` VARCHAR(20) NULL,
    `mobile` VARCHAR(20) NOT NULL,
    `email` VARCHAR(100) NULL,
    `authorization_letter_ref` VARCHAR(100) NULL,
    CONSTRAINT `fk_tauth_tenant` FOREIGN KEY (`tenant_id`) REFERENCES `tenants` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. LEASE MANAGEMENT (11-Stage Workflow & All Specific Fields)
ALTER TABLE `lease_agreements`
    ADD COLUMN IF NOT EXISTS `lease_no` VARCHAR(60) NULL AFTER `agreement_number`,
    ADD COLUMN IF NOT EXISTS `lease_type` ENUM('COMMERCIAL_SHOP', 'OFFICE_PREMISES', 'COMMUNITY_HALL', 'PARKING_SPACE', 'HAWKER_STALL', 'LONG_TERM_LAND_PLOT') NOT NULL DEFAULT 'COMMERCIAL_SHOP',
    ADD COLUMN IF NOT EXISTS `allotment_method` ENUM('PUBLIC_E_AUCTION', 'TENDER_CUM_AUCTION', 'RESERVATION_QUOTA', 'COMPASSIONATE_HEIR', 'TRANSFER_MUTATION') NOT NULL DEFAULT 'PUBLIC_E_AUCTION',
    ADD COLUMN IF NOT EXISTS `application_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `approval_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `agreement_no` VARCHAR(60) NULL,
    ADD COLUMN IF NOT EXISTS `rent_frequency` ENUM('MONTHLY', 'QUARTERLY', 'ANNUAL') NOT NULL DEFAULT 'MONTHLY',
    ADD COLUMN IF NOT EXISTS `annual_rent` DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `rent_escalation_type` ENUM('PERCENTAGE_ANNUAL', 'PERCENTAGE_TRIENNIAL', 'READY_RECKONER_LINKED', 'FIXED_STEP') NOT NULL DEFAULT 'PERCENTAGE_TRIENNIAL',
    ADD COLUMN IF NOT EXISTS `rent_escalation_value` DECIMAL(5,2) NOT NULL DEFAULT 10.00,
    ADD COLUMN IF NOT EXISTS `security_deposit` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `premium_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `advance_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    ADD COLUMN IF NOT EXISTS `lock_in_period_months` INT NOT NULL DEFAULT 11,
    ADD COLUMN IF NOT EXISTS `notice_period_days` INT NOT NULL DEFAULT 60,
    ADD COLUMN IF NOT EXISTS `renewal_allowed` TINYINT(1) NOT NULL DEFAULT 1,
    ADD COLUMN IF NOT EXISTS `approved_by` VARCHAR(100) NULL,
    ADD COLUMN IF NOT EXISTS `approved_at` TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS `terminated_at` TIMESTAMP NULL,
    ADD COLUMN IF NOT EXISTS `termination_reason` TEXT NULL;

-- Modify lease status to support the full 11-Stage Lifecycle State Machine
ALTER TABLE `lease_agreements`
    MODIFY COLUMN `status` ENUM(
        'DRAFT',
        'SUBMITTED',
        'DOCUMENT_VERIFICATION',
        'ESTATE_OFFICER_APPROVAL',
        'FINANCIAL_APPROVAL',
        'LEGAL_VERIFICATION',
        'COMPETENT_AUTHORITY_APPROVAL',
        'AGREEMENT_GENERATED',
        'EXECUTED',
        'ACTIVE',
        'RENEWAL_DUE',
        'RENEWED',
        'TERMINATED',
        'EXPIRED'
    ) NOT NULL DEFAULT 'ACTIVE';

-- 5.1 Lease Workflow Approval History Log
DROP TABLE IF EXISTS `lease_approval_history`;
CREATE TABLE `lease_approval_history` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `lease_id` INT UNSIGNED NOT NULL,
    `stage` VARCHAR(60) NOT NULL,
    `action` ENUM('SUBMITTED', 'FORWARDED', 'APPROVED', 'REJECTED', 'QUERY_RAISED') NOT NULL,
    `action_by_role` VARCHAR(60) NOT NULL,
    `action_by_name` VARCHAR(100) NOT NULL,
    `comments` TEXT NULL,
    `action_timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT `fk_lah_lease` FOREIGN KEY (`lease_id`) REFERENCES `lease_agreements` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;