hmm404/WASH_NL2SQL

1schema = """
2TABLE states (
3    state_id INTEGER PRIMARY KEY,
4    lgd_state_id INTEGER NOT NULL,
5    state_name TEXT NOT NULL,
6    census_state INTEGER NOT NULL
7)
8
9TABLE districts (
10    district_id INTEGER PRIMARY KEY,
11    lgd_district_id INTEGER NOT NULL,
12    district_name TEXT NOT NULL,
13    census_district INTEGER NOT NULL
14)
15
16TABLE blocks (
17    block_id INTEGER PRIMARY KEY,
18    lgd_block_id INTEGER NOT NULL,
19    block_name TEXT NOT NULL
20)
21
22TABLE panchayats (
23    panchayat_id INTEGER PRIMARY KEY,
24    lgd_panchayat_id INTEGER NOT NULL,
25    panchayat_name TEXT NOT NULL
26)
27
28TABLE divisions (
29    division_id INTEGER PRIMARY KEY,
30    division_name TEXT NOT NULL
31)
32
33TABLE villages (
34    village_id INTEGER PRIMARY KEY,
35    lgd_village_id INTEGER NOT NULL,
36    village_name TEXT NOT NULL,
37    census_village TEXT NOT NULL,
38    village_type TEXT NOT NULL,
39    village_status TEXT NOT NULL,
40    vap_status TEXT NOT NULL,
41    vwsc_formed INTEGER NOT NULL,
42    village_certificate INTEGER NOT NULL,
43    gp_resolution INTEGER NOT NULL,
44    declaration_video INTEGER NOT NULL,
45    total_no_households INTEGER NOT NULL,
46    total_no_house_connection INTEGER NOT NULL,
47    no_of_ftk_trained_women INTEGER NOT NULL,
48    no_of_school INTEGER NOT NULL,
49    school_with_tap_connection INTEGER NOT NULL,
50    no_of_aws INTEGER NOT NULL,
51    no_of_aws_with_tap_connection INTEGER NOT NULL,
52    total_pop INTEGER NOT NULL,
53    gen_pop INTEGER NOT NULL,
54    sc_pop INTEGER NOT NULL,
55    st_pop INTEGER NOT NULL,
56    sanctioned_approved_status INTEGER,
57    work_order_updated_status INTEGER,
58    scheme_is_work_started_status INTEGER
59)
60
61TABLE habitations (
62    habitation_id INTEGER PRIMARY KEY,
63    habitation_name TEXT NOT NULL,
64    is_pvtg INTEGER NOT NULL,
65    community_access_planned INTEGER NOT NULL,
66    pvtg_fully_partial INTEGER NOT NULL,
67    pvtg_households INTEGER NOT NULL,
68    total_no_households INTEGER NOT NULL,
69    total_no_house_connection INTEGER NOT NULL,
70    is_pvtg_given_by_mota INTEGER NOT NULL
71)
72
73TABLE source_type_categories (
74    source_type_category_id INTEGER PRIMARY KEY,
75    description TEXT NOT NULL
76)
77
78TABLE source_types (
79    source_type_id INTEGER PRIMARY KEY,
80    description TEXT NOT NULL
81)
82
83TABLE storage_structure_types (
84    storage_structure_type_id INTEGER PRIMARY KEY,
85    description TEXT NOT NULL
86)
87
88TABLE categories (
89    category_id INTEGER PRIMARY KEY,
90    description TEXT NOT NULL
91)
92
93TABLE water_sources (
94    source_id INTEGER PRIMARY KEY,
95    location TEXT,
96    source_type_category_id INTEGER,
97    source_type_id INTEGER,
98    response_on TEXT,
99    scheme_id INTEGER,
100    latitude TEXT,
101    longitude TEXT,
102    pws_fhtc_status INTEGER
103)
104
105TABLE schemes (
106    scheme_id INTEGER PRIMARY KEY,
107    scheme_name TEXT,
108    category TEXT,
109    no_of_villages INTEGER,
110    household_planned INTEGER,
111    fhtc_provided INTEGER,
112    is_pws INTEGER,
113    fhtc_scheme TEXT,
114    is_jjm INTEGER,
115    sanction_year TEXT,
116    type TEXT,
117    work_order_date TEXT,
118    status TEXT,
119    physical_progress_in_percentage REAL,
120    handed_over_community_status TEXT,
121    handed_over_community_date TEXT,
122    estimated_cost REAL,
123    csr_donation REAL,
124    om_cost REAL,
125    expenditure REAL,
126    total_central_expenditure REAL,
127    central_expenditure_sc REAL,
128    central_expenditure_st REAL,
129    central_expenditure_gen REAL,
130    total_state_expenditure REAL,
131    state_expenditure_sc REAL,
132    state_expenditure_st REAL,
133    state_expenditure_gen REAL,
134    total_world_bank_expenditure REAL,
135    total_community_expenditure REAL,
136    total_csr_expenditure REAL,
137    total_other_expenditure REAL,
138    total_expenditure_during_JJM REAL,
139    latitude REAL NOT NULL,
140    longitude REAL NOT NULL,
141    location TEXT NOT NULL
142)
143
144TABLE scheme_assets (
145    id INTEGER PRIMARY KEY,
146    habitation_id INTEGER,
147    scheme_id INTEGER,
148    scheme_name TEXT,
149    latitude REAL,
150    longitude REAL,
151    location TEXT,
152    category_id INTEGER,
153    FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id),
154    FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
155    FOREIGN KEY (category_id) REFERENCES categories(category_id)
156)
157
158TABLE district_state_mapping (
159    district_id INTEGER PRIMARY KEY,
160    state_id INTEGER,
161    FOREIGN KEY (district_id) REFERENCES districts(district_id),
162    FOREIGN KEY (state_id) REFERENCES states(state_id)
163)
164
165TABLE block_district_mapping (
166    block_id INTEGER PRIMARY KEY,
167    district_id INTEGER,
168    FOREIGN KEY (block_id) REFERENCES blocks(block_id),
169    FOREIGN KEY (district_id) REFERENCES districts(district_id)
170)
171
172TABLE block_division_mapping (
173    block_id INTEGER PRIMARY KEY,
174    division_id INTEGER,
175    FOREIGN KEY (block_id) REFERENCES blocks(block_id),
176    FOREIGN KEY (division_id) REFERENCES divisions(division_id)
177)
178
179TABLE panchayat_block_mapping (
180    panchayat_id INTEGER PRIMARY KEY,
181    block_id INTEGER,
182    FOREIGN KEY (panchayat_id) REFERENCES panchayats(panchayat_id),
183    FOREIGN KEY (block_id) REFERENCES blocks(block_id)
184)
185
186TABLE village_panchayat_mapping (
187    village_id INTEGER PRIMARY KEY,
188    panchayat_id INTEGER,
189    FOREIGN KEY (village_id) REFERENCES villages(village_id),
190    FOREIGN KEY (panchayat_id) REFERENCES panchayats(panchayat_id)
191)
192
193TABLE habitation_village_mapping (
194    habitation_id INTEGER PRIMARY KEY,
195    village_id INTEGER,
196    FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id),
197    FOREIGN KEY (village_id) REFERENCES villages(village_id)
198)
199
200TABLE source_habitation_mapping (
201    source_id INTEGER PRIMARY KEY,
202    habitation_id INTEGER,
203    FOREIGN KEY (source_id) REFERENCES water_sources(source_id),
204    FOREIGN KEY (habitation_id) REFERENCES habitations(habitation_id)
205)
206
207TABLE scheme_village_mapping (
208    scheme_id INTEGER,
209    village_id INTEGER,
210    PRIMARY KEY (scheme_id, village_id),
211    FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
212    FOREIGN KEY (village_id) REFERENCES villages(village_id)
213)
214
215TABLE scheme_division_mapping (
216    scheme_id INTEGER PRIMARY KEY,
217    division_id INTEGER,
218    FOREIGN KEY (scheme_id) REFERENCES schemes(scheme_id),
219    FOREIGN KEY (division_id) REFERENCES divisions(division_id)
220)
221
222TABLE source_type_source_type_category_mapping (
223    source_type_id INTEGER PRIMARY KEY,
224    source_type_category_id INTEGER,
225    FOREIGN KEY (source_type_id) REFERENCES source_types(source_type_id),
226    FOREIGN KEY (source_type_category_id) REFERENCES source_type_categories(source_type_category_id)
227)
228
229TABLE wtps (
230    wtp_id INTEGER PRIMARY KEY,
231    wtp_name INTEGER NOT NULL
232)
233
234TABLE labs (
235    lab_id INTEGER PRIMARY KEY,
236    lab_name TEXT NOT NULL,
237    lab_type TEXT NOT NULL,
238    lab_group TEXT NOT NULL,
239    latitude REAL,
240    longitude REAL,
241    wtp_id INTEGER NOT NULL,
242    is_in_house INTEGER NOT NULL,
243    FOREIGN KEY (wtp_id) REFERENCES wtps(wtp_id)
244)
245
246TABLE parameters (
247    parameterid INTEGER PRIMARY KEY,
248    parameter_name TEXT NOT NULL,
249    measurement_unit TEXT NOT NULL,
250    acceptable_limit REAL NOT NULL,
251    permissible_limit TEXT NOT NULL,
252    value_type TEXT NOT NULL,
253    value_type_description TEXT NOT NULL,
254    public_rate INTEGER NOT NULL,
255    department_rate INTEGER NOT NULL,
256    commercial_rate INTEGER NOT NULL,
257    test_parameter_type TEXT NOT NULL
258)
259
260TABLE types (
261    type_id INTEGER PRIMARY KEY,
262    type_name TEXT NOT NULL,
263    description TEXT NOT NULL
264)
265
266TABLE wtp_village_mapping (
267    wtp_id INTEGER PRIMARY KEY,
268    village_id INTEGER,
269    FOREIGN KEY (wtp_id) REFERENCES wtps(wtp_id),
270    FOREIGN KEY (village_id) REFERENCES villages(village_id)
271)
272
273TABLE lab_village_mapping (
274    lab_id INTEGER,
275    village_id INTEGER,
276    FOREIGN KEY (lab_id) REFERENCES labs(lab_id),
277    FOREIGN KEY (village_id) REFERENCES villages(village_id)
278)
279"""
280
281
282system_prompt = f"""
283You are a precise SQL query generator assistant working with the database schema below.
284
285Only use the tables and columns explicitly provided in the schema when generating SQL.
286
287Schema definition:
288{schema}
289
290Guidelines:
291- Use the correct primary and foreign key relationships.
292- Do not invent tables or columns not listed in the schema.
293- If the natural language question is ambiguous, make a reasonable assumption about the intent.
294- Output only the final SQL query. Do not add any explanations or commentary.
295
296Instructions: The user question will be provided after this prompt. Write the SQL query that answers it.
297"""
298