-- ==============================================================================
-- MAHARASHTRA MUNICIPAL PROPERTY ERP - ENRICHED SEEDS
-- Complex Documents, Permanent UPID, Normalized Tenants, 11-Stage Leases & Multi-Act Rules
-- ==============================================================================

-- 1. ULB LEASE RULES CONFIGS (Multi-Act Rules Engine)
DELETE FROM `ulb_lease_rules_configs`;
INSERT INTO `ulb_lease_rules_configs` (
    `id`, `ulb_id`, `regulatory_framework`, `max_lease_tenure_months`, 
    `dma_approval_required_above_months`, `default_security_deposit_months`, 
    `default_escalation_type`, `default_escalation_value`, `late_payment_penalty_monthly_pct`, 
    `reservation_quota_divyang_pct`, `reservation_quota_shg_women_pct`, `reservation_quota_sc_st_pct`, 
    `tender_minimum_publish_days`
) VALUES 
(1, 1, 'MMC_ACT_1949', 120, 36, 6, 'PERCENTAGE_TRIENNIAL', 10.00, 2.00, 5.00, 10.00, 10.00, 15),
(2, 2, 'COUNCILS_NAGAR_PANCHAYAT_RULES_2025', 36, 36, 6, 'PERCENTAGE_ANNUAL', 10.00, 2.00, 5.00, 10.00, 10.00, 21);

-- 2. ENRICH COMPLEXES WITH ALL SPECIFIED FIELDS
UPDATE `complexes` SET
    `complex_name` = 'Chhatrapati Shivaji Maharaj Commercial Complex',
    `complex_name_marathi` = 'छत्रपती शिवाजी महाराज व्यापारी संकुल',
    `property_id` = 1,
    `zone_id` = 1,
    `address` = 'Gulmandi Main Road, City Survey No 1204, Ward 01, Chhatrapati Sambhajinagar',
    `latitude` = 19.88210000,
    `longitude` = 75.33400000,
    `survey_no` = 'S.No. 45/2',
    `plot_no` = 'Plot No. A-1',
    `built_up_area` = 4250.00,
    `carpet_area` = 3450.00,
    `number_of_buildings` = 1,
    `number_of_floors` = 3,
    `ownership_type` = 'MUNICIPAL_OWNED',
    `property_status` = 'OPERATIONAL',
    `estimated_property_value` = 145000000.00,
    `book_value` = 82000000.00,
    `construction_cost` = 38500000.00,
    `property_tax_assessment_no` = 'PT-CSMC-W01-1204',
    `electricity_connection_no` = 'MSEDCL-HT-889100',
    `water_connection_no` = 'CSMC-W-COMM-014',
    `fire_noc_no` = 'FIRE-NOC/2024/771',
    `remarks` = 'Prime central city commercial complex with 48 retail shops and offices',
    `status` = 'ACTIVE'
WHERE `id` = 1;

UPDATE `complexes` SET
    `complex_name` = 'Dr. Babasaheb Ambedkar Commercial Plaza',
    `complex_name_marathi` = 'डॉ. बाबासाहेब आंबेडकर व्यापारी संकुल',
    `property_id` = 2,
    `zone_id` = 1,
    `address` = 'Aurangpura Circle, Near College Road, Ward 02, Chhatrapati Sambhajinagar',
    `latitude` = 19.87850000,
    `longitude` = 75.32680000,
    `survey_no` = 'S.No. 89/1',
    `plot_no` = 'Plot No. 12',
    `built_up_area` = 3500.00,
    `carpet_area` = 2850.00,
    `number_of_buildings` = 1,
    `number_of_floors` = 4,
    `ownership_type` = 'MUNICIPAL_OWNED',
    `property_status` = 'OPERATIONAL',
    `estimated_property_value` = 185000000.00,
    `book_value` = 98000000.00,
    `construction_cost` = 44000000.00,
    `property_tax_assessment_no` = 'PT-CSMC-W02-3410',
    `electricity_connection_no` = 'MSEDCL-HT-771200',
    `water_connection_no` = 'CSMC-W-COMM-022',
    `fire_noc_no` = 'FIRE-NOC/2023/502',
    `remarks` = 'High footfall multi-floor shopping plaza with bank branches and consulting offices',
    `status` = 'ACTIVE'
