-- ==============================================================================
-- महाराष्ट्र महानगरपालिका संकुल व मालमत्ता महसूल व्यवस्थापन प्रणाली - SEED DATA
-- MAHARASHTRA MUNICIPAL PROPERTY ERP - REALISTIC PRODUCTION SEEDS
-- ==============================================================================

-- 1. Municipal Corporation Master
INSERT INTO `ulb_bodies` (`id`, `code`, `name_en`, `name_mr`, `ulb_type`, `district`, `division`, `established_year`, `address`, `commissioner_name`, `email`, `phone`, `active`) VALUES
(1, 'CSMC-01', 'Chhatrapati Sambhajinagar Municipal Corporation', 'छत्रपती संभाजीनगर महानगरपालिका', 'MUNICIPAL_CORP_B', 'Chhatrapati Sambhajinagar', 'Marathwada', 1982, 'Town Hall, Chhatrapati Sambhajinagar - 431001', 'G. Shrikant, IAS', 'commissioner@csmc.gov.in', '0240-2331201', 1),
(2, 'PMC-01', 'Pune Municipal Corporation', 'पुणे महानगरपालिका', 'MUNICIPAL_CORP_A', 'Pune', 'Pune', 1950, 'PMC Main Building, Shivajinagar, Pune - 411005', 'Vikram Kumar, IAS', 'comm@punecorporation.org', '020-25501000', 1);

-- 2. Zones
INSERT INTO `zones` (`id`, `ulb_id`, `zone_no`, `name_en`, `name_mr`, `office_address`, `ward_officer_name`, `phone`) VALUES
(1, 1, 1, 'Zone 1 - Central City', 'झोन १ - मध्यवर्ती शहर', 'Buddi Lane, CSMC Ward Office No 1', 'Sunil Jadhav', '9822101122'),
(2, 1, 2, 'Zone 2 - Cidco & Hudco', 'झोन २ - सिडको-हडको', 'Cidco N-5 Ward Office', 'Meena Deshmukh', '9822103344'),
(3, 1, 3, 'Zone 3 - Railway Station & Kranti Chowk', 'झोन ३ - रेल्वे स्टेशन व क्रांती चौक', 'Station Road Office', 'Rajendra Patil', '9822105566');

-- 3. Wards
INSERT INTO `wards` (`id`, `ulb_id`, `zone_id`, `ward_no`, `name_en`, `name_mr`, `prabhag_samiti`, `population`, `area_sqkm`, `active`) VALUES
(1, 1, 1, 'Ward-01', 'Gulmandi - Shahgunj', 'गुलमंडी - शहागंज', 'मध्यवर्ती प्रभाग समिती', 45000, 3.20, 1),
(2, 1, 1, 'Ward-02', 'Aurangpura - Nirala Bazaar', 'औरंगपुरा - निराला बाजार', 'मध्यवर्ती प्रभाग समिती', 42000, 2.80, 1),
(3, 1, 2, 'Ward-07', 'Cidco Cannaught Place', 'सिडको कॅनॉट प्लेस', 'सिडको प्रभाग समिती', 58000, 4.50, 1),
(4, 1, 3, 'Ward-12', 'Kranti Chowk - Usmanpura', 'क्रांती चौक - उस्मानपुरा', 'दक्षिण प्रभाग समिती', 51000, 3.90, 1);

-- 4. Departments
INSERT INTO `departments` (`id`, `ulb_id`, `code`, `name_en`, `name_mr`, `head_designation`, `active`) VALUES
(1, 1, 'ESTATE', 'Estate & Property Management Department', 'मालमत्ता व संकुल व्यवस्थापन विभाग', 'उपायुक्त (मालमत्ता)', 1),
(2, 1, 'ACCOUNTS', 'Finance & Accounts Department', 'वित्त व लेखा विभाग', 'मुख्य लेखाधिकारी', 1),
(3, 1, 'LEGAL', 'Legal & Litigation Cell', 'विधी व न्यायालयीन कक्ष', 'मुख्य विधी सल्लागार', 1),
(4, 1, 'PWD', 'Civil Works & Maintenance Department', 'सार्वजनिक बांधकाम व देखभाल विभाग', 'शहर अभियंता', 1);

