schema.py

schema = """
TABLE states (
    state_id INTEGER PRIMARY KEY,
    lgd_state_id INTEGER NOT NULL,
    state_name TEXT NOT NULL,
    census_state INTEGER NOT NULL
)

TABLE districts (
    district_id INTEGER PRIMARY KEY,
    lgd_district_id INTEGER NOT NULL,
    district_name TEXT NOT NULL,
    census_district INTEGER NOT NULL
)

TABLE blocks (
    block_id INTEGER PRIMARY KEY,
    lgd_block_id INTEGER NOT NULL,
    block_name TEXT NOT NULL
)

TABLE panchayats (
    panchayat_id INTEGER PRIMARY KEY,
    lgd_panchayat_id INTEGER NOT NULL,
    panchayat_name TEXT NOT NULL
)

TABLE divisions (
    division_id INTEGER PRIMARY KEY,
    division_name TEXT NOT NULL
)

TABLE villages (
    village_id INTEGER PRIMARY KEY,
    lgd_village_id INTEGER NOT NULL,
    village_name TEXT NOT NULL,
    census_village TEXT NOT NULL,
    village_type TEXT NOT NULL,
    village_status TEXT NOT NULL,
    vap_status TEXT NOT NULL,
    vwsc_formed INTEGER NOT NULL,
    village_certificate INTEGER NOT NULL,
    gp_resolution INTEGER NOT NULL,
    declaration_video INTEGER NOT NULL,
    total_no_households INTEGER NOT NULL,
    total_no_house_connection INTEGER NOT NULL,
    no_of_ftk_trained_women INTEGER NOT NULL,
    no_of_school INTEGER NOT NULL,
    school_with_tap_connection INTEGER NOT NULL,
    no_of_aws INTEGER NOT NULL,
    no_of_aws_with_tap_connection INTEGER NOT NULL,
    total_pop INTEGER NOT NULL,
    gen_pop INTEGER NOT NULL,
    sc_pop INTEGER NOT NULL,
    st_pop INTEGER NOT NULL,
    sanctioned_approved_status INTEGER,
    work_order_updated_status INTEGER,
    scheme_is_work_started_status INTEGER
)

TABLE habitations (
    habitation_id INTEGER PRIMARY KEY,
    habitation_name TEXT NOT NULL,
    is_pvtg INTEGER NOT NULL,
    community_access_planned INTEGER NOT NULL,
    pvtg_fully_partial INTEGER NOT NULL,
    pvtg_households INTEGER NOT NULL,
    total_no_households INTEGER NOT NULL,
    total_no_house_connection INTEGER NOT NULL,
    is_pvtg_given_by_mota INTEGER NOT NULL
)

TABLE source_type_categories (
    source_type_category_id INTEGER PRIMARY KEY,
    description TEXT NOT NULL
)

TABLE source_types (
    source_type_id INTEGER PRIMARY KEY,
    description TEXT NOT NULL
)

TABLE storage_structure_types (
    storage_structure_type_id INTEGER PRIMARY KEY,
    description TEXT NOT NULL
)

TABLE categories (
    category_id INTEGER PRIMARY KEY,
    description TEXT NOT NULL
)

TABLE water_sources (
    source_id INTEGER PRIMARY KEY,
    location TEXT,
    source_type_category_id INTEGER,
    source_type_id INTEGER,
    response_on TEXT,
    scheme_id INTEGER,
    latitude TEXT,
    longitude TEXT,
    pws_fhtc_status INTEGER
)

TABLE schemes (
    scheme_id INTEGER PRIMARY KEY,
    scheme_name TEXT,
    category TEXT,
    no_of_villages INTEGER,
    household_planned INTEGER,
    fhtc_provided INTEGER,
    is_pws INTEGER,
    fhtc_scheme TEXT,
    is_jjm INTEGER,
    sanction_year TEXT,
    type TEXT,
    work_order_date TEXT,
    status TEXT,
    physical_progress_in_percentage REAL,
    handed_over_community_status TEXT,
    handed_over_community_date TEXT,
    estimated_cost REAL,
    csr_donation REAL,
    om_cost REAL,
    expenditure REAL,
    total_central_expenditure REAL,
    central_expenditure_sc REAL,
    central_expenditure_st REAL,
    central_expenditure_gen REAL,
    total_state_expenditure REAL,
    state_expenditure_sc REAL,
    state_expenditure_st REAL,
    state_expenditure_gen REAL,
    total_world_bank_expenditure REAL,
    total_community_expenditure REAL,
    total_csr_expenditure REAL,
    total_other_expenditure REAL,
    total_expenditure_during_JJM REAL,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    location TEXT NOT NULL
)

TABLE scheme_assets (
    id INTEGER PRIMARY KEY,
    habitation_id INTEGER,
    scheme_id INTEGER,
    scheme_name TEXT,
    latitude REAL,
    longitude REAL,
    location TEXT,
    category_id INTEGER,
    FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id),
    FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
)

TABLE district_state_mapping (
    district_id INTEGER PRIMARY KEY,
    state_id INTEGER,
    FOREIGN KEY (district_id) REFERENCES districts(district_id),
    FOREIGN KEY (state_id) REFERENCES states(state_id)
)

TABLE block_district_mapping (
    block_id INTEGER PRIMARY KEY,
    district_id INTEGER,
    FOREIGN KEY (block_id) REFERENCES blocks(block_id),
    FOREIGN KEY (district_id) REFERENCES districts(district_id)
)

TABLE block_division_mapping (
    block_id INTEGER PRIMARY KEY,
    division_id INTEGER,
    FOREIGN KEY (block_id) REFERENCES blocks(block_id),
    FOREIGN KEY (division_id) REFERENCES divisions(division_id)
)

TABLE panchayat_block_mapping (
    panchayat_id INTEGER PRIMARY KEY,
    block_id INTEGER,
    FOREIGN KEY (panchayat_id) REFERENCES panchayats(panchayat_id),
    FOREIGN KEY (block_id) REFERENCES blocks(block_id)
)

TABLE village_panchayat_mapping (
    village_id INTEGER PRIMARY KEY,
    panchayat_id INTEGER,
    FOREIGN KEY (village_id) REFERENCES villages(village_id),
    FOREIGN KEY (panchayat_id) REFERENCES panchayats(panchayat_id)
)

TABLE habitation_village_mapping (
    habitation_id INTEGER PRIMARY KEY,
    village_id INTEGER,
    FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id),
    FOREIGN KEY (village_id) REFERENCES villages(village_id)
)

TABLE source_habitation_mapping (
    source_id INTEGER PRIMARY KEY,
    habitation_id INTEGER,
    FOREIGN KEY (source_id) REFERENCES water_sources(source_id),
    FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id)
)

TABLE scheme_village_mapping (
    scheme_id INTEGER,
    village_id INTEGER,
    PRIMARY KEY (scheme_id, village_id),
    FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
    FOREIGN KEY (village_id) REFERENCES villages(village_id)
)

TABLE scheme_division_mapping (
    scheme_id INTEGER PRIMARY KEY,
    division_id INTEGER,
    FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
    FOREIGN KEY (division_id) REFERENCES divisions(division_id)
)

TABLE source_type_source_type_category_mapping (
    source_type_id INTEGER PRIMARY KEY,
    source_type_category_id INTEGER,
    FOREIGN KEY (source_type_id) REFERENCES source_types(source_type_id),
    FOREIGN KEY (source_type_category_id) REFERENCES source_type_categories(source_type_category_id)
)

TABLE wtps (
    wtp_id INTEGER PRIMARY KEY,
    wtp_name INTEGER NOT NULL
)

TABLE labs (
    lab_id INTEGER PRIMARY KEY,
    lab_name TEXT NOT NULL,
    lab_type TEXT NOT NULL,
    lab_group TEXT NOT NULL,
    latitude REAL,
    longitude REAL,
    wtp_id INTEGER NOT NULL,
    is_in_house INTEGER NOT NULL,
    FOREIGN KEY (wtp_id) REFERENCES wtps(wtp_id)
)

TABLE parameters (
    parameterid INTEGER PRIMARY KEY,
    parameter_name TEXT NOT NULL,
    measurement_unit TEXT NOT NULL,
    acceptable_limit REAL NOT NULL,
    permissible_limit TEXT NOT NULL,
    value_type TEXT NOT NULL,
    value_type_description TEXT NOT NULL,
    public_rate INTEGER NOT NULL,
    department_rate INTEGER NOT NULL,
    commercial_rate INTEGER NOT NULL,
    test_parameter_type TEXT NOT NULL
)

TABLE types (
    type_id INTEGER PRIMARY KEY,
    type_name TEXT NOT NULL,
    description TEXT NOT NULL
)

TABLE wtp_village_mapping (
    wtp_id INTEGER PRIMARY KEY,
    village_id INTEGER,
    FOREIGN KEY (wtp_id) REFERENCES wtps(wtp_id),
    FOREIGN KEY (village_id) REFERENCES villages(village_id)
)

TABLE lab_village_mapping (
    lab_id INTEGER,
    village_id INTEGER,
    FOREIGN KEY (lab_id) REFERENCES labs(lab_id),
    FOREIGN KEY (village_id) REFERENCES villages(village_id)
)
"""


system_prompt = f"""
You are a precise SQL query generator assistant working with the database schema below.

Only use the tables and columns explicitly provided in the schema when generating SQL.

Schema definition:
{schema}

Guidelines:
- Use the correct primary and foreign key relationships.
- Do not invent tables or columns not listed in the schema.
- If the natural language question is ambiguous, make a reasonable assumption about the intent.
- Output only the final SQL query. Do not add any explanations or commentary.

Instructions: The user question will be provided after this prompt. Write the SQL query that answers it.
"""