WHERE `id` = 2;

UPDATE `complexes` SET
    `complex_name` = 'Connaught Place Shopping Arcade & Community Hall',
    `complex_name_marathi` = 'कॅनॉट प्लेस व्यापारी संकुल व समाज मंदिर',
    `property_id` = 3,
    `zone_id` = 2,
    `address` = 'Cannaught Garden Road, Cidco N-5, Ward 07, Chhatrapati Sambhajinagar',
    `latitude` = 19.87120000,
    `longitude` = 75.36210000,
    `survey_no` = 'S.No. 112, Gat-44',
    `plot_no` = 'Plot No. C-1',
    `built_up_area` = 6800.00,
    `carpet_area` = 5500.00,
    `number_of_buildings` = 2,
    `number_of_floors` = 2,
    `ownership_type` = 'MUNICIPAL_OWNED',
    `property_status` = 'OPERATIONAL',
    `estimated_property_value` = 240000000.00,
    `book_value` = 135000000.00,
    `construction_cost` = 62000000.00,
    `property_tax_assessment_no` = 'PT-CSMC-W07-8900',
    `electricity_connection_no` = 'MSEDCL-HT-990010',
    `water_connection_no` = 'CSMC-W-COMM-089',
    `fire_noc_no` = 'FIRE-NOC/2025/119',
    `remarks` = 'Integrated retail arcade with 800-capacity public community hall and exhibition lawn',
    `status` = 'ACTIVE'
WHERE `id` = 3;

-- 2.1 COMPLEX DOCUMENTS VAULT
DELETE FROM `complex_documents`;
INSERT INTO `complex_documents` (`complex_id`, `document_category`, `document_title`, `document_number`, `issuing_authority`, `issue_date`, `file_path`, `verified`) VALUES
(1, 'SANCTIONED_PLAN', 'Town Planning Sanctioned Layout & Elevation Plan', 'TP/CSMC/PLN-2011/440', 'Town Planning Dept, CSMC', '2011-06-14', 'docs/complex1_sanction_plan.pdf', 1),
(1, 'OWNERSHIP_DOCUMENT', 'Municipal Gazette Notification of Property Title', 'MAHA-GAZ-2010/890', 'Urban Development Dept, Govt of Maharashtra', '2010-03-22', 'docs/complex1_ownership_gazette.pdf', 1),
(1, 'COMPLETION_CERTIFICATE', 'City Engineer Building Completion Certificate', 'CC/2012/891', 'City Engineer, CSMC', '2012-08-10', 'docs/complex1_completion_cert.pdf', 1),
(1, 'FIRE_DOCUMENTS', 'Fire Safety Final NOC & Hydrant Inspection', 'FIRE/NOC/2024/771', 'Chief Fire Officer, CSMC', '2024-01-15', 'docs/complex1_fire_noc.pdf', 1),
(1, 'PHOTOGRAPHS', 'Geo-tagged Front Elevation & Shutter Corridors', 'PHOTO-CMPLX1-2026', 'Estate Department Drone Survey', '2026-01-10', 'docs/complex1_drone_photo.jpg', 1),
(1, 'PROPERTY_CARD', 'City Survey Office Property Card (मालमत्ता पत्रक)', 'CTS-1204/CSMC', 'Superintendent of Land Records', '2018-09-05', 'docs/complex1_property_card.pdf', 1),
(1, 'MEASUREMENT_SHEET', 'Architect Carpet & Super Built-up Area Measurement Sheet', 'ARCH/MS/2012/04', 'M/s Kulkarni & Associates (Registered Architects)', '2012-07-28', 'docs/complex1_measurement_sheet.pdf', 1),
(1, 'VALUATION_REPORT', 'Government Approved Valuer Ready Reckoner Assessment', 'VAL-ASR-2026/102', 'Govt Approved Valuer Regd No CAT-I/442', '2026-03-31', 'docs/complex1_valuation_report.pdf', 1),

