"""add lookup_value master for admin-managed dropdown lists Revision ID: c7d8e9f0a1b2 Revises: b2c3d4e5f6a8 Create Date: 2026-09-30 12:00:00.000000 Moves dropdown lists that were hard-coded in the frontend into the database. Seeds exactly the values the screens used before, so nothing changes for users; admins can now edit them under Setup > Dropdown Lists. Idempotent: safe to run on a database that already has the table or some rows. """ import json from typing import Sequence, Union from alembic import op import sqlalchemy as sa revision: str = 'c7d8e9f0a1b2' down_revision: Union[str, None] = 'b2c3d4e5f6a8' branch_labels: Union[str, Sequence[str], None] = None depends_on: Union[str, Sequence[str], None] = None # category -> (friendly name, description, [(code, label, meta), ...]) SEED = { 'PRODUCTION_LINE': ('Production lines', 'Line / contractor group a chassis is built on', [ ('A', 'Line A', None), ('B', 'Line B', None), ]), 'AC_MAKE': ('AC makes', 'AC unit manufacturers selectable on the production report', [ ('Haiger', 'Haiger', None), ('Jatck', 'Jatck', None), ('Sparrow', 'Sparrow', None), ('Daga', 'Daga', None), ]), 'INTERIOR_WORK': ('Interior work contractors', 'Who does the interior work on a chassis', [ ('Shell and Core', 'Shell and Core', None), ('Jtack', 'Jtack', None), ('Haiger', 'Haiger', None), ]), 'YARD_LOCATION': ('Yard locations', 'Yards where incoming chassis are parked', [ ('34 Acre Yard', '34 Acre Yard', None), ('Tarpura Yard', 'Tarpura Yard', None), ]), 'MACHINE_TYPE': ('Machine types', 'Maintenance asset types', [ ('CO2_MIG', 'CO2 MIG Machine', None), ('BLOWER', 'Blower', None), ('COMPRESSOR', 'Compressor', None), ('HOIST', 'Hoist', None), ('OVAN', 'Ovan', None), ('SHOWER', 'Shower', None), ('DG_SET', 'DG Set', None), ('VOLTAGE_STABILIZER', 'Voltage Stabilizer', None), ('CONTROL_PANEL', 'Control Panel', None), ('ARC_MACHINE', 'Arc Machine', None), ('PLASMA_CUTTER', 'Plasma Cutter', None), ('IR_LAMP', 'IR Lamp', None), ('DRYER', 'Dryer', None), ('SHEARING_MACHINE', 'Shearing M/C', None), ('DISC_CUTTER', 'Disc Cutter', None), ('ELECTRIC_GRINDER', 'Electric Grinder', None), ('CO2_GAS_PLANT', 'CO2 Gas Plant', None), ('VACUUM_CLEANER', 'Vacuum Cleaner', None), ]), 'INCOMING_MATERIAL_CATEGORY': ('Maintenance material categories', 'Categories for maintenance incoming material entries', [ (c, c, None) for c in ['CO2 Parts', 'Pneumatic Parts', 'Compressor Parts', 'Hoist Parts', 'Blower Parts', 'Ovan Parts', 'DG Parts', 'LPG Gas', 'Electrical Parts', 'General'] ]), 'RENTAL_VENDOR': ('Equipment rental vendors', 'Vendors suggested on equipment rental entries', [ (v, v, None) for v in ['Juneja Hydra', 'Juneja Forklift', 'Mukati Crane', 'Other'] ]), 'RENTAL_EQUIPMENT_TYPE': ('Rental equipment types', 'Equipment types suggested on equipment rental entries', [ (v, v, None) for v in ['Hydra Crane', 'Forklift', 'Mobile Crane', 'JCB', 'Other'] ]), 'QA_VERIFICATION_METHOD': ('QA verification methods', 'How a QA checkpoint is verified', [ ('VISUAL', 'Visual', None), ('MEASUREMENT', 'Measurement', None), ('TORQUE_WRENCH', 'Torque wrench', None), ('TIGHTNESS', 'Tightness', None), ('TEST', 'Test', None), ]), 'PRODUCTION_HOLD_TYPE': ('Production hold types', 'Reasons a chassis can be put on hold on the floor', [ ('QUALITY_HOLD', 'Quality / QA inspection hold', None), ('MATERIAL_HOLD', 'Part / material shortage hold', None), ('CLIENT_HOLD', 'Client request hold', None), ('OPERATIONAL_HOLD', 'Tool / machine breakdown hold', None), ('OTHER', 'Other hold', None), ]), 'PAYMENT_TERMS': ('Vendor payment terms', 'Payment terms offered on a vendor record', [ ('Immediate', 'Immediate (COD)', None), ('50% Advance', '50% Advance', None), ('100% Advance', '100% Advance', None), ('Net 15', 'Net 15', None), ('Net 30', 'Net 30', None), ('Net 45', 'Net 45', None), ('Net 60', 'Net 60', None), ('Net 90', 'Net 90', None), ('30% Advance, 70% on Delivery', '30% Advance, 70% on Delivery', None), ('LC (Letter of Credit)', 'LC (Letter of Credit)', None), ]), 'REGION': ('Regions', 'Sales region of a dealer or customer', [ ('NORTH', 'North', None), ('SOUTH', 'South', None), ('EAST', 'East', None), ('WEST', 'West', None), ('CENTRAL', 'Central', None), ]), 'ENQUIRY_TYPE': ('Enquiry types', 'What a sales enquiry is about', [ ('SALES', 'Sales / New Bus', None), ('SERVICE', 'Service & Repair', None), ('SPARE_PARTS', 'Spare Parts', None), ('OTHER', 'Other', None), ]), 'ENQUIRY_SOURCE': ('Enquiry sources', 'Where a sales enquiry came from', [ ('WEBSITE', 'Website', None), ('CALL', 'Phone Call', None), ('WALK_IN', 'Walk In', None), ('REFERRAL', 'Referral', None), ('OTHER', 'Other', None), ]), 'CHASSIS_DOCUMENT_TYPE': ('Chassis document types', 'Documents that can be uploaded against a chassis', [ ('TAX_INVOICE', 'Tax Invoice', None), ('FORM_19', 'Form 19', None), ('FORM_22A', 'Form 22A', None), ('FORM_22', 'Form 22', None), ('FORM_F', 'Form F', None), ('PACKING_SLIP_POD', 'Packing Slip/POD', None), ('OTHER', 'Other', None), ('SERVICE_COUPON', 'Service Coupon', None), ]), 'GATE_PASS_ITEM': ('Gate pass items', 'Loose items handed over with a vehicle at dispatch', [ ('clam_box', 'Clam', {'default_qty': 1, 'row': 1}), ('stephony', 'Stephony', {'default_qty': 1, 'row': 1}), ('jack', 'Jack', {'default_qty': 1, 'row': 2}), ('toolkit', 'Toolkit', {'default_qty': 1, 'row': 2}), ('urea_key', 'Urea Key', {'default_qty': 1, 'row': 3}), ('ignition_key', 'Ignition Key', {'default_qty': 2, 'row': 3}), ]), 'THERMAL_CHECK_ITEM': ('Thermal checksheet items', 'Points on the pre-dispatch thermal checksheet', [ (str(i + 1), t, None) for i, t in enumerate([ "बैटरी कट-ऑफ कनेक्शन ठीक है", "बैटरी केबल स्थिति & कसाव जाँच करें", "बैटरी अर्थिंग की कसावट जांच करें", "बैटरी कट-ऑफ फ्यूज", "Battery cut-off स्विच लगा हुआ", "ABS स्विच लगा हुआ है", "DPF, ECO, ECO+ स्विच लगा हुआ है", "कूलेंट स्तर की जाँच ठीक है", "स्टीयरिंग तेल का स्तर अवश्य जांचें", "क्लच तेल स्तर की जाँच करें", "रेडिएटर / कूलेंट रिसाव जाँच करें", "यूरिया स्तर की जांच ठीक है", "यूरिया कैप लगा हुआ है", "क्लस्टर मीटर काम कर रहा है", "वाहन स्टार्ट करने में समस्या", "बैटरी वोल्टेज में वृद्धि", "Active Error / CHECK / MIL / STOP मीटर पर प्रतीक दिखाई दे रहा है", "EATS हार्नेस को हिट ज़ोन को नहीं छूना चाहिए", "T4, T5, T6 सेंसर क्षति की जाँच करें", "वायु रिसाव की जाँच ठीक है", "बेक बूस्टर बोल्ट कसने की जाँच करें", "बेक ठीक से काम कर रहा है", "क्लच ठीक से काम कर रहा है", "गियर ठीक से काम कर रहा है", "एक्सीलरेटर ठीक से काम कर रहा है", "हॉर्न काम कर रहा है", ]) ]), } def upgrade() -> None: conn = op.get_bind() conn.execute(sa.text(""" CREATE TABLE IF NOT EXISTS lookup_value ( lookup_id UUID PRIMARY KEY DEFAULT gen_random_uuid(), category VARCHAR(60) NOT NULL, code VARCHAR(120) NOT NULL, label VARCHAR(300) NOT NULL, sort_order INTEGER NOT NULL DEFAULT 0, meta JSONB, is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at TIMESTAMPTZ DEFAULT now(), updated_at TIMESTAMPTZ DEFAULT now(), CONSTRAINT uq_lookup_value_category_code UNIQUE (category, code) ) """)) conn.execute(sa.text("CREATE INDEX IF NOT EXISTS ix_lookup_value_category ON lookup_value (category)")) insert = sa.text(""" INSERT INTO lookup_value (lookup_id, category, code, label, sort_order, meta, is_active) VALUES (gen_random_uuid(), :category, :code, :label, :sort_order, CAST(:meta AS JSONB), TRUE) ON CONFLICT (category, code) DO NOTHING """) for cat_idx, (category, (name, description, values)) in enumerate(SEED.items()): # Each category also describes itself, so the admin screen needs no hard-coded names. conn.execute(insert, { 'category': 'LOOKUP_CATEGORY', 'code': category, 'label': name, 'sort_order': cat_idx + 1, 'meta': json.dumps({'description': description}), }) for idx, (code, label, meta) in enumerate(values): conn.execute(insert, { 'category': category, 'code': code, 'label': label, 'sort_order': idx + 1, 'meta': json.dumps(meta) if meta else None, }) def downgrade() -> None: op.execute("DROP TABLE IF EXISTS lookup_value")