from datetime import date from decimal import Decimal from app.core.security import get_password_hash from app.db.master_data import upsert_budget_heads, upsert_item_master from app.db.migrations import apply_master_format_migrations from app.db.session import Base, SessionLocal, engine from app.models.enums import ProcurementStatus, Role from app.models.procurement import ( DeliveryAcceptance, Demand, ETenderCase, EMD, FinancialEvaluation, GeMProcurementCase, GlobalTenderCase, LPCProcurementCase, OpenMarketCase, OtherProcurementCase, PaymentTracking, PerformanceSecurity, ProcurementCase, PurchaseOrder, Requisition, SampleRegister, TechnicalEvaluation, TenderBid, ) from app.models.master_data import BudgetHead from app.models.user import User PROCUREMENT_MODE_SAMPLES = { "GEM": [ { "case_no": "PC-2026-GEM-001", "quantity": 12, "cost": "480000.00", "status": "Contracted", "selection_date": date(2026, 6, 12), "selected_by": "Procurement Officer", "file_no": "OFS/PROC/GEM/2026/001", "sanction_no": "SAN-GEM-2026-001", "sanction_date": date(2026, 6, 14), "authority": "CFO", "remarks": "Catalogue procurement through Government e-Marketplace.", "detail": { "gem_procurement_type": "CATALOGUE", "gem_bid_no": "GEM/2026/B/001", "gem_bid_date": date(2026, 6, 13), "gem_contract_no": "GEMC-2026-001", "gem_contract_date": date(2026, 6, 18), "gem_order_value": "445000.00", "vendor_id": 101, "delivery_status": "In Transit", "payment_status": "Part Paid", "status": "Contracted", }, }, { "case_no": "PC-2026-GEM-002", "quantity": 20, "cost": "650000.00", "status": "In Progress", "selection_date": date(2026, 6, 25), "selected_by": "Stores Procurement Officer", "file_no": "OFS/PROC/GEM/2026/002", "sanction_no": "SAN-GEM-2026-002", "sanction_date": date(2026, 6, 26), "authority": "CFO", "remarks": "GeM bid for breathing apparatus spares.", "detail": { "gem_procurement_type": "BID", "gem_bid_no": "GEM/2026/B/002", "gem_bid_date": date(2026, 6, 27), "gem_order_value": "640000.00", "vendor_id": 102, "delivery_status": "Pending", "payment_status": "Pending", "status": "Bid Published", }, }, { "case_no": "PC-2026-GEM-003", "quantity": 6, "cost": "1250000.00", "status": "Awarded", "selection_date": date(2026, 7, 2), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/GEM/2026/003", "sanction_no": "SAN-GEM-2026-003", "sanction_date": date(2026, 7, 3), "authority": "IG", "remarks": "GeM reverse auction for rescue cutters.", "detail": { "gem_procurement_type": "REVERSE_AUCTION", "gem_bid_no": "GEM/2026/RA/003", "gem_bid_date": date(2026, 7, 4), "gem_contract_no": "GEMC-2026-003", "gem_contract_date": date(2026, 7, 11), "gem_order_value": "1198000.00", "vendor_id": 103, "delivery_status": "Awaiting Dispatch", "payment_status": "Pending", "status": "Awarded", }, }, { "case_no": "PC-2026-GEM-004", "quantity": 4, "cost": "2200000.00", "status": "Technical Evaluation", "selection_date": date(2026, 7, 6), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/GEM/2026/004", "sanction_no": "SAN-GEM-2026-004", "sanction_date": date(2026, 7, 7), "authority": "IG", "remarks": "GeM BOQ procurement for hydraulic rescue kits.", "detail": { "gem_procurement_type": "BOQ", "gem_bid_no": "GEM/2026/BOQ/004", "gem_bid_date": date(2026, 7, 8), "gem_order_value": "0.00", "vendor_id": 104, "delivery_status": "Not Started", "payment_status": "Pending", "status": "Technical Evaluation", }, }, { "case_no": "PC-2026-GEM-005", "quantity": 100, "cost": "300000.00", "status": "Delivered", "selection_date": date(2026, 7, 10), "selected_by": "AFO Stores", "file_no": "OFS/PROC/GEM/2026/005", "sanction_no": "SAN-GEM-2026-005", "sanction_date": date(2026, 7, 11), "authority": "CFO", "remarks": "Catalogue purchase of safety gloves.", "detail": { "gem_procurement_type": "CATALOGUE", "gem_bid_no": "GEM/2026/B/005", "gem_bid_date": date(2026, 7, 12), "gem_contract_no": "GEMC-2026-005", "gem_contract_date": date(2026, 7, 15), "gem_order_value": "292000.00", "vendor_id": 105, "delivery_status": "Delivered", "payment_status": "Pending", "status": "Delivered", }, }, ], "E_TENDER": [ { "case_no": "PC-2026-ET-001", "quantity": 4, "cost": "1800000.00", "status": "Technical Evaluation", "selection_date": date(2026, 6, 15), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/ET/2026/001", "sanction_no": "SAN-ET-2026-001", "sanction_date": date(2026, 6, 16), "authority": "IG", "remarks": "Odisha e-Procurement tender for fire equipment.", "detail": { "tender_ref_no": "OFS/ET/2026/001", "tender_publication_date": date(2026, 6, 17), "pre_bid_meeting_date": date(2026, 6, 22), "bid_submission_end_date": date(2026, 7, 5), "technical_bid_opening_date": date(2026, 7, 6), "financial_bid_opening_date": date(2026, 7, 12), "estimated_cost": "1800000.00", "technical_evaluation_status": "In Progress", "financial_evaluation_status": "Pending", "l1_vendor_id": 202, "status": "Technical Evaluation", }, }, { "case_no": "PC-2026-ET-002", "quantity": 2, "cost": "7800000.00", "status": "Bid Published", "selection_date": date(2026, 6, 28), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/ET/2026/002", "sanction_no": "SAN-ET-2026-002", "sanction_date": date(2026, 6, 29), "authority": "DG", "remarks": "e-Tender for specialized fire tenders.", "detail": { "tender_ref_no": "OFS/ET/2026/002", "tender_publication_date": date(2026, 7, 1), "pre_bid_meeting_date": date(2026, 7, 8), "bid_submission_end_date": date(2026, 7, 25), "technical_bid_opening_date": date(2026, 7, 26), "estimated_cost": "7800000.00", "technical_evaluation_status": "Pending", "financial_evaluation_status": "Pending", "status": "Bid Published", }, }, { "case_no": "PC-2026-ET-003", "quantity": 1, "cost": "2400000.00", "status": "Financial Evaluation", "selection_date": date(2026, 7, 4), "selected_by": "Range Office", "file_no": "OFS/PROC/ET/2026/003", "sanction_no": "SAN-ET-2026-003", "sanction_date": date(2026, 7, 5), "authority": "IG", "remarks": "e-Tender for fire station building repair works.", "detail": { "tender_ref_no": "OFS/ET/2026/003", "tender_publication_date": date(2026, 7, 6), "bid_submission_end_date": date(2026, 7, 20), "technical_bid_opening_date": date(2026, 7, 21), "financial_bid_opening_date": date(2026, 7, 24), "estimated_cost": "2400000.00", "technical_evaluation_status": "Qualified", "financial_evaluation_status": "In Progress", "l1_vendor_id": 203, "status": "Financial Evaluation", }, }, { "case_no": "PC-2026-ET-004", "quantity": 12, "cost": "3600000.00", "status": "LOA Issued", "selection_date": date(2026, 7, 12), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/ET/2026/004", "sanction_no": "SAN-ET-2026-004", "sanction_date": date(2026, 7, 13), "authority": "IG", "remarks": "Rate contract tender for firefighting hoses.", "detail": { "tender_ref_no": "OFS/ET/2026/004", "tender_publication_date": date(2026, 7, 14), "bid_submission_end_date": date(2026, 7, 30), "technical_bid_opening_date": date(2026, 7, 31), "financial_bid_opening_date": date(2026, 8, 4), "estimated_cost": "3600000.00", "technical_evaluation_status": "Qualified", "financial_evaluation_status": "Completed", "l1_vendor_id": 204, "award_approval_date": date(2026, 8, 8), "loa_no": "LOA/ET/2026/004", "loa_date": date(2026, 8, 9), "contract_no": "ET-CON-2026-004", "status": "LOA Issued", }, }, { "case_no": "PC-2026-ET-005", "quantity": 1, "cost": "1100000.00", "status": "Contracted", "selection_date": date(2026, 7, 18), "selected_by": "Circle Office", "file_no": "OFS/PROC/ET/2026/005", "sanction_no": "SAN-ET-2026-005", "sanction_date": date(2026, 7, 19), "authority": "CFO", "remarks": "Services tender for station equipment maintenance.", "detail": { "tender_ref_no": "OFS/ET/2026/005", "tender_publication_date": date(2026, 7, 20), "bid_submission_end_date": date(2026, 8, 5), "technical_bid_opening_date": date(2026, 8, 6), "financial_bid_opening_date": date(2026, 8, 10), "estimated_cost": "1100000.00", "technical_evaluation_status": "Qualified", "financial_evaluation_status": "Completed", "l1_vendor_id": 205, "award_approval_date": date(2026, 8, 14), "loa_no": "LOA/ET/2026/005", "loa_date": date(2026, 8, 15), "contract_no": "ET-CON-2026-005", "status": "Contracted", }, }, ], "GLOBAL_TENDER": [ { "case_no": "PC-2026-GT-001", "quantity": 2, "cost": "12500000.00", "status": "Commercial Evaluation", "selection_date": date(2026, 6, 18), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/GT/2026/001", "sanction_no": "SAN-GT-2026-001", "sanction_date": date(2026, 6, 20), "authority": "DG", "remarks": "International bidding for specialized rescue systems.", "detail": { "global_tender_ref_no": "OFS/GT/2026/001", "country_of_vendor": "Germany", "foreign_vendor_name": "RescueTech GmbH", "foreign_currency": "EUR", "exchange_rate": "91.2500", "bid_value_foreign_currency": "135000.00", "bid_value_inr": "12318750.00", "technical_evaluation_status": "Qualified", "commercial_evaluation_status": "In Progress", "approval_date": date(2026, 7, 18), "contract_no": "GT-CON-2026-001", "status": "Commercial Evaluation", }, }, { "case_no": "PC-2026-GT-002", "quantity": 1, "cost": "22000000.00", "status": "Technical Evaluation", "selection_date": date(2026, 7, 1), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/GT/2026/002", "sanction_no": "SAN-GT-2026-002", "sanction_date": date(2026, 7, 3), "authority": "DG", "remarks": "Global tender for high-reach aerial platform vehicle.", "detail": { "global_tender_ref_no": "OFS/GT/2026/002", "country_of_vendor": "Finland", "foreign_vendor_name": "Nordic Rescue Oy", "foreign_currency": "EUR", "exchange_rate": "91.8000", "bid_value_foreign_currency": "238000.00", "bid_value_inr": "21848400.00", "technical_evaluation_status": "In Progress", "commercial_evaluation_status": "Pending", "approval_date": date(2026, 8, 2), "status": "Technical Evaluation", }, }, { "case_no": "PC-2026-GT-003", "quantity": 3, "cost": "15000000.00", "status": "Award Approval Pending", "selection_date": date(2026, 7, 8), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/GT/2026/003", "sanction_no": "SAN-GT-2026-003", "sanction_date": date(2026, 7, 9), "authority": "DG", "remarks": "International procurement of thermal imaging cameras.", "detail": { "global_tender_ref_no": "OFS/GT/2026/003", "country_of_vendor": "United States", "foreign_vendor_name": "ThermalVision Inc.", "foreign_currency": "USD", "exchange_rate": "83.4000", "bid_value_foreign_currency": "178000.00", "bid_value_inr": "14845200.00", "technical_evaluation_status": "Qualified", "commercial_evaluation_status": "Completed", "approval_date": date(2026, 8, 12), "contract_no": "GT-CON-2026-003", "status": "Award Approval Pending", }, }, { "case_no": "PC-2026-GT-004", "quantity": 2, "cost": "31000000.00", "status": "Contracted", "selection_date": date(2026, 7, 14), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/GT/2026/004", "sanction_no": "SAN-GT-2026-004", "sanction_date": date(2026, 7, 15), "authority": "DG", "remarks": "Imported hazmat response system procurement.", "detail": { "global_tender_ref_no": "OFS/GT/2026/004", "country_of_vendor": "Japan", "foreign_vendor_name": "Kansai Safety Systems", "foreign_currency": "JPY", "exchange_rate": "0.5600", "bid_value_foreign_currency": "54800000.00", "bid_value_inr": "30688000.00", "technical_evaluation_status": "Qualified", "commercial_evaluation_status": "Completed", "approval_date": date(2026, 8, 20), "contract_no": "GT-CON-2026-004", "status": "Contracted", }, }, { "case_no": "PC-2026-GT-005", "quantity": 1, "cost": "18000000.00", "status": "Bid Submission", "selection_date": date(2026, 7, 22), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/GT/2026/005", "sanction_no": "SAN-GT-2026-005", "sanction_date": date(2026, 7, 23), "authority": "DG", "remarks": "Global tender for urban search and rescue technology solution.", "detail": { "global_tender_ref_no": "OFS/GT/2026/005", "country_of_vendor": "United Kingdom", "foreign_vendor_name": "UrbanRescue Solutions Ltd", "foreign_currency": "GBP", "exchange_rate": "106.9000", "bid_value_foreign_currency": "166000.00", "bid_value_inr": "17745400.00", "technical_evaluation_status": "Pending", "commercial_evaluation_status": "Pending", "approval_date": date(2026, 8, 28), "status": "Bid Submission", }, }, ], "LPC": [ { "case_no": "PC-2026-LPC-001", "quantity": 50, "cost": "95000.00", "status": "Approval Pending", "selection_date": date(2026, 6, 19), "selected_by": "AFO", "file_no": "OFS/PROC/LPC/2026/001", "authority": "CFO", "remarks": "Emergency consumables through Local Purchase Committee.", "detail": { "lpc_meeting_no": "LPC/2026/014", "lpc_meeting_date": date(2026, 6, 24), "committee_members": "AFO Stores; Station Officer; Accounts Representative", "quotation_count": 3, "lowest_vendor_id": 303, "comparative_statement_path": "comparatives/lpc-2026-014.pdf", "recommendation_date": date(2026, 6, 25), "approval_date": date(2026, 6, 27), "status": "Approval Pending", }, }, { "case_no": "PC-2026-LPC-002", "quantity": 75, "cost": "120000.00", "status": "Recommended", "selection_date": date(2026, 6, 30), "selected_by": "AFO", "file_no": "OFS/PROC/LPC/2026/002", "authority": "CFO", "remarks": "Urgent purchase of foam compound cans.", "detail": { "lpc_meeting_no": "LPC/2026/015", "lpc_meeting_date": date(2026, 7, 1), "committee_members": "AFO Stores; Station Officer; Senior Assistant", "quotation_count": 4, "lowest_vendor_id": 304, "comparative_statement_path": "comparatives/lpc-2026-015.pdf", "recommendation_date": date(2026, 7, 2), "approval_date": date(2026, 7, 4), "status": "Recommended", }, }, { "case_no": "PC-2026-LPC-003", "quantity": 30, "cost": "85000.00", "status": "Approved", "selection_date": date(2026, 7, 5), "selected_by": "DFO", "file_no": "OFS/PROC/LPC/2026/003", "authority": "CFO", "remarks": "Small value purchase of portable lights.", "detail": { "lpc_meeting_no": "LPC/2026/016", "lpc_meeting_date": date(2026, 7, 6), "committee_members": "DFO; AFO Stores; Accounts Representative", "quotation_count": 3, "lowest_vendor_id": 305, "comparative_statement_path": "comparatives/lpc-2026-016.pdf", "recommendation_date": date(2026, 7, 7), "approval_date": date(2026, 7, 9), "status": "Approved", }, }, { "case_no": "PC-2026-LPC-004", "quantity": 15, "cost": "65000.00", "status": "Completed", "selection_date": date(2026, 7, 11), "selected_by": "Station Officer", "file_no": "OFS/PROC/LPC/2026/004", "authority": "AFO", "remarks": "Emergency replacement of station battery units.", "detail": { "lpc_meeting_no": "LPC/2026/017", "lpc_meeting_date": date(2026, 7, 12), "committee_members": "Station Officer; AFO Stores; Mechanic", "quotation_count": 3, "lowest_vendor_id": 306, "comparative_statement_path": "comparatives/lpc-2026-017.pdf", "recommendation_date": date(2026, 7, 13), "approval_date": date(2026, 7, 14), "status": "Completed", }, }, { "case_no": "PC-2026-LPC-005", "quantity": 40, "cost": "140000.00", "status": "Market Survey", "selection_date": date(2026, 7, 16), "selected_by": "AFO", "file_no": "OFS/PROC/LPC/2026/005", "authority": "CFO", "remarks": "Consumables for monsoon emergency response.", "detail": { "lpc_meeting_no": "LPC/2026/018", "lpc_meeting_date": date(2026, 7, 17), "committee_members": "AFO Stores; Station Officer; Accounts Representative", "quotation_count": 2, "lowest_vendor_id": 307, "comparative_statement_path": "comparatives/lpc-2026-018.pdf", "recommendation_date": date(2026, 7, 18), "approval_date": date(2026, 7, 20), "status": "Market Survey", }, }, ], "OPEN_MARKET": [ { "case_no": "PC-2026-OM-001", "quantity": 8, "cost": "320000.00", "status": "Awarded", "selection_date": date(2026, 6, 21), "selected_by": "DFO", "file_no": "OFS/PROC/OM/2026/001", "authority": "CFO", "remarks": "Open market procurement where GeM/e-Tender is not feasible.", "detail": { "procurement_justification": "Proprietary spare parts unavailable on GeM and unsuitable for e-Tender lead time.", "market_survey_date": date(2026, 6, 23), "quotation_count": 2, "selected_vendor_id": 404, "comparative_statement_path": "comparatives/open-market-2026-001.pdf", "approval_date": date(2026, 6, 26), "status": "Awarded", }, }, { "case_no": "PC-2026-OM-002", "quantity": 12, "cost": "275000.00", "status": "Approved", "selection_date": date(2026, 7, 2), "selected_by": "DFO", "file_no": "OFS/PROC/OM/2026/002", "authority": "CFO", "remarks": "Localized procurement of vehicle-mounted pump accessories.", "detail": { "procurement_justification": "Localized operational requirement with no suitable GeM catalogue match.", "market_survey_date": date(2026, 7, 3), "quotation_count": 3, "selected_vendor_id": 405, "comparative_statement_path": "comparatives/open-market-2026-002.pdf", "approval_date": date(2026, 7, 6), "status": "Approved", }, }, { "case_no": "PC-2026-OM-003", "quantity": 1, "cost": "580000.00", "status": "Market Survey", "selection_date": date(2026, 7, 8), "selected_by": "Range Office", "file_no": "OFS/PROC/OM/2026/003", "authority": "IG", "remarks": "Single-source diagnostic tool for specialized rescue vehicle.", "detail": { "procurement_justification": "Single-source diagnostic interface required for warranty-safe maintenance.", "market_survey_date": date(2026, 7, 9), "quotation_count": 1, "selected_vendor_id": 406, "comparative_statement_path": "comparatives/open-market-2026-003.pdf", "approval_date": date(2026, 7, 12), "status": "Market Survey", }, }, { "case_no": "PC-2026-OM-004", "quantity": 25, "cost": "210000.00", "status": "Completed", "selection_date": date(2026, 7, 13), "selected_by": "AFO", "file_no": "OFS/PROC/OM/2026/004", "authority": "CFO", "remarks": "Operationally urgent repair kits sourced from local approved suppliers.", "detail": { "procurement_justification": "Immediate repair kits required for operational readiness after cyclone deployment.", "market_survey_date": date(2026, 7, 14), "quotation_count": 3, "selected_vendor_id": 407, "comparative_statement_path": "comparatives/open-market-2026-004.pdf", "approval_date": date(2026, 7, 16), "status": "Completed", }, }, { "case_no": "PC-2026-OM-005", "quantity": 6, "cost": "460000.00", "status": "Approval Pending", "selection_date": date(2026, 7, 19), "selected_by": "Circle Office", "file_no": "OFS/PROC/OM/2026/005", "authority": "IG", "remarks": "Open market procurement for specialized operational communication adapters.", "detail": { "procurement_justification": "Special operational adapters are proprietary and not listed on GeM.", "market_survey_date": date(2026, 7, 20), "quotation_count": 2, "selected_vendor_id": 408, "comparative_statement_path": "comparatives/open-market-2026-005.pdf", "approval_date": date(2026, 7, 23), "status": "Approval Pending", }, }, ], "OTHER": [ { "case_no": "PC-2026-OTH-001", "quantity": 1, "cost": "750000.00", "status": "In Progress", "selection_date": date(2026, 6, 22), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/OTH/2026/001", "authority": "IG", "remarks": "Special procurement under approved framework arrangement.", "pbg_applicable": True, "detail": { "other_procurement_type": "Framework Agreement", "procurement_reference_no": "FW/OFS/2026/009", "procurement_description": "Procurement under approved emergency response framework agreement.", "vendor_id": 505, "approval_date": date(2026, 6, 29), "status": "In Progress", }, }, { "case_no": "PC-2026-OTH-002", "quantity": 10, "cost": "900000.00", "status": "Approved", "selection_date": date(2026, 7, 3), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/OTH/2026/002", "authority": "IG", "remarks": "Inter-departmental procurement of training equipment.", "detail": { "other_procurement_type": "Inter-departmental Procurement", "procurement_reference_no": "IDP/OFS/2026/002", "procurement_description": "Training mannequins procured through state training establishment arrangement.", "vendor_id": 506, "approval_date": date(2026, 7, 7), "status": "Approved", }, }, { "case_no": "PC-2026-OTH-003", "quantity": 3, "cost": "2100000.00", "status": "Contracted", "selection_date": date(2026, 7, 9), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/OTH/2026/003", "authority": "DG", "remarks": "PSU procurement for communication systems.", "pbg_applicable": True, "detail": { "other_procurement_type": "PSU Procurement", "procurement_reference_no": "PSU/OFS/2026/003", "procurement_description": "Communication repeaters procured from approved PSU channel.", "vendor_id": 507, "approval_date": date(2026, 7, 13), "status": "Contracted", }, }, { "case_no": "PC-2026-OTH-004", "quantity": 18, "cost": "540000.00", "status": "In Progress", "selection_date": date(2026, 7, 15), "selected_by": "Procurement Officer", "file_no": "OFS/PROC/OTH/2026/004", "authority": "CFO", "remarks": "DGS&D or rate contract procurement of standard store items.", "detail": { "other_procurement_type": "DGS&D / Rate Contract", "procurement_reference_no": "RC/OFS/2026/004", "procurement_description": "Standard store items under approved rate contract.", "vendor_id": 508, "approval_date": date(2026, 7, 18), "status": "In Progress", }, }, { "case_no": "PC-2026-OTH-005", "quantity": 2, "cost": "1600000.00", "status": "Approval Pending", "selection_date": date(2026, 7, 21), "selected_by": "Directorate Procurement Cell", "file_no": "OFS/PROC/OTH/2026/005", "authority": "IG", "remarks": "Future method placeholder for approved pilot procurement.", "detail": { "other_procurement_type": "Future Procurement Method", "procurement_reference_no": "FPM/OFS/2026/005", "procurement_description": "Pilot procurement for emerging operational technology.", "vendor_id": 509, "approval_date": date(2026, 7, 25), "status": "Approval Pending", }, }, ], } MODE_DETAIL_MODELS = { "GEM": GeMProcurementCase, "E_TENDER": ETenderCase, "GLOBAL_TENDER": GlobalTenderCase, "LPC": LPCProcurementCase, "OPEN_MARKET": OpenMarketCase, "OTHER": OtherProcurementCase, } ACTIVE_SAMPLE_MODES = {"GEM", "E_TENDER", "GLOBAL_TENDER", "LPC", "OPEN_MARKET"} MODE_LABELS = { "GEM": "GeM Procurement", "E_TENDER": "Odisha e-Procurement", "GLOBAL_TENDER": "Global Tender", "LPC": "Local Purchase Committee", "OPEN_MARKET": "Open Market", } VENDOR_BY_MODE = { "GEM": "Odisha Safety Equipments", "E_TENDER": "Kalinga Fire Systems", "GLOBAL_TENDER": "International Rescue Systems", "LPC": "Bhubaneswar Emergency Stores", "OPEN_MARKET": "Odisha Operational Supplies", } def _decimal(value: str) -> Decimal: return Decimal(value) def _applicability(mode: str, sample: dict) -> tuple[bool, bool]: if mode in {"E_TENDER", "GLOBAL_TENDER"}: return True, True if mode == "GEM": return bool(sample.get("emd_applicable", False)), True if mode == "LPC": return False, bool(sample.get("pbg_applicable", False)) return bool(sample.get("emd_applicable", False)), bool(sample.get("pbg_applicable", False)) def _sample_item_description(mode: str, index: int) -> str: descriptions = { "GEM": [ "Portable fire pump accessories", "Breathing apparatus spare kit", "Hydraulic rescue cutter", "Hydraulic rescue kit", "Fire safety gloves", ], "E_TENDER": [ "Firefighting equipment package", "Specialized fire tender vehicle", "Fire station repair works", "Firefighting hose rate contract", "Station equipment maintenance service", ], "GLOBAL_TENDER": [ "Specialized rescue system", "High-reach aerial platform vehicle", "Thermal imaging camera", "Hazmat response system", "Urban search and rescue technology solution", ], "LPC": [ "Emergency consumable kit", "Foam compound cans", "Nozzle replacement set", "Vehicle repair consumables", "Monsoon emergency response consumables", ], "OPEN_MARKET": [ "Proprietary pump spare parts", "Vehicle-mounted pump accessories", "Specialized diagnostic tool", "Operational repair kit", "Communication adapter set", ], } return descriptions.get(mode, ["Procurement item"])[index % 5] def _ensure_case_requisition(db, mode: str, sample: dict, index: int) -> Requisition: requisition_no = sample["case_no"].replace("PC-", "REQ-") item_description = _sample_item_description(mode, index) requisition = db.query(Requisition).filter(Requisition.requisition_no == requisition_no).first() if not requisition: requisition = Requisition(requisition_no=requisition_no) db.add(requisition) db.flush() requisition.requisition_date = sample["selection_date"] requisition.requested_by = "Odisha Fire & Emergency Services" requisition.received_from = "Directorate Fire & Emergency Services - Odisha" requisition.received_from_district = "Khordha" requisition.received_from_range = "Central Range" requisition.sender_name = sample["selected_by"] requisition.sender_designation = "Procurement Nodal Officer" requisition.requisition_items = [ { "serial_no": 1, "description": item_description, "item_type": "Equipment", "quantity": sample["quantity"], "unit": "Nos.", "estimated_cost": sample["cost"], } ] requisition.item_description = item_description requisition.quantity = sample["quantity"] requisition.estimated_cost = _decimal(sample["cost"]) requisition.procurement_mode = mode requisition.budget_head = "Fire Safety Equipment and Operational Stores" requisition.administrative_approval_date = sample.get("sanction_date") or sample["selection_date"] requisition.administrative_approval_quantity = sample["quantity"] requisition.lifecycle_stage = "Approved" requisition.approval_status = ProcurementStatus.approved requisition.remarks = f"Seed requisition linked to {sample['case_no']} for cross-verification." return requisition def _add_procurement_mode_samples(db, requisition_id: int) -> int: inserted = 0 for mode, samples in PROCUREMENT_MODE_SAMPLES.items(): if mode not in ACTIVE_SAMPLE_MODES: continue detail_model = MODE_DETAIL_MODELS[mode] for index, sample in enumerate(samples): requisition = _ensure_case_requisition(db, mode, sample, index) case = db.query(ProcurementCase).filter( ProcurementCase.procurement_case_no == sample["case_no"] ).first() if not case: emd_applicable, pbg_applicable = _applicability(mode, sample) case = ProcurementCase( procurement_case_no=sample["case_no"], requisition_id=requisition.id, approved_quantity=sample["quantity"], approved_estimated_cost=_decimal(sample["cost"]), procurement_mode=mode, case_status=sample["status"], mode_selection_date=sample["selection_date"], mode_selected_by=sample["selected_by"], emd_applicable=emd_applicable, pbg_applicable=pbg_applicable, file_no=sample["file_no"], sanction_no=sample.get("sanction_no"), sanction_date=sample.get("sanction_date"), approving_authority=sample["authority"], remarks=sample["remarks"], ) db.add(case) db.flush() inserted += 1 else: case.requisition_id = requisition.id case.approved_quantity = sample["quantity"] case.approved_estimated_cost = _decimal(sample["cost"]) case.procurement_mode = mode if not db.query(detail_model).filter( detail_model.procurement_case_id == case.id ).first(): detail = { key: _decimal(value) if isinstance(value, str) and value.replace(".", "", 1).isdigit() else value for key, value in sample["detail"].items() } db.add(detail_model(procurement_case_id=case.id, **detail)) inserted += 1 return inserted def _ensure_linked_process_samples(db) -> int: inserted = 0 cases = db.query(ProcurementCase).filter( ProcurementCase.procurement_mode.in_(ACTIVE_SAMPLE_MODES) ).order_by(ProcurementCase.procurement_mode, ProcurementCase.procurement_case_no).all() for serial_no, case in enumerate(cases, start=1): requisition = db.get(Requisition, case.requisition_id) if case.requisition_id else None mode = case.procurement_mode case_no = case.procurement_case_no tender_no = case_no.replace("PC-", "TND-") po_no = case_no.replace("PC-", "PO-") vendor_name = VENDOR_BY_MODE.get(mode, "Approved Vendor") item_description = (requisition.item_description if requisition else None) or f"{MODE_LABELS.get(mode, mode)} item" tender_items = [ { "serial_no": 1, "description": item_description, "item_type": "Equipment", "quantity": case.approved_quantity, "unit": "Nos.", } ] tender = db.query(TenderBid).filter(TenderBid.tender_no == tender_no).first() if not tender: tender = TenderBid( serial_no=serial_no, tender_no=tender_no, procurement_case_id=case.id, requisition_id=case.requisition_id, title=f"{case_no} - {item_description}", tender_date=case.mode_selection_date, tender_items=tender_items, item_name=item_description, quantity=case.approved_quantity, estimated_cost=case.approved_estimated_cost, budget_head=requisition.budget_head if requisition else None, bid_published_date=case.mode_selection_date, technical_qualified_bidders=3, l1_bidder_name=vendor_name, contract_number=po_no, contract_date=case.sanction_date, amount=case.approved_estimated_cost, bid_type=MODE_LABELS.get(mode, "Open Tender"), status=ProcurementStatus.in_progress, ) db.add(tender) db.flush() inserted += 1 else: tender.procurement_case_id = case.id tender.requisition_id = case.requisition_id if case.emd_applicable: emd_unique_id = f"EMD-{case_no}" if not db.query(EMD).filter(EMD.emd_unique_id == emd_unique_id).first(): db.add( EMD( tender_id=tender.id, procurement_case_id=case.id, serial_no=serial_no, emd_unique_id=emd_unique_id, entry_date=case.mode_selection_date, tender_number=tender.tender_no, tender_date=tender.tender_date, item_name=item_description, bidder_name=vendor_name, emd_form="BG/FDR", emd_no=f"BG-{serial_no:03d}", emd_date=case.mode_selection_date, amount=(case.approved_estimated_cost or Decimal("0")) * Decimal("0.02"), issuing_branch="State Bank of India, Bhubaneswar", expiry_date=date(2026, 12, 31), refund_status="Pending", ) ) inserted += 1 if not db.query(SampleRegister).filter( SampleRegister.tender_id == tender.id, SampleRegister.bidder_name == vendor_name, ).first(): db.add( SampleRegister( serial_no=serial_no, tender_id=tender.id, procurement_case_id=case.id, tender_number=tender.tender_no, tender_date=tender.tender_date, sample_items=tender_items, item_name=item_description, quantity=1, demonstration_date=case.mode_selection_date, bidder_name=vendor_name, make="OFES Approved", model=f"Model-{serial_no:03d}", make_model=f"OFES Approved Model-{serial_no:03d}", receipt_date=case.mode_selection_date, recipient_name="Technical Evaluation Committee", sample_description=item_description, received_date=case.mode_selection_date, evaluation_status=ProcurementStatus.approved, ) ) inserted += 1 if not db.query(TechnicalEvaluation).filter( TechnicalEvaluation.tender_id == tender.id, TechnicalEvaluation.bidder_name == vendor_name, ).first(): db.add( TechnicalEvaluation( tender_id=tender.id, procurement_case_id=case.id, bidder_name=vendor_name, evaluator="Technical Evaluation Committee", score=82 + (serial_no % 12), result="Qualified", remarks="Auto-linked sample technical evaluation.", ) ) inserted += 1 if not db.query(FinancialEvaluation).filter( FinancialEvaluation.tender_id == tender.id, FinancialEvaluation.bidder_name == vendor_name, ).first(): db.add( FinancialEvaluation( tender_id=tender.id, procurement_case_id=case.id, bidder_name=vendor_name, quoted_amount=case.approved_estimated_cost or Decimal("0"), negotiated_amount=(case.approved_estimated_cost or Decimal("0")) * Decimal("0.98"), rank=1, result="L1", ) ) inserted += 1 po = db.query(PurchaseOrder).filter(PurchaseOrder.po_no == po_no).first() if not po: po = PurchaseOrder( po_no=po_no, tender_id=tender.id, procurement_case_id=case.id, procurement_mode=mode, tender_reference_no=tender.tender_no, tender_reference_date=tender.tender_date, estimated_cost=case.approved_estimated_cost, process_initiate_date=case.mode_selection_date, budget_head=requisition.budget_head if requisition else None, vendor_name=vendor_name, item_description=item_description, quantity=case.approved_quantity, unit="Nos.", po_date=case.sanction_date or case.mode_selection_date, po_value=case.approved_estimated_cost, delivery_due_date=date(2026, 12, 31), status=ProcurementStatus.approved, ) db.add(po) db.flush() inserted += 1 else: po.procurement_case_id = case.id po.tender_id = tender.id if case.pbg_applicable and not db.query(PerformanceSecurity).filter( PerformanceSecurity.po_id == po.id, PerformanceSecurity.security_number == f"PBG-{serial_no:03d}", ).first(): db.add( PerformanceSecurity( po_id=po.id, procurement_case_id=case.id, entry_date=po.po_date, contract_number=po.po_no, contract_date=po.po_date, item_name=item_description, quantity=po.quantity, vendor_name=vendor_name, bidder_name=vendor_name, security_form="BG/FDR", security_number=f"PBG-{serial_no:03d}", security_date=po.po_date, amount=(po.po_value or Decimal("0")) * Decimal("0.10"), issuing_branch="State Bank of India, Bhubaneswar", expiry_date=date(2027, 12, 31), warranty_period="12 months", valid_until=date(2027, 12, 31), release_status="Held", ) ) inserted += 1 if not db.query(DeliveryAcceptance).filter(DeliveryAcceptance.po_id == po.id).first(): db.add( DeliveryAcceptance( po_id=po.id, procurement_case_id=case.id, delivery_date=date(2026, 11, 30), accepted_quantity=po.quantity or 0, acceptance_status=ProcurementStatus.completed, remarks="Auto-linked sample delivery and acceptance.", ) ) inserted += 1 invoice_no = f"INV-{po_no}" if not db.query(PaymentTracking).filter(PaymentTracking.invoice_no == invoice_no).first(): db.add( PaymentTracking( po_id=po.id, procurement_case_id=case.id, invoice_no=invoice_no, invoice_date=date(2026, 12, 5), invoice_amount=po.po_value or Decimal("0"), paid_amount=(po.po_value or Decimal("0")) * Decimal("0.50"), payment_status="Part Paid", ) ) inserted += 1 return inserted def upsert_seed_data(): Base.metadata.create_all(bind=engine) apply_master_format_migrations(engine) db = SessionLocal() try: admin = db.query(User).filter(User.username == "admin").first() if not admin: db.add( User( username="admin", full_name="System Administrator", email="admin@example.local", role=Role.admin, is_active=True, hashed_password=get_password_hash("admin123"), ) ) users = [ ("procurement", "Procurement Officer", "procurement_officer"), ("evaluator", "Technical Evaluator", "evaluator"), ("finance", "Finance User", "finance"), ("viewer", "Read Only Viewer", "viewer"), ] for username, full_name, role in users: if not db.query(User).filter(User.username == username).first(): db.add( User( username=username, full_name=full_name, role=Role(role), is_active=True, hashed_password=get_password_hash("password123"), ) ) db.flush() budget_head_count = upsert_budget_heads(db) item_count = upsert_item_master(db) db.commit() print( "Seed data loaded. " f"Budget heads upserted: {budget_head_count}. " f"Items upserted: {item_count}. " "No dummy lifecycle data inserted. " "Default admin: admin / admin123" ) finally: db.close() if __name__ == "__main__": upsert_seed_data()