(2, 'SANCTIONED_PLAN', 'Commercial Complex Building Plan Sanction', 'TP/CSMC/PLN-2015/802', 'Town Planning Dept, CSMC', '2015-09-18', 'docs/complex2_plan.pdf', 1),
(2, 'PROPERTY_CARD', 'City Survey Property Card CTS 3410', 'CTS-3410/AUR', 'City Survey Officer No. 1', '2019-11-12', 'docs/complex2_property_card.pdf', 1),
(2, 'FIRE_DOCUMENTS', 'Annual Fire Audit Compliance Certificate', 'FIRE/AUDIT/2025/304', 'CFO, Municipal Fire Brigade', '2025-04-10', 'docs/complex2_fire_audit.pdf', 1),

(3, 'COMPLETION_CERTIFICATE', 'Multi-Purpose Complex & Hall Completion Certificate', 'CC/2018/104', 'Executive Engineer (PWD), CSMC', '2018-05-02', 'docs/complex3_completion.pdf', 1),
(3, 'VALUATION_REPORT', 'Commercial Capital Valuation & Rent Benchmarking', 'VAL/2025/89', 'District Valuation Committee', '2025-12-15', 'docs/complex3_valuation.pdf', 1);

-- 3. PERMANENT UNIT / SHOP MASTER UPID & ALL 9 STATUSES
-- Format: [ULB_PREFIX]-[WARD_PREFIX]-[COMPLEX_CODE]-[BLDG_CODE]-[FLOOR_CODE]-[UNIT_CODE]
-- e.g. CSMC-W01-C001-B01-F00-S001
UPDATE `units_galas` SET
    `unit_code` = 'CSMC-W01-C001-B01-F00-S001',
    `shop_no` = 'G-001',
    `unit_type` = 'SHOP',
    `usage_type` = 'COMMERCIAL_RETAIL',
    `builtup_area` = 375.00,
    `frontage` = '12 ft glass shutter',
    `floor_position` = 'FRONT_FACING',
    `electricity_meter_no` = 'MSEDCL-889102',
    `water_meter_no` = 'WM-CSMC-101',
    `current_status` = 'OCCUPIED',
    `occupancy_status` = 'CURRENTLY_LEASED',
    `reservation_category` = 'GENERAL_OPEN',
    `market_rate` = 15000.00,
    `base_rent` = 12500.00,
    `security_deposit` = 45000.00,
    `premium_amount` = 100000.00,
    `opening_date` = '2023-04-01',
    `qr_code` = 'QR-CSMC-W01-C001-B01-F00-S001',
    `latitude` = 19.88211000,
    `longitude` = 75.33402000,
    `remarks` = 'Prime retail cloth shop facing main market square',
    `status` = 'OCCUPIED'
WHERE `id` = 1;

UPDATE `units_galas` SET
    `unit_code` = 'CSMC-W01-C001-B01-F00-S002',
    `shop_no` = 'G-002',
    `unit_type` = 'SHOP',
    `usage_type` = 'COMMERCIAL_RETAIL',
    `builtup_area` = 375.00,
    `frontage` = '12 ft shutter',
    `floor_position` = 'FRONT_FACING',
    `electricity_meter_no` = 'MSEDCL-889103',
    `water_meter_no` = 'WM-CSMC-102',
    `current_status` = 'OCCUPIED',
    `occupancy_status` = 'CURRENTLY_LEASED',
    `reservation_category` = 'GENERAL_OPEN',
    `market_rate` = 15000.00,
    `base_rent` = 12500.00,
    `security_deposit` = 45000.00,
    `premium_amount` = 100000.00,
    `opening_date` = '2023-04-01',
    `qr_code` = 'QR-CSMC-W01-C001-B01-F00-S002',
    `latitude` = 19.88212000,
    `longitude` = 75.33403000,
    `remarks` = 'Retail pharmacy & surgical store',
    `status` = 'OCCUPIED'
WHERE `id` = 2;

