Untitled

-- Create table for infrastructure projects cost-benefit analysis
CREATE TABLE IF NOT EXISTS infrastructure_projects (
    project_id INTEGER PRIMARY KEY,
    project_name TEXT NOT NULL,
    project_type TEXT,
    date DATE,
    total_cost_mil_eur NUMERIC(10,2),
    bcr TEXT,
    length_km NUMERIC(10,2),
    status TEXT,
    investment_cost_mil_eur NUMERIC(10,2),
    operating_cost_mil_eur NUMERIC(10,2),
    time_savings_mil_eur NUMERIC(10,2),
    safety_benefits_mil_eur NUMERIC(10,2),
    environmental_benefits_mil_eur NUMERIC(10,2),
    noise_reduction_mil_eur NUMERIC(10,2),
    main_recommendation TEXT,
    priority_level TEXT,
    funding_source TEXT,
    traffic_volume_daily TEXT,
    cargo_share_pct INTEGER,
    problem_addressed TEXT,
    alternatives_considered TEXT,
    key_risks TEXT,
    implementation_start INTEGER,
    implementation_end INTEGER,
    cost_per_km_mil_eur NUMERIC(10,2),
    stage TEXT,
    authority TEXT,
    document_type TEXT,
    price_level_year INTEGER
);

-- Insert project data
INSERT INTO infrastructure_projects VALUES
(1, 'Nákup 25 rušňov a 10 ucelených súprav do diaľkovej dopravy', 'Railway Rolling Stock', '2024-06-01', 479.70, NULL, NULL, 'Planning', 479.70, NULL, NULL, NULL, NULL, NULL, 'Secure replacement for 10 leased locomotives by 2027; condition further procurement on guarantees', 'Medium', 'Unknown', NULL, NULL, 'Aging rolling stock; need to replace 40 old electric locomotives and wagons after lifetime; 10 leased locomotives expiring 2027', 'Purchase vs lease; different locomotive speeds (160 vs 200 km/h)', 'Liberalization impact - vehicle usage guaranteed only until 2030; may lose contracts in public tenders', 2025, 2028, NULL, 'Full', 'ZSSK', 'Feasibility Study', 2024),

(2, 'Modernizácia cesty I/75 Galanta – Nové Zámky', 'Road Modernization', '2019-01-01', 113.00, '1.17-1.22', 40.00, 'Planning', 113.00, NULL, NULL, NULL, NULL, NULL, 'Implement as priority; ensure detailed surveys before Šaľa bypass decision', 'High', 'OPII', '6500-20000', 20, 'Šaľa is 4th highest traffic without bypass; road width below standard (8.5m vs 9.5m minimum)', 'Northern vs Southern Šaľa bypass; Jatov-Tvrdošovce bypass alternatives', 'Traffic survey conducted only 1 day; travel time estimates 100% higher than Google Maps measurements', 2019, 2029, 2.80, 'Phased', 'SSC', 'Feasibility Study', 2018),

(3, 'Nákup rýchlikových vozňov ZSSK', 'Railway Rolling Stock', '2017-05-01', 68.00, NULL, NULL, 'Procurement', 68.00, NULL, NULL, NULL, NULL, NULL, 'Follow value for money principles; compare alternatives including used cars', 'Medium', NULL, NULL, NULL, 'Increase quality of long-distance rail transport; potential to reduce vehicle need by 30-68 through better utilization', 'New purchase vs used wagons vs modernization; speed 160 vs 200 km/h', 'Higher unit prices than comparable EU purchases; uncertainty about future demand', 2017, 2019, NULL, 'Full', 'ZSSK', 'VfM Assessment', 2017),