-- 5. Designations
INSERT INTO `designations` (`id`, `department_id`, `title_en`, `title_mr`, `cadre`, `grade_pay`) VALUES
(1, 1, 'Deputy Municipal Commissioner (Estate)', 'उपायुक्त (मालमत्ता व संकुल)', 'Group-A (IAS/State Civil)', 'Level-14'),
(2, 1, 'Estate Superintendent', 'मालमत्ता अधीक्षक', 'Group-B Gazetted', 'Level-10'),
(3, 1, 'Revenue & Market Inspector', 'महसूल व बाजार निरीक्षक', 'Group-C', 'Level-7'),
(4, 2, 'Chief Accounts Officer', 'मुख्य लेखा व वित्त अधिकारी', 'Group-A', 'Level-13'),
(5, 4, 'Executive Engineer (Civil/PWD)', 'कार्यकारी अभियंता (स्थापत्य)', 'Group-A', 'Level-12');

-- 6. Users
INSERT INTO `users` (`id`, `ulb_id`, `department_id`, `designation_id`, `name`, `username`, `email`, `phone`, `password_hash`, `role`, `status`) VALUES
(1, 1, 1, 1, 'Shri Sanjay Pawar', 'commissioner', 'commissioner@csmc.gov.in', '9822001100', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'COMMISSIONER', 'ACTIVE'),
(2, 1, 1, 2, 'Shri Ramesh Kulkarni', 'estate_officer', 'estate@csmc.gov.in', '9822002200', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ESTATE_OFFICER', 'ACTIVE'),
(3, 1, 1, 3, 'Smt Anita Shinde', 'inspector1', 'anita.shinde@csmc.gov.in', '9822003300', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'REVENUE_INSPECTOR', 'ACTIVE'),
(4, 1, 2, 4, 'Shri Prakash Gaikwad', 'accounts_officer', 'accounts@csmc.gov.in', '9822004400', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'ACCOUNTS_OFFICER', 'ACTIVE'),
(5, 1, 4, 5, 'Er. Nitin Chavan', 'pwd_engineer', 'pwd@csmc.gov.in', '9822005500', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'PWD_ENGINEER', 'ACTIVE');

-- 7. Financial Years
INSERT INTO `financial_years` (`id`, `code`, `start_date`, `end_date`, `is_current`, `is_closed`) VALUES
(1, '2024-2025', '2024-04-01', '2025-03-31', 0, 1),
(2, '2025-2026', '2025-04-01', '2026-03-31', 0, 1),
(3, '2026-2027', '2026-04-01', '2027-03-31', 1, 0);

-- 8. Document Templates
INSERT INTO `document_templates` (`id`, `code`, `title_mr`, `title_en`, `category`, `header_html`, `body_template`, `footer_html`) VALUES
(1, 'SEC_81_NOTICE', 'महाराष्ट्र महानगरपालिका अधिनियम कलम ८१ अन्वये थकबाकी मागणी व इशारा नोटीस', 'Notice under Section 81 of MMCA 1949 for Recovery of Arrears', 'NOTICE', '<h4>छत्रपती संभाजीनगर महानगरपालिका - मालमत्ता व महसूल विभाग</h4>', 'प्रति, {TENANT_NAME}, गाळा क्र. {UNIT_NO}, संकुल: {COMPLEX_NAME}. आपणास कळविण्यात येते की आपल्या गाळ्याची एकूण थकबाकी रक्कम रु. {TOTAL_DUES}/- थकीत आहे. सदर रक्कम ही नोटीस मिळाल्यापासून १५ दिवसांच्या आत महापालिका कोषागारात जमा करावी, अन्यथा गाळा सील केला जाईल.', '<p>स्वाक्षरी: अधिकृत मालमत्ता अधिकारी, महानगरपालिका</p>'),
(2, 'RENT_RECEIPT', 'महापालिका गाळा भाडे अधिकृत पावती', 'Municipal Property Rent Payment Official Receipt', 'RECEIPT', '<h4>छत्रपती संभाजीनगर महानगरपालिका - संगणकीय पावती</h4>', '<p>पावती क्र: {RECEIPT_NO} | तारीख: {RECEIPT_DATE} | भाडेकरू: {TENANT_NAME} | गाळा क्र: {UNIT_NO} | जमा रक्कम: रु. {AMOUNT} (अक्षरी: {AMOUNT_WORDS})</p>', '<p>सदर संगणकीय पावती असून स्वाक्षरीची आवश्यकता नाही.</p>');

-- 9. Properties (Land)
INSERT INTO `properties_land` (`id`, `ulb_id`, `ward_id`, `property_code`, `survey_no`, `cts_no`, `gat_no`, `plot_no`, `title_ownership`, `reservation_purpose`, `land_area_sqm`, `property_card_no`, `active`) VALUES
(1, 1, 1, 'LAND-CSMC-001', 'S.No. 45/2', 'CTS-1204', NULL, 'Plot-A', 'MUNICIPAL_OWNED', 'व्यापारी संकुल व भाजी मंडई आरक्षण', 5400.00, 'PR-CARD-1204/CS', 1),
(2, 1, 2, 'LAND-CSMC-002', 'S.No. 89/1', 'CTS-3410', NULL, 'Plot-12', 'MUNICIPAL_OWNED', 'बहुउद्देशीय व्यापारी व कार्यालयीन संकुल', 3800.00, 'PR-CARD-3410/CS', 1),
(3, 1, 3, 'LAND-CSMC-003', 'S.No. 112', 'CTS-8900', 'Gat-44', 'Plot-C1', 'MUNICIPAL_OWNED', 'कॅनॉट प्लेस कमर्शियल कॉम्प्लेक्स व मंगल कार्यालय', 7200.00, 'PR-CARD-8900/CD', 1);

-- 10. Complexes (संकुल Master)
INSERT INTO `complexes` (`id`, `ulb_id`, `ward_id`, `land_property_id`, `complex_code`, `name_en`, `name_mr`, `category`, `total_buildings`, `total_units`, `construction_year`, `ready_reckoner_rate_sqm`, `gis_latitude`, `gis_longitude`, `photo_url`, `active`) VALUES
(1, 1, 1, 1, 'CMPLX-01', 'Chhatrapati Shivaji Maharaj Commercial Complex', 'छत्रपती शिवाजी महाराज व्यापारी संकुल, गुलमंडी', 'COMMERCIAL_COMPLEX', 1, 48, 2012, 65000.00, 19.88210000, 75.33400000, 'assets/img/complex1.jpg', 1),
(2, 1, 2, 2, 'CMPLX-02', 'Dr. Babasaheb Ambedkar Commercial Plaza', 'डॉ. बाबासाहेब आंबेडकर व्यापारी संकुल, औरंगपुरा', 'COMMERCIAL_COMPLEX', 1, 36, 2016, 72000.00, 19.87850000, 75.32680000, 'assets/img/complex2.jpg', 1),
(3, 1, 3, 3, 'CMPLX-03', 'Connaught Place Shopping & Community Hall', 'कॅनॉट प्लेस व्यापारी संकुल व समाज मंदिर, सिडको', 'MULTIPURPOSE_HALL', 2, 64, 2018, 58000.00, 19.87120000, 75.36210000, 'assets/img/complex3.jpg', 1);

-- 11. Buildings
INSERT INTO `buildings` (`id`, `complex_id`, `building_code`, `name_en`, `name_mr`, `construction_type`, `total_floors`, `oc_number`, `oc_date`, `built_up_area_sqm`, `active`) VALUES
(1, 1, 'BLDG-01-A', 'Main Commercial Wing', 'मुख्य व्यापारी इमारत', 'RCC', 3, 'OC/2012/441', '2012-08-15', 3600.00, 1),
(2, 2, 'BLDG-02-A', 'Ambedkar Plaza Wing A', 'आंबेडकर प्लाझा मुख्य विंग', 'RCC', 4, 'OC/2016/892', '2016-11-20', 2900.00, 1),
(3, 3, 'BLDG-03-A', 'Shopping Arcade Wing', 'शॉपिंग आर्केड', 'RCC', 2, 'OC/2018/104', '2018-05-10', 4200.00, 1),
(4, 3, 'BLDG-03-B', 'Savitribai Phule Community Hall', 'सावित्रीबाई फुले समाज मंदिर / मंगल कार्यालय', 'RCC', 2, 'OC/2018/105', '2018-05-10', 1800.00, 1);

-- 12. Floors
INSERT INTO `floors` (`id`, `building_id`, `floor_number`, `floor_name_en`, `floor_name_mr`, `total_units`) VALUES
(1, 1, 0, 'Ground Floor', 'तळमजला', 16),
(2, 1, 1, 'First Floor', 'पहिला मजला', 16),
(3, 1, 2, 'Second Floor', 'दुसरा मजला', 16),
(4, 2, 0, 'Ground Floor', 'तळमजला', 18),
(5, 2, 1, 'First Floor', 'पहिला मजला', 18),
(6, 3, 0, 'Ground Floor', 'तळमजला', 32),
(7, 4, 0, 'Ground Floor (Hall & Lawn)', 'तळमजला (मुख्य सभागृह व लॉन)', 2);

-- 13. Units / Galas
INSERT INTO `units_galas` (`id`, `ulb_id`, `ward_id`, `complex_id`, `building_id`, `floor_id`, `unit_code`, `unit_number`, `unit_name`, `category`, `length_ft`, `width_ft`, `carpet_area_sqft`, `built_up_area_sqft`, `carpet_area_sqm`, `shutter_size`, `front_facing`, `electricity_meter_no`, `water_connection_no`, `base_ready_reckoner_rent`, `current_market_rent`, `status`) VALUES
(1, 1, 1, 1, 1, 1, 'GALA-01-G-01', 'G-01', 'Shree Ganesh Cloth Stores', 'SHOP', 15.00, 20.00, 300.00, 375.00, 27.87, '10x8 ft', 'MAIN_ROAD', 'MSEDCL-889102', 'MC-W-104', 12500.00, 15000.00, 'OCCUPIED'),
(2, 1, 1, 1, 1, 1, 'GALA-01-G-02', 'G-02', 'Marathwada Medical & General', 'SHOP', 15.00, 20.00, 300.00, 375.00, 27.87, '10x8 ft', 'MAIN_ROAD', 'MSEDCL-889103', 'MC-W-105', 12500.00, 15000.00, 'OCCUPIED'),
(3, 1, 1, 1, 1, 1, 'GALA-01-G-03', 'G-03', 'Ajanta Mobile World', 'SHOP', 12.00, 18.00, 216.00, 270.00, 20.07, '8x8 ft', 'INTERNAL_CORRIDOR', 'MSEDCL-889104', 'MC-W-106', 9000.00, 11500.00, 'OCCUPIED'),
(4, 1, 1, 1, 1, 1, 'GALA-01-G-04', 'G-04', 'State Bank of India ATM', 'ATM', 10.00, 10.00, 100.00, 130.00, 9.29, 'Glass Shutter', 'MAIN_ROAD', 'MSEDCL-889105', NULL, 15000.00, 20000.00, 'OCCUPIED'),
(5, 1, 1, 1, 1, 1, 'GALA-01-G-05', 'G-05', 'Vacant Commercial Gala', 'SHOP', 15.00, 20.00, 300.00, 375.00, 27.87, '10x8 ft', 'INTERNAL_CORRIDOR', NULL, NULL, 12500.00, 14000.00, 'VACANT'),
(6, 1, 1, 1, 1, 1, 'GALA-01-G-06', 'G-06', 'Sealed Due to Chronic Default', 'SHOP', 15.00, 20.00, 300.00, 375.00, 27.87, '10x8 ft', 'INTERNAL_CORRIDOR', 'MSEDCL-889108', 'MC-W-110', 12500.00, 14000.00, 'SEALED'),
(7, 1, 2, 2, 2, 4, 'GALA-02-G-11', 'G-11', 'Bachat Gat Mahila Kendra', 'SHOP', 18.00, 22.00, 396.00, 490.00, 36.79, '12x8 ft', 'MAIN_ROAD', 'MSEDCL-771201', 'MC-W-201', 16000.00, 18500.00, 'OCCUPIED'),
(8, 1, 3, 3, 4, 7, 'HALL-03-H-01', 'HALL-01', 'Savitribai Phule Multipurpose Hall', 'COMMUNITY_HALL', 60.00, 80.00, 4800.00, 5800.00, 445.93, 'Grand Double Shutter', 'MAIN_ROAD', 'MSEDCL-990011', 'MC-W-801', 35000.00, 50000.00, 'OCCUPIED');

-- 14. Tenants
INSERT INTO `tenants` (`id`, `ulb_id`, `tenant_code`, `full_name_en`, `full_name_mr`, `father_or_husband_name`, `mobile`, `email`, `pan_no`, `gst_no`, `residential_address`, `business_entity_type`, `category_quota`, `kyc_status`, `active`) VALUES
(1, 1, 'TNT-001', 'Vijay Madhavrao Patil', 'विजय माधवराव पाटील', 'माधवराव पाटील', '9422112233', 'vijay.patil@gmail.com', 'ABCPP1234D', '27ABCPP1234D1Z5', 'Samarth Nagar, Chhatrapati Sambhajinagar', 'PROPRIETORSHIP', 'GENERAL', 'VERIFIED', 1),
(2, 1, 'TNT-002', 'Dr. Pravin Shridhar Joshi', 'डॉ. प्रवीण श्रीधर जोशी', 'श्रीधर जोशी', '9422445566', 'dr.joshi@marathwadamed.in', 'XYZPJ5678K', '27XYZPJ5678K1Z2', 'Garkheda Parisar, Chhatrapati Sambhajinagar', 'PROPRIETORSHIP', 'GENERAL', 'VERIFIED', 1),
(3, 1, 'TNT-003', 'Shaikh Naimuddin Shaikh Bashir', 'शेख नईमुद्दीन शेख बशीर', 'शेख बशीर', '9890112244', 'naim.ajanta@yahoo.com', 'ABCPS8899M', NULL, 'Shahgunj, Chhatrapati Sambhajinagar', 'PROPRIETORSHIP', 'GENERAL', 'VERIFIED', 1),
(4, 1, 'TNT-004', 'State Bank of India (Branch Manager)', 'भारतीय स्टेट बँक (शाखा व्यवस्थापक)', 'Chief Manager Commercial', '0240-2345678', 'bm.csmc@sbi.co.in', 'AAACS8577K', '27AAACS8577K1Z8', 'Kranti Chowk Main Branch', 'PUBLIC_LTD', 'GENERAL', 'VERIFIED', 1),
(5, 1, 'TNT-005', 'Rajesh Baliram More (Defaulter)', 'राजेश बळीराम मोरे (थकबाकीदार)', 'बळीराम मोरे', '9823998877', 'rajesh.more@gmail.com', 'BKMPM3344Q', NULL, 'Bhavsinghpura, Chhatrapati Sambhajinagar', 'INDIVIDUAL', 'OBC', 'VERIFIED', 1),
(6, 1, 'TNT-006', 'Smt. Sunita Ramesh Sonawane (Bachat Gat)', 'श्रीमती सुनिता रमेश सोनवणे (महालक्ष्मी बचत गट)', 'रमेश सोनवणे', '9766554433', 'mahalaxmi.shg@gmail.com', 'AARPS1122H', NULL, 'N-6 Cidco, Chhatrapati Sambhajinagar', 'SHG_BACHATGAT', 'WOMEN_SHG', 'VERIFIED', 1);

-- 15. Lease Agreements
INSERT INTO `lease_agreements` (`id`, `ulb_id`, `unit_id`, `tenant_id`, `agreement_number`, `allotment_mode`, `start_date`, `end_date`, `tenure_months`, `monthly_rent`, `maintenance_charge`, `water_sanitation_cess`, `gst_rate_percent`, `escalation_rate_percent`, `escalation_frequency_months`, `security_deposit_amount`, `sub_registrar_reg_no`, `stamp_duty_paid`, `status`) VALUES
(1, 1, 1, 1, 'AGR/CSMC/2023/101', 'PUBLIC_E_AUCTION', '2023-04-01', '2026-03-31', 36, 15000.00, 1000.00, 500.00, 18.00, 10.00, 36, 45000.00, 'SR-AUR/4502/2023', 5000.00, 'ACTIVE'),
(2, 1, 2, 2, 'AGR/CSMC/2023/102', 'PUBLIC_E_AUCTION', '2023-04-01', '2026-03-31', 36, 15000.00, 1000.00, 500.00, 18.00, 10.00, 36, 45000.00, 'SR-AUR/4503/2023', 5000.00, 'ACTIVE'),
(3, 1, 3, 3, 'AGR/CSMC/2022/88', 'PUBLIC_E_AUCTION', '2022-10-01', '2025-09-30', 36, 11500.00, 800.00, 400.00, 18.00, 10.00, 36, 34500.00, 'SR-AUR/7810/2022', 4000.00, 'ACTIVE'),
(4, 1, 4, 4, 'AGR/CSMC/2021/45', 'TENDER', '2021-01-01', '2031-12-31', 120, 20000.00, 1500.00, 500.00, 18.00, 15.00, 36, 120000.00, 'SR-AUR/112/2021', 15000.00, 'ACTIVE'),
(5, 1, 6, 5, 'AGR/CSMC/2020/12', 'PUBLIC_E_AUCTION', '2020-04-01', '2023-03-31', 36, 14000.00, 1000.00, 500.00, 18.00, 10.00, 36, 42000.00, 'SR-AUR/991/2020', 4500.00, 'TERMINATED'),
(6, 1, 7, 6, 'AGR/CSMC/2024/210', 'RESERVATION_QUOTA', '2024-04-01', '2027-03-31', 36, 9250.00, 800.00, 300.00, 0.00, 10.00, 36, 27750.00, 'SR-AUR/2109/2024', 3000.00, 'ACTIVE');


-- 16. Security Deposits
INSERT INTO `security_deposits` (`id`, `lease_id`, `tenant_id`, `deposit_amount`, `receipt_number`, `deposit_date`, `refund_status`) VALUES
(1, 1, 1, 45000.00, 'DEP-REC-2023-001', '2023-03-25', 'HELD_ACTIVE'),
(2, 2, 2, 45000.00, 'DEP-REC-2023-002', '2023-03-26', 'HELD_ACTIVE'),
(3, 3, 3, 34500.00, 'DEP-REC-2022-045', '2022-09-20', 'HELD_ACTIVE'),
(4, 4, 4, 120000.00, 'DEP-REC-2021-012', '2020-12-15', 'HELD_ACTIVE'),
(5, 5, 5, 42000.00, 'DEP-REC-2020-008', '2020-03-10', 'FORFEITED_DEFAULT');

-- 17. Demand Batches
INSERT INTO `demand_batches` (`id`, `ulb_id`, `financial_year_id`, `billing_month`, `billing_year`, `total_bills_generated`, `total_demand_amount`, `created_at`) VALUES
(1, 1, 3, 9, 2026, 6, 158420.00, '2026-09-01 06:00:00');

-- 18. Demand Bills
INSERT INTO `demand_bills` (`id`, `ulb_id`, `demand_batch_id`, `unit_id`, `tenant_id`, `lease_id`, `bill_number`, `bill_date`, `due_date`, `billing_month`, `billing_year`, `base_rent`, `maintenance_fee`, `water_sanitation_cess`, `gst_amount`, `previous_arrears_principal`, `previous_arrears_interest`, `penalty_interest`, `total_demand_gross`, `total_paid_amount`, `balance_due_amount`, `payment_status`, `upi_qr_payload`) VALUES
(1, 1, 1, 1, 1, 1, 'BILL-CSMC-2026-09-001', '2026-09-01', '2026-09-15', 9, 2026, 15000.00, 1000.00, 500.00, 2700.00, 0.00, 0.00, 0.00, 19200.00, 19200.00, 0.00, 'PAID', 'upi://pay?pa=csmc.estate@sbi&pn=CSMC_MUNICIPAL&am=19200.00&tr=BILL-CSMC-2026-09-001'),
(2, 1, 1, 2, 2, 2, 'BILL-CSMC-2026-09-002', '2026-09-01', '2026-09-15', 9, 2026, 15000.00, 1000.00, 500.00, 2700.00, 0.00, 0.00, 0.00, 19200.00, 19200.00, 0.00, 'PAID', 'upi://pay?pa=csmc.estate@sbi&pn=CSMC_MUNICIPAL&am=19200.00&tr=BILL-CSMC-2026-09-002'),
(3, 1, 1, 3, 3, 3, 'BILL-CSMC-2026-09-003', '2026-09-01', '2026-09-15', 9, 2026, 11500.00, 800.00, 400.00, 2070.00, 14770.00, 590.80, 295.40, 30426.20, 0.00, 30426.20, 'OVERDUE', 'upi://pay?pa=csmc.estate@sbi&pn=CSMC_MUNICIPAL&am=30426.20&tr=BILL-CSMC-2026-09-003'),
(4, 1, 1, 4, 4, 4, 'BILL-CSMC-2026-09-004', '2026-09-01', '2026-09-15', 9, 2026, 20000.00, 1500.00, 500.00, 3600.00, 0.00, 0.00, 0.00, 25600.00, 25600.00, 0.00, 'PAID', 'upi://pay?pa=csmc.estate@sbi&pn=CSMC_MUNICIPAL&am=25600.00&tr=BILL-CSMC-2026-09-004'),
(5, 1, 1, 6, 5, 5, 'BILL-CSMC-2026-09-005', '2026-09-01', '2026-09-15', 9, 2026, 14000.00, 1000.00, 500.00, 2520.00, 180240.00, 28838.40, 3604.80, 230703.20, 0.00, 230703.20, 'OVERDUE', 'upi://pay?pa=csmc.estate@sbi&pn=CSMC_MUNICIPAL&am=230703.20&tr=BILL-CSMC-2026-09-005'),
(6, 1, 1, 7, 6, 6, 'BILL-CSMC-2026-09-006', '2026-09-01', '2026-09-15', 9, 2026, 9250.00, 800.00, 300.00, 0.00, 0.00, 0.00, 0.00, 10350.00, 10350.00, 0.00, 'PAID', 'upi://pay?pa=csmc.estate@sbi&pn=CSMC_MUNICIPAL&am=10350.00&tr=BILL-CSMC-2026-09-006');

-- 19. Payment Receipts
INSERT INTO `payment_receipts` (`id`, `ulb_id`, `bill_id`, `tenant_id`, `unit_id`, `receipt_number`, `receipt_date`, `amount_paid`, `principal_settled`, `interest_settled`, `payment_mode`, `transaction_ref_no`, `bank_name`, `created_at`) VALUES
(1, 1, 1, 1, 1, 'RCP-2026-09-0012', '2026-09-05', 19200.00, 19200.00, 0.00, 'UPI_BHIM', 'UPI/SBI/62491028472', 'State Bank of India', '2026-09-05 11:30:00'),
(2, 1, 2, 2, 2, 'RCP-2026-09-0013', '2026-09-06', 19200.00, 19200.00, 0.00, 'ONLINE_GATEWAY', 'RZP_PAY_991823719', 'HDFC Bank', '2026-09-06 14:15:00'),
(3, 1, 4, 4, 4, 'RCP-2026-09-0014', '2026-09-08', 25600.00, 25600.00, 0.00, 'NEFT_RTGS', 'SBIN00012398471', 'State Bank of India', '2026-09-08 16:45:00'),
(4, 1, 6, 6, 7, 'RCP-2026-09-0015', '2026-09-10', 10350.00, 10350.00, 0.00, 'CASH', 'CSMC-CASH-CHALLAN-901', 'CSMC Cash Counter No. 2', '2026-09-10 12:10:00');

-- 20. Statutory Notices (Section 81 MMCA)
INSERT INTO `statutory_notices` (`id`, `ulb_id`, `unit_id`, `tenant_id`, `bill_id`, `notice_number`, `notice_type`, `issue_date`, `compliance_deadline`, `total_dues_demanded`, `served_mode`, `served_date`, `status`, `sealing_executed_date`) VALUES
(1, 1, 6, 5, 5, 'NOT/CSMC/EST/2026/089', 'SHOP_SEALING_ORDER', '2026-08-10', '2026-08-25', 230703.20, 'PASTED_ON_SHUTTER', '2026-08-12', 'SEALED_EXECUTED', '2026-08-28'),
(2, 1, 3, 3, 3, 'NOT/CSMC/EST/2026/112', 'SEC_81_DEMAND_7_DAYS', '2026-09-18', '2026-09-25', 30426.20, 'HAND_DELIVERY_SUMMONS', '2026-09-19', 'SERVED', NULL);

-- 21. Contractors
INSERT INTO `contractors` (`id`, `ulb_id`, `company_name`, `contact_person`, `mobile`, `pwd_class`, `pan_no`, `gst_no`, `active`) VALUES
(1, 1, 'M/s Shinde Infrastructure & Maintenance Pvt Ltd', 'Anand Shinde', '9822441188', 'CLASS_II', 'AAECS4912J', '27AAECS4912J1Z3', 1),
(2, 1, 'Prathamesh Electricals & Civil Contractors', 'Prashant Kale', '9890887766', 'CLASS_IV', 'ALPPK9921D', '27ALPPK9921D1Z9', 1);

-- 22. Maintenance Tickets
INSERT INTO `maintenance_tickets` (`id`, `ulb_id`, `complex_id`, `building_id`, `unit_id`, `ticket_number`, `complainant_name`, `complainant_mobile`, `issue_category`, `description`, `priority`, `status`) VALUES
(1, 1, 1, 1, 1, 'TCK-2026-0045', 'Vijay Patil', '9422112233', 'DRAINAGE_LEAKAGE', 'तळमजल्यावरील सांडपाणी वाहिनी चोकअप झाली असून दुर्गंधी पसरत आहे.', 'HIGH', 'WORK_IN_PROGRESS'),
(2, 1, 2, 2, NULL, 'TCK-2026-0046', 'Dr. Pravin Joshi', '9422445566', 'ELECTRICAL_COMMON', 'कॉरिडोरमधील ६ एलईडी दिवे बंद असून रात्री अंधार असतो.', 'MEDIUM', 'RESOLVED'),
(3, 1, 3, 3, NULL, 'TCK-2026-0047', 'Savitribai Phule Hall Manager', '9766554433', 'WATER_SUPPLY', 'समाज मंदिर छतावरील पाण्याची टाकी भरताना व्हॉल्व्ह खराब झाल्याने गळती होत आहे.', 'MEDIUM', 'ESTIMATE_APPROVED');

-- 23. Hall Bookings
INSERT INTO `hall_bookings` (`id`, `ulb_id`, `unit_id`, `booking_reference_no`, `applicant_name`, `applicant_mobile`, `purpose`, `start_date`, `end_date`, `slot`, `rent_amount`, `cleaning_charges`, `security_deposit`, `total_payable`, `payment_status`) VALUES
(1, 1, 8, 'BK-HALL-2026-101', 'Sunil Babanrao Deshmukh', '9822114477', 'WEDDING', '2026-10-15', '2026-10-15', 'FULL_DAY', 50000.00, 2000.00, 10000.00, 62000.00, 'CONFIRMED'),
(2, 1, 8, 'BK-HALL-2026-102', 'Marathwada Sahitya Parishad', '9422118899', 'CULTURAL_EVENT', '2026-10-22', '2026-10-23', 'FULL_DAY', 70000.00, 3000.00, 10000.00, 83000.00, 'CONFIRMED');

-- 24. Defaulter Risk Scores (AI Analysis)
INSERT INTO `defaulter_risk_scores` (`id`, `unit_id`, `tenant_id`, `risk_score`, `risk_tier`, `average_delay_days`, `consecutive_missed_months`, `total_arrears_balance`, `ai_recommendation`) VALUES
(1, 1, 1, 12, 'LOW_RISK', 3, 0, 0.00, 'नियमित देयक भरणारा भाडेकरू. कोणताही कारवाईची आवश्यकता नाही.'),
(2, 2, 2, 8, 'LOW_RISK', 2, 0, 0.00, 'वेळेवर ऑनलाइन पेमेंट. ५% आगाऊ सवलतीसाठी पात्र.'),
(3, 3, 3, 74, 'HIGH_RISK', 42, 2, 30426.20, 'सलग २ महिने थकीत. कलम ८१ ची अंतिम १५ दिवसांची नोटीस बजावणे आवश्यक.'),
(4, 6, 5, 98, 'CHRONIC_DEFAULTER', 380, 18, 230703.20, 'दीर्घकालीन थकबाकीदार. गाळा सील केला असून ई-लिलावाद्वारे पुनर्वाटप करावे.');

-- 25. Service Applications (RTS Act)
INSERT INTO `service_applications` (`id`, `ulb_id`, `application_no`, `applicant_name`, `applicant_mobile`, `service_type`, `unit_id`, `submission_date`, `sla_deadline_date`, `current_status`) VALUES
(1, 1, 'APP-RTS-2026-001', 'Ganesh Vijay Patil', '9422112233', 'TENANCY_MUTATION', 1, '2026-09-12', '2026-09-27', 'UNDER_SCRUTINY'),
(2, 1, 'APP-RTS-2026-002', 'Dr. Pravin Joshi', '9422445566', 'RENT_NOC', 2, '2026-09-20', '2026-09-27', 'APPROVED');

-- 26. Monthly DCB Summaries (म.व.शि.)
INSERT INTO `dcb_monthly_summaries` (`id`, `ulb_id`, `ward_id`, `complex_id`, `financial_year_id`, `summary_month`, `summary_year`, `opening_arrears`, `current_demand`, `total_demand`, `collection_arrears`, `collection_current`, `total_collection`, `balance_arrears`, `balance_current`, `total_balance`, `recovery_percentage`) VALUES
(1, 1, 1, 1, 3, 9, 2026, 195010.00, 89600.00, 284610.00, 0.00, 64000.00, 64000.00, 195010.00, 25600.00, 220610.00, 22.48),
(2, 1, 2, 2, 3, 9, 2026, 45000.00, 68000.00, 113000.00, 15000.00, 52000.00, 67000.00, 30000.00, 16000.00, 46000.00, 59.29),
(3, 1, 3, 3, 3, 9, 2026, 32000.00, 95000.00, 127000.00, 12000.00, 78000.00, 90000.00, 20000.00, 17000.00, 37000.00, 70.87);