UPDATE `units_galas` SET
    `unit_code` = 'CSMC-W01-C001-B01-F00-S003',
    `shop_no` = 'G-003',
    `unit_type` = 'SHOP',
    `usage_type` = 'COMMERCIAL_RETAIL',
    `builtup_area` = 270.00,
    `frontage` = '8 ft shutter',
    `floor_position` = 'CORRIDOR_INSIDE',
    `electricity_meter_no` = 'MSEDCL-889104',
    `water_meter_no` = 'WM-CSMC-103',
    `current_status` = 'OCCUPIED',
    `occupancy_status` = 'CURRENTLY_LEASED',
    `reservation_category` = 'GENERAL_OPEN',
    `market_rate` = 11500.00,
    `base_rent` = 9000.00,
    `security_deposit` = 34500.00,
    `premium_amount` = 75000.00,
    `opening_date` = '2022-10-01',
    `qr_code` = 'QR-CSMC-W01-C001-B01-F00-S003',
    `latitude` = 19.88213000,
    `longitude` = 75.33404000,
    `remarks` = 'Electronics and mobile accessories retail',
    `status` = 'OCCUPIED'
WHERE `id` = 3;

UPDATE `units_galas` SET
    `unit_code` = 'CSMC-W01-C001-B01-F00-A004',
    `shop_no` = 'ATM-01',
    `unit_type` = 'ATM',
    `usage_type` = 'BANKING_FINANCE',
    `builtup_area` = 130.00,
    `frontage` = '10 ft glass front',
    `floor_position` = 'FRONT_FACING',
    `electricity_meter_no` = 'MSEDCL-889105',
    `water_meter_no` = NULL,
    `current_status` = 'OCCUPIED',
    `occupancy_status` = 'CURRENTLY_LEASED',
    `reservation_category` = 'GENERAL_OPEN',
    `market_rate` = 20000.00,
    `base_rent` = 15000.00,
    `security_deposit` = 120000.00,
    `premium_amount` = 200000.00,
    `opening_date` = '2021-01-01',
    `qr_code` = 'QR-CSMC-W01-C001-B01-F00-A004',
    `latitude` = 19.88214000,
    `longitude` = 75.33405000,
    `remarks` = 'SBI Automated Teller Machine 24x7 kiosk',
    `status` = 'OCCUPIED'
WHERE `id` = 4;

UPDATE `units_galas` SET
    `unit_code` = 'CSMC-W01-C001-B01-F00-S005',
    `shop_no` = 'G-005',
    `unit_type` = 'SHOP',
    `usage_type` = 'COMMERCIAL_RETAIL',
    `builtup_area` = 375.00,
    `frontage` = '10 ft shutter',
    `floor_position` = 'CORRIDOR_INSIDE',
    `electricity_meter_no` = NULL,
    `water_meter_no` = NULL,
    `current_status` = 'VACANT',
    `occupancy_status` = 'FREE_FOR_ALLOTMENT',
    `reservation_category` = 'GENERAL_OPEN',
    `market_rate` = 14000.00,
    `base_rent` = 12500.00,
    `security_deposit` = 42000.00,
    `premium_amount` = 0.00,
    `opening_date` = NULL,
    `qr_code` = 'QR-CSMC-W01-C001-B01-F00-S005',
    `latitude` = 19.88215000,
    `longitude` = 75.33406000,
    `remarks` = 'Ready for upcoming municipal public e-tender auction',
    `status` = 'VACANT'
WHERE `id` = 5;

UPDATE `units_galas` SET
    `unit_code` = 'CSMC-W01-C001-B01-F00-S006',
    `shop_no` = 'G-006',
    `unit_type` = 'SHOP',
    `usage_type` = 'COMMERCIAL_RETAIL',
    `builtup_area` = 375.00,
    `frontage` = '10 ft shutter',
    `floor_position` = 'CORRIDOR_INSIDE',
    `electricity_meter_no` = 'MSEDCL-889108',
    `water_meter_no` = 'WM-CSMC-106',
    `current_status` = 'SEALED',
    `occupancy_status` = 'LEGAL_SUIT_STAY',
    `reservation_category` = 'OBC',
    `market_rate` = 14000.00,
    `base_rent` = 12500.00,
    `security_deposit` = 42000.00,
    `premium_amount` = 0.00,
    `opening_date` = '2020-04-01',
    `closing_date` = '2026-08-28',
    `qr_code` = 'QR-CSMC-W01-C001-B01-F00-S006',
    `latitude` = 19.88216000,
    `longitude` = 75.33407000,
    `remarks` = 'Sealed under Section 81 & 267 MMCA 1949 for Rs 2.30 Lakh chronic default',
    `status` = 'SEALED'
WHERE `id` = 6;

