-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathseed_extensive_data.sql
More file actions
269 lines (226 loc) · 19.4 KB
/
Copy pathseed_extensive_data.sql
File metadata and controls
269 lines (226 loc) · 19.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
-- =======================================================================
-- Student Loan Planner — Comprehensive Seed Data (V2 Corrected Production)
-- Seeds high-quality, realistic, and diverse course data.
-- Run order:
-- 1. ALTER TABLE statements to support new branches/degrees
-- 2. City inserts
-- 3. Institution inserts (including St. Stephen's & Christ University)
-- 4. Dynamic Course inserts (with MBBS duration cast to 6)
-- =======================================================================
-- =======================================================================
-- STEP 1: Update Database Constraints to Support New Degrees and Branches
-- =======================================================================
-- Update degree_type check constraint
ALTER TABLE courses DROP CONSTRAINT IF EXISTS courses_degree_type_check;
ALTER TABLE courses ADD CONSTRAINT courses_degree_type_check
CHECK (degree_type IN (
'BTech', 'MTech', 'BCA', 'MCA', 'BSc', 'MSc',
'MBBS', 'BDS', 'BPharm', 'Integrated', 'Other',
'BA', 'BCom', 'BBA'
));
-- Update branch check constraint
ALTER TABLE courses DROP CONSTRAINT IF EXISTS courses_branch_check;
ALTER TABLE courses ADD CONSTRAINT courses_branch_check
CHECK (branch IN (
'CS', 'Electronics', 'Mechanical', 'Biotechnology',
'Chemical', 'Civil', 'Electrical', 'Data Science',
'MBBS', 'BDS', 'BAMS', 'BHMS', 'BPharm', 'Nursing',
'BSc Physics', 'BSc Chemistry', 'BSc Maths', 'BSc Biology',
'Integrated MSc', 'BCA', 'Other',
'BA Economics', 'BCom Hons', 'BBA', 'BSc CS'
));
-- =======================================================================
-- STEP 2: Insert New Cities (If not present)
-- =======================================================================
INSERT INTO public.cities (id, name, state, tier) VALUES
('c8a2b5e1-0d33-4f27-a7eb-6238b7d5f812', 'Lucknow', 'Uttar Pradesh', 2)
ON CONFLICT (id) DO NOTHING;
-- =======================================================================
-- STEP 3: Insert Premium Institutions
-- =======================================================================
INSERT INTO public.institutions (id, name, short_name, type, nirf_rank, city_id) VALUES
-- Medical
('a3f5b7d1-0de8-482a-a92c-5b29db48d5b1', 'Maulana Azad Medical College', 'MAMC Delhi', 'State', 10, '036a2605-cc88-43bc-becb-3675a77875f2'),
('b4f6c8e2-1df9-493b-b83c-6c3adb59d6c2', 'King George''s Medical University', 'KGMU Lucknow', 'State', 12, 'c8a2b5e1-0d33-4f27-a7eb-6238b7d5f812'),
('c5a7d9f3-2e0a-4a4c-a94d-7d4bec6ae7d3', 'Kasturba Medical College Manipal', 'KMC Manipal', 'Private-Tier1', 9, 'fdbc1e7e-80e3-4017-9932-c3958ced4e47'),
-- Sciences / Commerce / Arts (Delhi University & Loyola)
('d36f9812-4cf3-485a-8bfe-8f921d283cf2', 'St. Stephen''s College', 'St. Stephen''s', 'State', 14, '036a2605-cc88-43bc-becb-3675a77875f2'),
('e45b871c-3cd8-4b2a-a7df-d93d7c585d82', 'Christ University', 'Christ Bangalore', 'Private-Tier2', 60, '39ae7f5c-1b25-4b6d-a4d9-7292cfbc3ab1'),
('d6b8e0a4-3f1b-4b5d-ba5e-8e5cdf7bf8e4', 'Hindu College', 'Hindu Delhi', 'State', 2, '036a2605-cc88-43bc-becb-3675a77875f2'),
('e7c9f1b5-4f2c-4c6e-cb6f-9f6de88cf9f5', 'Miranda House', 'Miranda House', 'State', 1, '036a2605-cc88-43bc-becb-3675a77875f2'),
('f8da02c6-5f3d-4d7f-dc7f-0a7ef99df0a6', 'Hansraj College', 'Hansraj Delhi', 'State', 12, '036a2605-cc88-43bc-becb-3675a77875f2'),
('a9eb13d7-6f4e-4e8f-ed8f-1b8f0a0ef1b7', 'Lady Shri Ram College for Women', 'LSR Delhi', 'State', 9, '036a2605-cc88-43bc-becb-3675a77875f2'),
('b0fc24e8-7f5f-4f9f-fe9f-2c9f1b1fe2c8', 'Loyola College', 'Loyola Chennai', 'State', 7, '55b4d15d-29ba-4b1c-b198-6897bb91b34e')
ON CONFLICT (name) DO NOTHING;
-- =======================================================================
-- STEP 4: Insert Dynamic Diverse Courses (Using Subquery Lookups)
-- =======================================================================
INSERT INTO public.courses (id, institution_id, name, branch, degree_type, duration_years, tuition_per_year, hostel_per_year, median_salary_lpa, placement_rate_pct, data_year) VALUES
-- -----------------------------------------------------------------------
-- CATEGORY: ENGINEERING (ECE, EEE, Mechanical, Civil, Biotech, Data Science)
-- -----------------------------------------------------------------------
-- IIT Madras
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'IIT Madras' LIMIT 1), 'B.Tech Electronics & Communication', 'Electronics', 'BTech', 4, 200000, 120000, 18.5, 90, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'IIT Madras' LIMIT 1), 'B.Tech Electrical Engineering', 'Electrical', 'BTech', 4, 200000, 120000, 17.2, 88, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'IIT Madras' LIMIT 1), 'B.Tech Mechanical Engineering', 'Mechanical', 'BTech', 4, 200000, 120000, 13.5, 82, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'IIT Madras' LIMIT 1), 'B.Tech Civil Engineering', 'Civil', 'BTech', 4, 200000, 120000, 11.2, 78, 2024),
-- IIT Bombay
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'IIT Bombay' LIMIT 1), 'B.Tech Electrical Engineering', 'Electrical', 'BTech', 4, 230000, 140000, 18.2, 91, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'IIT Bombay' LIMIT 1), 'B.Tech Mechanical Engineering', 'Mechanical', 'BTech', 4, 230000, 140000, 14.5, 85, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'IIT Bombay' LIMIT 1), 'B.Tech Chemical Engineering', 'Chemical', 'BTech', 4, 230000, 140000, 13.8, 83, 2024),
-- BITS Pilani
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'BITS Pilani' LIMIT 1), 'B.E. Electronics & Communication', 'Electronics', 'BTech', 4, 575000, 140000, 15.5, 91, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'BITS Pilani' LIMIT 1), 'B.E. Mechanical Engineering', 'Mechanical', 'BTech', 4, 575000, 140000, 10.2, 80, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'BITS Pilani' LIMIT 1), 'B.E. Biotechnology', 'Biotechnology', 'BTech', 4, 575000, 140000, 8.8, 75, 2024),
-- NIT Trichy
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'NIT Trichy' LIMIT 1), 'B.Tech Electronics & Communication', 'Electronics', 'BTech', 4, 125000, 90000, 13.2, 88, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'NIT Trichy' LIMIT 1), 'B.Tech Electrical & Electronics', 'Electrical', 'BTech', 4, 125000, 90000, 11.5, 85, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'NIT Trichy' LIMIT 1), 'B.Tech Mechanical Engineering', 'Mechanical', 'BTech', 4, 125000, 90000, 9.8, 80, 2024),
-- -----------------------------------------------------------------------
-- CATEGORY: MEDICAL (MBBS, BDS Dentistry, BAMS Ayurveda, B.Sc Nursing)
-- -----------------------------------------------------------------------
-- MAMC Delhi (Govt - Highly subsidized, MBBS duration 6 years)
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'MAMC Delhi' LIMIT 1), 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 4500, 12000, 11.5, 96, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'MAMC Delhi' LIMIT 1), 'Bachelor of Dental Surgery', 'BDS', 'BDS', 5, 4200, 12000, 7.2, 90, 2024),
-- KGMU Lucknow (Govt - MBBS duration 6 years)
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'KGMU Lucknow' LIMIT 1), 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 54600, 15000, 10.2, 94, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'KGMU Lucknow' LIMIT 1), 'Bachelor of Dental Surgery', 'BDS', 'BDS', 5, 48000, 15000, 6.5, 88, 2024),
-- KMC Manipal (Private - MBBS duration 6 years)
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'KMC Manipal' LIMIT 1), 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 1780000, 150000, 9.6, 92, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'KMC Manipal' LIMIT 1), 'Bachelor of Dental Surgery', 'BDS', 'BDS', 5, 450000, 150000, 6.0, 85, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'KMC Manipal' LIMIT 1), 'B.Sc Nursing', 'Nursing', 'BSc', 4, 125000, 120000, 4.2, 88, 2024),
-- AIIMS Delhi (MBBS duration 6 years)
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE name = 'All India Institute of Medical Sciences Delhi' LIMIT 1), 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 1628, 5000, 14.0, 98, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE name = 'All India Institute of Medical Sciences Delhi' LIMIT 1), 'B.Sc (Hons) Nursing', 'Nursing', 'BSc', 4, 1500, 5000, 5.5, 95, 2024),
-- CMC Vellore (MBBS duration 6 years)
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE name = 'Christian Medical College Vellore' LIMIT 1), 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 52000, 80000, 10.5, 95, 2024),
-- -----------------------------------------------------------------------
-- CATEGORY: SCIENCES, COMMERCE & ARTS (Physics, Maths, Chemistry, Econ, BBA, BCom)
-- -----------------------------------------------------------------------
-- Hindu College Delhi
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Hindu Delhi' LIMIT 1), 'B.Sc (Hons) Physics', 'BSc Physics', 'BSc', 3, 24000, 65000, 6.8, 75, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Hindu Delhi' LIMIT 1), 'B.Sc (Hons) Mathematics', 'BSc Maths', 'BSc', 3, 22000, 65000, 7.0, 78, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Hindu Delhi' LIMIT 1), 'B.Sc (Hons) Chemistry', 'BSc Chemistry', 'BSc', 3, 23000, 65000, 6.2, 70, 2024),
-- Miranda House Delhi
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Miranda House' LIMIT 1), 'B.Sc (Hons) Physics', 'BSc Physics', 'BSc', 3, 19000, 60000, 6.5, 72, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Miranda House' LIMIT 1), 'B.Sc (Hons) Mathematics', 'BSc Maths', 'BSc', 3, 17500, 60000, 6.8, 75, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Miranda House' LIMIT 1), 'B.Sc (Hons) Chemistry', 'BSc Chemistry', 'BSc', 3, 18500, 60000, 6.0, 68, 2024),
-- LSR Delhi (Lady Shri Ram - Highly acclaimed placements for Commerce/Economics)
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'LSR Delhi' LIMIT 1), 'B.A. (Hons) Economics', 'BA Economics', 'BA', 3, 22000, 70000, 10.5, 88, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'LSR Delhi' LIMIT 1), 'B.Com (Hons)', 'BCom Hons', 'BCom', 3, 24000, 70000, 9.8, 85, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'LSR Delhi' LIMIT 1), 'B.Sc (Hons) Mathematics', 'BSc Maths', 'BSc', 3, 21000, 70000, 7.5, 80, 2024),
-- St. Stephen's Delhi (Economics & B.A. Courses)
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'St. Stephen''s' LIMIT 1), 'B.A. (Hons) Economics', 'BA Economics', 'BA', 3, 40000, 75000, 11.2, 90, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'St. Stephen''s' LIMIT 1), 'B.Sc (Hons) Physics', 'BSc Physics', 'BSc', 3, 42000, 75000, 7.5, 80, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'St. Stephen''s' LIMIT 1), 'B.Sc (Hons) Mathematics', 'BSc Maths', 'BSc', 3, 40000, 75000, 7.2, 78, 2024),
-- Hansraj College Delhi
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Hansraj Delhi' LIMIT 1), 'B.Sc (Hons) Physics', 'BSc Physics', 'BSc', 3, 22500, 65000, 6.2, 70, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Hansraj Delhi' LIMIT 1), 'B.Sc (Hons) Computer Science', 'BSc CS', 'BSc', 3, 45000, 65000, 8.2, 82, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Hansraj Delhi' LIMIT 1), 'B.Com (Hons)', 'BCom Hons', 'BCom', 3, 21000, 65000, 8.5, 80, 2024),
-- Loyola College Chennai
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Loyola Chennai' LIMIT 1), 'B.Sc (Hons) Mathematics', 'BSc Maths', 'BSc', 3, 18000, 55000, 5.8, 74, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Loyola Chennai' LIMIT 1), 'Bachelor of Business Administration', 'BBA', 'BBA', 3, 48000, 55000, 6.5, 78, 2024),
-- Christ University Bangalore (BBA & Economics)
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Christ Bangalore' LIMIT 1), 'Bachelor of Business Administration', 'BBA', 'BBA', 3, 195000, 95000, 6.8, 85, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Christ Bangalore' LIMIT 1), 'B.A. (Hons) Economics', 'BA Economics', 'BA', 3, 95000, 95000, 6.2, 80, 2024),
(gen_random_uuid(), (SELECT id FROM public.institutions WHERE short_name = 'Christ Bangalore' LIMIT 1), 'Bachelor of Computer Applications', 'BCA', 'BCA', 3, 140000, 95000, 5.8, 85, 2024)
ON CONFLICT ON CONSTRAINT courses_inst_name_unique DO NOTHING;
-- =======================================================================
-- STEP 5: Add More Medical & BSc Institutions
-- =======================================================================
INSERT INTO public.cities (id, name, state, tier) VALUES
('8e3a51f3-1111-4222-b333-123456789abc', 'Puducherry', 'Puducherry', 3),
('8e3a51f3-2222-4222-b333-123456789abc', 'Kolkata', 'West Bengal', 1)
ON CONFLICT (id) DO NOTHING;
-- Let's define the new Medical & BSc institutions
INSERT INTO public.institutions (id, name, short_name, type, nirf_rank, city_id) VALUES
-- New Medical
('8e3a51f3-3333-4222-b333-123456789abc', 'Jawaharlal Institute of Postgraduate Medical Education & Research', 'JIPMER Puducherry', 'State', 5, '8e3a51f3-1111-4222-b333-123456789abc'),
('8e3a51f3-4444-4222-b333-123456789abc', 'Armed Forces Medical College', 'AFMC Pune', 'State', 3, (SELECT id FROM public.cities WHERE name = 'Pune' LIMIT 1)),
('8e3a51f3-5555-4222-b333-123456789abc', 'Grant Government Medical College', 'Grant Medical College', 'State', 15, (SELECT id FROM public.cities WHERE name = 'Mumbai' LIMIT 1)),
('8e3a51f3-6666-4222-b333-123456789abc', 'Madras Medical College', 'MMC Chennai', 'State', 11, (SELECT id FROM public.cities WHERE name = 'Chennai' LIMIT 1)),
-- New BSc/Arts
('8e3a51f3-7777-4222-b333-123456789abc', 'St. Xavier''s College Mumbai', 'St. Xavier''s Mumbai', 'Private-Tier2', 8, (SELECT id FROM public.cities WHERE name = 'Mumbai' LIMIT 1)),
('8e3a51f3-8888-4222-b333-123456789abc', 'Fergusson College Pune', 'Fergusson Pune', 'Private-Tier2', 20, (SELECT id FROM public.cities WHERE name = 'Pune' LIMIT 1)),
('8e3a51f3-9999-4222-b333-123456789abc', 'Presidency College Chennai', 'Presidency Chennai', 'State', 3, (SELECT id FROM public.cities WHERE name = 'Chennai' LIMIT 1)),
('8e3a51f3-aaaa-4222-b333-123456789abc', 'Madras Christian College', 'MCC Chennai', 'Private-Tier2', 15, (SELECT id FROM public.cities WHERE name = 'Chennai' LIMIT 1))
ON CONFLICT (id) DO NOTHING;
-- =======================================================================
-- STEP 6: Insert Courses for New Medical & BSc Institutions
-- =======================================================================
INSERT INTO public.courses (id, institution_id, name, branch, degree_type, duration_years, tuition_per_year, hostel_per_year, median_salary_lpa, placement_rate_pct, data_year) VALUES
-- JIPMER Puducherry (MBBS 6 years)
(gen_random_uuid(), '8e3a51f3-3333-4222-b333-123456789abc', 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 12000, 20000, 12.0, 95, 2024),
(gen_random_uuid(), '8e3a51f3-3333-4222-b333-123456789abc', 'B.Sc Nursing', 'Nursing', 'BSc', 4, 8000, 20000, 5.0, 90, 2024),
-- AFMC Pune
(gen_random_uuid(), '8e3a51f3-4444-4222-b333-123456789abc', 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 0, 0, 15.0, 100, 2024),
-- Grant Medical College Mumbai
(gen_random_uuid(), '8e3a51f3-5555-4222-b333-123456789abc', 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 114000, 150000, 9.5, 92, 2024),
-- Madras Medical College Chennai
(gen_random_uuid(), '8e3a51f3-6666-4222-b333-123456789abc', 'Bachelor of Medicine, Bachelor of Surgery', 'MBBS', 'MBBS', 6, 18000, 120000, 8.5, 90, 2024),
(gen_random_uuid(), '8e3a51f3-6666-4222-b333-123456789abc', 'Bachelor of Pharmacy', 'BPharm', 'BPharm', 4, 15000, 120000, 4.5, 80, 2024),
-- St. Xavier's Mumbai
(gen_random_uuid(), '8e3a51f3-7777-4222-b333-123456789abc', 'B.Sc (Hons) Physics', 'BSc Physics', 'BSc', 3, 35000, 160000, 7.5, 82, 2024),
(gen_random_uuid(), '8e3a51f3-7777-4222-b333-123456789abc', 'B.A. (Hons) Economics', 'BA Economics', 'BA', 3, 30000, 160000, 8.5, 85, 2024),
-- Fergusson College Pune
(gen_random_uuid(), '8e3a51f3-8888-4222-b333-123456789abc', 'B.Sc (Hons) Mathematics', 'BSc Maths', 'BSc', 3, 28000, 110000, 5.5, 75, 2024),
(gen_random_uuid(), '8e3a51f3-8888-4222-b333-123456789abc', 'B.Sc (Hons) Physics', 'BSc Physics', 'BSc', 3, 29000, 110000, 5.0, 70, 2024),
-- Presidency College Chennai
(gen_random_uuid(), '8e3a51f3-9999-4222-b333-123456789abc', 'B.Sc (Hons) Chemistry', 'BSc Chemistry', 'BSc', 3, 8000, 120000, 4.5, 68, 2024),
(gen_random_uuid(), '8e3a51f3-9999-4222-b333-123456789abc', 'B.Sc (Hons) Mathematics', 'BSc Maths', 'BSc', 3, 7500, 120000, 4.8, 70, 2024),
-- MCC Chennai
(gen_random_uuid(), '8e3a51f3-aaaa-4222-b333-123456789abc', 'B.Sc (Hons) Physics', 'BSc Physics', 'BSc', 3, 42000, 120000, 5.5, 75, 2024),
(gen_random_uuid(), '8e3a51f3-aaaa-4222-b333-123456789abc', 'B.Com (Hons)', 'BCom Hons', 'BCom', 3, 45000, 120000, 7.5, 82, 2024)
ON CONFLICT ON CONSTRAINT courses_inst_name_unique DO NOTHING;
-- =======================================================================
-- STEP 7: Dynamically Insert Core Engineering Branches for all B.Tech Colleges
-- =======================================================================
-- We cross join existing institutions (that offer B.Tech) with core branches,
-- and dynamically assign tuition based on their tier, and hostel based on city tier.
INSERT INTO public.courses (
id, institution_id, name, branch, degree_type,
duration_years, tuition_per_year, hostel_per_year,
median_salary_lpa, placement_rate_pct, data_year
)
SELECT
gen_random_uuid(),
i.id,
'B.Tech ' || b.branch_name,
b.branch_code,
'BTech',
4,
CASE
WHEN i.type = 'IIT' THEN 200000
WHEN i.type = 'NIT' THEN 125000
WHEN i.type LIKE 'Private%' THEN 450000
ELSE 150000
END, -- tuition_per_year
CASE
WHEN c.tier = 1 THEN 150000
WHEN c.tier = 2 THEN 110000
ELSE 90000
END, -- hostel_per_year varies by city tier
CASE
WHEN i.type = 'IIT' THEN 14.0
WHEN i.type = 'NIT' THEN 10.0
ELSE 6.5
END, -- median_salary_lpa
CASE
WHEN i.type = 'IIT' THEN 85
WHEN i.type = 'NIT' THEN 80
ELSE 75
END, -- placement_rate_pct
2024
FROM public.institutions i
JOIN public.cities c ON i.city_id = c.id
CROSS JOIN (
VALUES
('Mechanical Engineering', 'Mechanical'),
('Civil Engineering', 'Civil'),
('Electrical Engineering', 'Electrical')
) AS b(branch_name, branch_code)
WHERE i.type IN ('IIT', 'NIT', 'Private-Tier1', 'Private-Tier2', 'State')
AND NOT EXISTS (
SELECT 1 FROM public.courses c_check
WHERE c_check.institution_id = i.id AND c_check.branch = b.branch_code
);