(4, 'R3 Tvrdošín – Nižná nad Oravou', 'Highway', '2017-06-01', 85.00, '1.37-1.50', 5.20, 'Ready for tender', 85.00, NULL, NULL, NULL, NULL, NULL, 'Implement economically viable project; consider examining alternative route for stage 2', 'Medium', 'State budget', '11000-12000', 20, 'Tvrdošín bypass - 11th most loaded district town on Route I without bypass', 'Full vs half profile; with/without tunnel alternatives', 'Transit shift to D3 after completion may reduce R3 traffic by 13%; model calibration changes increased estimates 26%', 2018, 2023, 16.30, 'Full', 'NDS', 'Feasibility Study', 2017),

(5, 'D1 Bratislava – Senec rozšírenie a rekonštrukcia', 'Motorway Expansion', '2021-05-01', 132.60, NULL, 3.60, 'Preparation', 126.30, NULL, NULL, NULL, NULL, NULL, 'Reduce costs by 6.3M EUR; verify technical solution before tender', 'High', 'State budget', '71000', 12, 'D1/D4 junction completion; state liability to pay 55M EUR annually for D4/R7 availability without junction; D1 capacity increase', 'Different traffic forecasts (variance up to 30%); 4 vs 6 vs 8 lane configurations', 'Three different traffic models produced simultaneously with unexplained discrepancies; proposed tenders below 70% PHZ deemed abnormally low', 2022, 2025, 36.80, '2 stages', 'NDS', 'Feasibility Study Update', 2021),

(6, 'Modernizácia cesty I/68 Sabinov obchvat', 'Road Bypass', '2022-01-01', 61.00, '1.87-1.91', 9.80, 'Ready for tender', 59.70, NULL, NULL, NULL, NULL, NULL, 'Implement socially beneficial project; optimize unit costs to 53.2M EUR', 'High', 'OPII', '11000-12000', 20, 'Sabinov bypass on highly loaded I/68 route; costs reduced 17-19M EUR (22-24%) vs 2019 assessment', 'Different routing options; construction standards', 'Unit price optimization potential 6.5M EUR (11%); need to reconcile public procurement law vs public works law for PHZ', 2022, 2026, 6.20, 'Full', 'SSC', 'Feasibility Study Update', 2021),

(7, 'R4 Severný obchvat Prešova', 'Highway Bypass', '2017-12-01', 424.00, '1.25', 14.50, 'Stage 1 permitted', 424.00, 37.40, 395.80, 13.10, -25.10, 29.70, 'Assess stage 2 benefits vs alternatives; consider eastern bypass option', 'High', 'State budget', '25000', NULL, 'Prešov road capacity insufficient - 3rd largest city; relieve I/18 and I/68 congestion through city', 'Northern vs Eastern bypass; I/68 relocation; junction improvements; public transport enhancement', 'Stage 2 costs not separately analyzed in CBA; unit prices not optimized (35M EUR potential savings); complex Dúbrava junction predetermines stage 2', 2018, 2023, 29.20, '2 stages', 'NDS', 'Feasibility Study', 2017);

-- Create indexes for common queries
CREATE INDEX idx_project_type ON infrastructure_projects(project_type);
CREATE INDEX idx_priority_level ON infrastructure_projects(priority_level);
CREATE INDEX idx_status ON infrastructure_projects(status);
CREATE INDEX idx_authority ON infrastructure_projects(authority);
CREATE INDEX idx_bcr ON infrastructure_projects(bcr);

-- View to show economically viable projects (BCR > 1.0)
CREATE VIEW viable_projects AS
SELECT project_id, project_name, total_cost_mil_eur, bcr, priority_level, status
FROM infrastructure_projects
WHERE bcr IS NOT NULL
ORDER BY priority_level, CAST(SPLIT_PART(bcr, '-', 1) AS NUMERIC) DESC;

-- Example queries to verify data
-- SELECT * FROM infrastructure_projects ORDER BY project_id;
-- SELECT * FROM viable_projects;
-- SELECT project_type, COUNT(*), SUM(total_cost_mil_eur) as total_investment 
-- FROM infrastructure_projects GROUP BY project_type;