UPDATE `units_galas` SET
    `unit_code` = 'CSMC-W02-C002-B01-F00-S011',
    `shop_no` = 'G-011',
    `unit_type` = 'SHOP',
    `usage_type` = 'COMMERCIAL_RETAIL',
    `builtup_area` = 490.00,
    `frontage` = '14 ft shutter',
    `floor_position` = 'FRONT_FACING',
    `electricity_meter_no` = 'MSEDCL-771201',
    `water_meter_no` = 'WM-CSMC-201',
    `current_status` = 'OCCUPIED',
    `occupancy_status` = 'RESERVED_QUOTA',
    `reservation_category` = 'WOMEN_SHG',
    `market_rate` = 18500.00,
    `base_rent` = 9250.00,
    `security_deposit` = 27750.00,
    `premium_amount` = 0.00,
    `opening_date` = '2024-04-01',
    `qr_code` = 'QR-CSMC-W02-C002-B01-F00-S011',
    `latitude` = 19.87851000,
    `longitude` = 75.32681000,
    `remarks` = 'Mahila Bachat Gat handicraft and domestic food product display centre',
    `status` = 'OCCUPIED'
WHERE `id` = 7;

UPDATE `units_galas` SET
    `unit_code` = 'CSMC-W07-C003-B02-F00-H001',
    `shop_no` = 'HALL-01',
    `unit_type` = 'HALL',
    `usage_type` = 'COMMUNITY_SOCIAL',
    `builtup_area` = 5800.00,
    `frontage` = '25 ft grand entrance',
    `floor_position` = 'FRONT_FACING',
    `electricity_meter_no` = 'MSEDCL-990011',
    `water_meter_no` = 'WM-CSMC-801',
    `current_status` = 'OCCUPIED',
    `occupancy_status` = 'CURRENTLY_LEASED',
    `reservation_category` = 'GENERAL_OPEN',
    `market_rate` = 50000.00,
    `base_rent` = 35000.00,
    `security_deposit` = 10000.00,
    `premium_amount` = 0.00,
    `opening_date` = '2018-05-10',
    `qr_code` = 'QR-CSMC-W07-C003-B02-F00-H001',
    `latitude` = 19.87122000,
    `longitude` = 75.36212000,
    `remarks` = 'Air-cooled Community Banquet Hall with attached dining lawn and green rooms',
    `status` = 'OCCUPIED'
WHERE `id` = 8;

-- Insert two additional units to showcase other required statuses (RESERVED, UNDER_RENOVATION)
INSERT INTO `units_galas` (
    `id`, `ulb_id`, `ward_id`, `complex_id`, `building_id`, `floor_id`,
    `unit_code`, `unit_number`, `shop_no`, `unit_name`, `category`, `unit_type`, `usage_type`,
    `carpet_area_sqft`, `builtup_area`, `frontage`, `floor_position`,
    `electricity_meter_no`, `water_meter_no`, `current_status`, `occupancy_status`,
    `reservation_category`, `market_rate`, `base_rent`, `security_deposit`, `premium_amount`,
    `qr_code`, `status`
) VALUES
(9, 1, 1, 1, 1, 2, 'CSMC-W01-C001-B01-F01-O101', 'F-101', 'O-101', 'Municipal Legal Aid Clinic', 'OFFICE', 'OFFICE', 'COMMERCIAL_OFFICE', 450.00, 560.00, '12 ft corridor', 'CORRIDOR_INSIDE', 'MSEDCL-889120', 'WM-CSMC-120', 'UNDER_RENOVATION', 'FREE_FOR_ALLOTMENT', 'GENERAL_OPEN', 16000.00, 14000.00, 48000.00, 0.00, 'QR-CSMC-W01-C001-B01-F01-O101', 'UNDER_RENOVATION'),
(10, 1, 2, 2, 2, 4, 'CSMC-W02-C002-B01-F00-S012', 'G-012', 'S-012', 'Divyang Swarojgar Kendra', 'SHOP', 'STALL', 'COMMERCIAL_RETAIL', 180.00, 220.00, '8 ft front', 'FRONT_FACING', 'MSEDCL-771205', NULL, 'RESERVED', 'RESERVED_QUOTA', 'DIVYANG', 7500.00, 5000.00, 15000.00, 0.00, 'QR-CSMC-W02-C002-B01-F00-S012', 'RESERVED')
ON DUPLICATE KEY UPDATE `unit_code` = VALUES(`unit_code`);

-- 4. ENRICH NORMALIZED TENANTS WITH ADDRESSES, CONTACTS, KYC, BUSINESSES & ACCOUNTS
UPDATE `tenants` SET
    `tenant_no` = 'TNT-2023-CSMC-001',
    `tenant_type` = 'individual',
    `name_marathi` = 'विजय माधवराव पाटील',
    `father_name` = 'माधवराव विठ्ठलराव पाटील',
    `dob` = '1978-04-12',
    `aadhaar_last4` = '4819',
    `business_name` = 'श्री गणेश क्लॉथ स्टोअर्स',
    `business_type` = 'कापड व तयार कपडे किरकोळ विक्री (Textiles Retail)',
    `address` = 'Plot No. 14, Samarth Nagar, Opp. Water Tank',
    `city` = 'Chhatrapati Sambhajinagar',
    `district` = 'Chhatrapati Sambhajinagar',
    `state` = 'Maharashtra',
    `pincode` = '431001',
    `status` = 'ACTIVE'
WHERE `id` = 1;

UPDATE `tenants` SET
    `tenant_no` = 'TNT-2023-CSMC-002',
    `tenant_type` = 'individual',
    `name_marathi` = 'डॉ. प्रवीण श्रीधर जोशी',
    `father_name` = 'श्रीधर रंगनाथ जोशी',
    `dob` = '1982-11-25',
    `aadhaar_last4` = '9120',
    `business_name` = 'मराठवाडा मेडिकल अ‍ॅन्ड जनरल स्टोअर्स',
    `business_type` = 'औषध व वैद्यकीय साहित्य किरकोळ विक्री (Retail Pharmacy)',
    `address` = 'Row House No 5, Near Gajanan Maharaj Temple, Garkheda',
    `city` = 'Chhatrapati Sambhajinagar',
    `district` = 'Chhatrapati Sambhajinagar',
    `state` = 'Maharashtra',
    `pincode` = '431009',
    `status` = 'ACTIVE'
WHERE `id` = 2;

UPDATE `tenants` SET
    `tenant_no` = 'TNT-2021-CSMC-004',
    `tenant_type` = 'company',
    `name_marathi` = 'भारतीय स्टेट बँक (शाखा व्यवस्थापक)',
    `father_name` = 'State Bank of India Corporate Body',
    `dob` = '1955-07-01',
    `aadhaar_last4` = '0000',
    `business_name` = 'State Bank of India (Public Sector Bank)',
    `business_type` = 'शेड्युल्ड व्यावसायिक बँक (Banking & Financial Services)',
    `address` = 'State Bank of India Zonal Office, Town Centre, Cidco',
    `city` = 'Chhatrapati Sambhajinagar',
    `district` = 'Chhatrapati Sambhajinagar',
    `state` = 'Maharashtra',
    `pincode` = '431003',
    `status` = 'ACTIVE'
WHERE `id` = 4;

-- 4.1 Tenant Addresses
DELETE FROM `tenant_addresses`;
INSERT INTO `tenant_addresses` (`tenant_id`, `address_type`, `address_line1`, `address_line2`, `city`, `district`, `state`, `pincode`, `is_primary`) VALUES
(1, 'RESIDENTIAL', 'Plot No. 14, Samarth Nagar', 'Near Water Tank, Town Hall', 'Chhatrapati Sambhajinagar', 'Chhatrapati Sambhajinagar', 'Maharashtra', '431001', 1),
(1, 'BUSINESS_REGISTERED', 'Gala No. G-001, Chhatrapati Shivaji Maharaj Complex', 'Gulmandi Circle', 'Chhatrapati Sambhajinagar', 'Chhatrapati Sambhajinagar', 'Maharashtra', '431001', 0),
(2, 'RESIDENTIAL', 'Row House No 5, Gajanan Nagar', 'Near Temple, Garkheda Parisar', 'Chhatrapati Sambhajinagar', 'Chhatrapati Sambhajinagar', 'Maharashtra', '431009', 1),
(4, 'BUSINESS_REGISTERED', 'SBI Bhavan, Madame Cama Road', 'Nariman Point', 'Mumbai', 'Mumbai City', 'Maharashtra', '400021', 1);

-- 4.2 Tenant Contacts
DELETE FROM `tenant_contacts`;
INSERT INTO `tenant_contacts` (`tenant_id`, `contact_person_name`, `designation`, `mobile`, `alternate_phone`, `email`, `is_primary`) VALUES
(1, 'Vijay M. Patil', 'Proprietor', '9422112233', '0240-2341901', 'vijay.patil@gmail.com', 1),
(2, 'Dr. Pravin S. Joshi', 'Chief Pharmacist / Owner', '9422445566', '0240-2489012', 'dr.joshi@marathwadamed.in', 1),
(4, 'Shri Anand Shekhawat', 'Assistant General Manager (Premises)', '0240-2345678', '9822009988', 'agmpremises.zocsm@sbi.co.in', 1);

-- 4.3 Tenant Businesses
DELETE FROM `tenant_businesses`;
INSERT INTO `tenant_businesses` (`tenant_id`, `business_name`, `trade_license_no`, `business_category`, `nature_of_goods_services`, `annual_turnover_declared`) VALUES
(1, 'Shree Ganesh Cloth Stores', 'CSMC/SHOP/2023/10291', 'Textiles & Garments', 'Suits, Sarees, Shirting and Readymade Clothing', 3800000.00),
(2, 'Marathwada Medical & General Stores', 'FDA-MH-20B-18921', 'Pharmaceutical Retail', 'Life saving drugs, surgical items and personal healthcare', 6200000.00),
(4, 'State Bank of India', 'RBI-LIC-001-SCH-B', 'Public Sector Scheduled Commercial Bank', 'Deposit accounts, Cash Withdrawal ATM, Core Banking Services', 850000000.00);

-- 4.4 Tenant Bank Accounts
DELETE FROM `tenant_bank_accounts`;
INSERT INTO `tenant_bank_accounts` (`tenant_id`, `bank_name`, `branch_name`, `account_no_masked`, `ifsc_code`, `account_type`, `is_verified`) VALUES
(1, 'Bank of Maharashtra', 'Gulmandi Branch', 'XXXX-XXXX-8910', 'MAHB0000045', 'CURRENT', 1),
(2, 'HDFC Bank Ltd', 'Jalna Road Branch', 'XXXX-XXXX-4412', 'HDFC0000189', 'CURRENT', 1),
(4, 'State Bank of India', 'Kranti Chowk Main Branch', 'XXXX-XXXX-1111', 'SBIN0000318', 'CURRENT', 1);

-- 4.5 Tenant Authorized Persons
DELETE FROM `tenant_authorized_persons`;
INSERT INTO `tenant_authorized_persons` (`tenant_id`, `person_name`, `designation`, `pan_no`, `mobile`, `email`, `authorization_letter_ref`) VALUES
(1, 'Sachin Vijay Patil', 'Manager / Son', 'BCDSP9910K', '9890119922', 'sachin.patil@gmail.com', 'AUTH-PATIL-2023/01'),
(4, 'Priya Kulkarni', 'Branch Manager (Scale-III)', 'AKKPK4512N', '9422004411', 'bm.csmc@sbi.co.in', 'SBI/HR/POW/2024/889');

-- 5. UPGRADE LEASE MANAGEMENT WITH 11-STAGE WORKFLOW & MULTI-ACT AUDIT
UPDATE `lease_agreements` SET
    `lease_no` = 'LS-CSMC-2023-001',
    `lease_type` = 'COMMERCIAL_SHOP',
    `allotment_method` = 'PUBLIC_E_AUCTION',
    `application_no` = 'APP-EAUCTION-2023-441',
    `approval_no` = 'RES-SC-2023-89',
    `agreement_no` = 'REG-AGR-2023-101',
    `rent_frequency` = 'MONTHLY',
    `annual_rent` = 180000.00,
    `rent_escalation_type` = 'PERCENTAGE_TRIENNIAL',
    `rent_escalation_value` = 10.00,
    `security_deposit` = 45000.00,
    `premium_amount` = 100000.00,
    `advance_amount` = 15000.00,
    `lock_in_period_months` = 11,
    `notice_period_days` = 60,
    `renewal_allowed` = 1,
    `approved_by` = 'Municipal Commissioner, CSMC',
    `approved_at` = '2023-03-25 15:30:00',
    `status` = 'ACTIVE'
WHERE `id` = 1;

UPDATE `lease_agreements` SET
    `lease_no` = 'LS-CSMC-2023-002',
    `lease_type` = 'COMMERCIAL_SHOP',
    `allotment_method` = 'PUBLIC_E_AUCTION',
    `application_no` = 'APP-EAUCTION-2023-442',
    `approval_no` = 'RES-SC-2023-90',
    `agreement_no` = 'REG-AGR-2023-102',
    `rent_frequency` = 'MONTHLY',
    `annual_rent` = 180000.00,
    `rent_escalation_type` = 'PERCENTAGE_TRIENNIAL',
    `rent_escalation_value` = 10.00,
    `security_deposit` = 45000.00,
    `premium_amount` = 100000.00,
    `advance_amount` = 15000.00,
    `lock_in_period_months` = 11,
    `notice_period_days` = 60,
    `renewal_allowed` = 1,
    `approved_by` = 'Municipal Commissioner, CSMC',
    `approved_at` = '2023-03-26 11:00:00',
    `status` = 'ACTIVE'
WHERE `id` = 2;

-- 5.1 LEASE WORKFLOW APPROVAL HISTORY AUDIT (Showing 11-Stage Workflow Stages)
DELETE FROM `lease_approval_history`;
INSERT INTO `lease_approval_history` (`lease_id`, `stage`, `action`, `action_by_role`, `action_by_name`, `comments`, `action_timestamp`) VALUES
(1, 'DRAFT', 'SUBMITTED', 'ESTATE_CLERK', 'Ganesh More', 'Draft allotment file generated from winning auction bid Rs 15,000/month', '2023-03-01 10:30:00'),
(1, 'SUBMITTED', 'FORWARDED', 'REVENUE_INSPECTOR', 'Anita Shinde', 'Physical shop dimensions verified on site (15x20 ft, 300 sq.ft carpet)', '2023-03-03 14:15:00'),
(1, 'DOCUMENT_VERIFICATION', 'APPROVED', 'SUPERINTENDENT', 'Ramesh Kulkarni', 'Aadhaar, PAN, GST and EMD deposit challan Rs 1,00,000 verified', '2023-03-06 16:45:00'),
(1, 'ESTATE_OFFICER_APPROVAL', 'APPROVED', 'DY_COMMISSIONER', 'Sanjay Pawar', 'Sanctioned under Section 79 MMCA 1949 with standard 3-year term', '2023-03-10 11:30:00'),
(1, 'FINANCIAL_APPROVAL', 'APPROVED', 'CHIEF_ACCOUNTS_OFFICER', 'Prakash Gaikwad', 'Security deposit 3 months Rs 45,000 and 1st month advance credited to municipal treasury', '2023-03-14 15:00:00'),
(1, 'LEGAL_VERIFICATION', 'APPROVED', 'LEGAL_ADVISOR', 'Adv. M. V. Deshpande', 'Lease deed covenants strictly adhere to municipal model agreement', '2023-03-18 12:20:00'),
(1, 'COMPETENT_AUTHORITY_APPROVAL', 'APPROVED', 'COMMISSIONER', 'G. Shrikant, IAS', 'Final approval accorded as per Standing Committee Resolution No 89', '2023-03-22 17:00:00'),
(1, 'AGREEMENT_GENERATED', 'FORWARDED', 'ESTATE_OFFICER', 'Ramesh Kulkarni', 'Bilingual stamp agreement printed on Rs 500 Government e-Stamp', '2023-03-24 10:00:00'),
(1, 'EXECUTED', 'APPROVED', 'SUB_REGISTRAR', 'Sub-Registrar Aurangabad-1', 'Registered under SR-AUR/4502/2023 with stamp duty payment', '2023-03-28 14:30:00'),
(1, 'ACTIVE', 'APPROVED', 'ESTATE_OFFICER', 'Ramesh Kulkarni', 'Possession delivered and shop status activated in municipal ledger', '2023-04-01 09:00:00');