CREATE TABLE Prefectures ( -- equivalent of provinces / states
    prefecture_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    region TEXT
);

CREATE TABLE Islands ( -- Japan is an archipelago, so rail service is somewhat
                       -- divided up between island regions (Hokkaido, Kyushu, etc.)
    island_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    prefecture_id INTEGER,
    FOREIGN KEY (prefecture_id) REFERENCES Prefectures(prefecture_id)
);

CREATE TABLE Cities (
    city_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    prefecture_id INTEGER,
    island_id INTEGER,
    population INTEGER,
    median_age FLOAT,
    FOREIGN KEY (prefecture_id) REFERENCES Prefectures(prefecture_id),
    FOREIGN KEY (island_id) REFERENCES Islands(island_id)
);

CREATE TABLE Stations ( -- Stores individual station details independent of their line associations
    station_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    city_id INTEGER,
    is_bullet_train_station BOOLEAN,
    FOREIGN KEY (city_id) REFERENCES Cities(city_id)
);

CREATE TABLE RailLines (
    line_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    type TEXT CHECK (type IN ('JR', 'Subway', 'Shinkansen')),
    operator TEXT, -- Rail companies such as "JR East", "Tokyo Metro", or "Hokkaido Railway Company", etc.
    start_station_id INTEGER,
    end_station_id INTEGER,
    FOREIGN KEY (start_station_id) REFERENCES Stations(station_id),
    FOREIGN KEY (end_station_id) REFERENCES Stations(station_id)
);

CREATE TABLE LineStations ( -- Maps stations to specific rail lines and defines their sequence on each line,
                            -- mainly for routing and scheduling purposes
    line_id INTEGER,
    station_id INTEGER,
    station_order INTEGER,
    PRIMARY KEY (line_id, station_order),
    FOREIGN KEY (line_id) REFERENCES RailLines(line_id),
    FOREIGN KEY (station_id) REFERENCES Stations(station_id)
);

CREATE TABLE StationMaintenance ( -- historical accounting of station maintenance
    maintenance_id INTEGER PRIMARY KEY AUTOINCREMENT,
    station_id INTEGER,
    maintenance_date DATE,
    cost REAL,
    description TEXT,
    FOREIGN KEY (station_id) REFERENCES Stations(station_id)
);

CREATE TABLE TrainServices (
    service_id INTEGER PRIMARY KEY AUTOINCREMENT,
    line_id INTEGER,
    name TEXT,
    frequency TEXT,
    start_time TIME,
    end_time TIME,
    FOREIGN KEY (line_id) REFERENCES RailLines(line_id)
);

CREATE TABLE TrainCars (
    car_id INTEGER PRIMARY KEY AUTOINCREMENT,
    service_id INTEGER,
    car_number INTEGER,
    capacity INTEGER,
    type TEXT,
    FOREIGN KEY (service_id) REFERENCES TrainServices(service_id)
);

-- daily foot passenger information at each station
CREATE TABLE PassengerTraffic (
    station_id INTEGER,
    date DATETIME,
    total_entrances INTEGER,
    total_exits INTEGER,
    FOREIGN KEY (station_id) REFERENCES Stations(station_id)
);

INSERT INTO Prefectures (name, region) VALUES 
('Tokyo', 'Kanto'),
('Kanagawa', 'Kanto'),
('Osaka', 'Kansai'),
('Hokkaido', 'Hokkaido'),
('Fukuoka', 'Kyushu'),
('Kyoto', 'Kansai'),
('Aichi', 'Chubu'),
('Hiroshima', 'Chugoku'),
('Miyagi', 'Tohoku'),
('Hyogo', 'Kansai');

INSERT INTO Islands (name, prefecture_id) VALUES 
('Honshu', 1),
('Honshu', 2),
('Honshu', 3),
('Hokkaido', 4),
('Kyushu', 5),
('Kyushu', 6),
('Honshu', 7),
('Shikoku', 8),
('Honshu', 9),
('Honshu', 10);

INSERT INTO Cities (name, prefecture_id, island_id, population, median_age) VALUES 
('Tokyo', 1, 1, 9744000, 43.3),
('Yokohama', 2, 1, 3726000, 42.9),
('Osaka', 3, 2, 2709000, 43.9),
('Sapporo', 4, 3, 1982000, 46.2),
('Fukuoka', 5, 4, 1589000, 43.7),
('Kyoto', 6, 2, 1459000, 48.9),
('Nagoya', 7, 5, 2325000, 42.6),
('Hiroshima', 8, 6, 1199000, 44.8),
('Sendai', 9, 7, 1063000, 42.1),
('Kawasaki', 10, 1, 1518000, 41.8);

INSERT INTO Stations (name, city_id, is_bullet_train_station) VALUES 
('Tokyo Station', 1, true),
('Shinagawa Station', 1, true),
('Shinjuku Station', 1, false),
('Ikebukuro Station', 10, false),
('Yokohama Station', 2, false),
('Osaka Station', 3, true),
('Umeda Station', 3, false),
('Sapporo Station', 4, true),
('Fukuoka Station', 5, true),
('Hakata Station', 5, false),
('Kyoto Station', 6, true),
('Nagoya Station', 7, true),
('Hiroshima Station', 8, true),
('Sendai Station', 9, true),
('Kawasaki Station', 10, false),
('Shibuya Station', 1, false),
('Shin-Osaka Station', 3, true),
('Sapporo Subway Station', 4, false),
('Odori Station', 4, false),
('Fukuoka Airport Station', 5, false),
('Tenjin Station', 5, false),
('Namba Station', 3, false),
('Umeda Subway Station', 3, false),
('Sapporo JR Station', 4, true),
('Susukino Station', 4, false),
('Hakata JR Station', 5, true),
('Tenjin Subway Station', 5, false),
('Kyoto Subway Station', 6, false),
('Gion Station', 6, false),
('Nagoya JR Station', 7, true),
('Sakae Station', 7, false),
('Hiroshima JR Station', 8, true),
('Miyajima Station', 8, false),
('Sendai JR Station', 9, true),
('Ichibancho Station', 9, false),
('Kawasaki JR Station', 10, false),
('Musashi-Kosugi Station', 10, false),
('Tokyo Disney Station', 1, false),
('Shiodome Station', 1, false),
('Roppongi Station', 1, false),
('Ueno Station', 1, false),
('Shibuya JR Station', 1, true),
('Shin-Kiba Station', 1, false),
('Sapporo Shinkansen Station', 4, true),
('New Chitose Airport Station', 4, true),
('Fukuoka Shinkansen Station', 5, true),
('Dazaifu Station', 5, false),
('Osaka Namba Station', 3, false),
('Kita-Osaka Station', 3, false);

INSERT INTO RailLines (name, type, operator, start_station_id, end_station_id) VALUES 
('Tokaido Shinkansen', 'Shinkansen', 'JR Central', 1, 7),
('Sanyo Shinkansen', 'Shinkansen', 'JR West', 7, 5),
('Tohoku Shinkansen', 'Shinkansen', 'JR East', 1, 9),
('Hokkaido Shinkansen', 'Shinkansen', 'JR Hokkaido', 4, 17),
('Tokyo Loop Line', 'JR', 'JR East', 1, 1),
('Osaka Loop Line', 'JR', 'JR West', 3, 3),
('Sapporo Subway Nanboku Line', 'Subway', 'Sapporo City Transportation Bureau', 16, 18),
('Fukuoka Subway Kuko Line', 'Subway', 'Fukuoka City Transportation Bureau', 24, 25),
('Kyoto Subway Karasuma Line', 'Subway', 'Kyoto Municipal Transportation Bureau', 27, 28),
('Nagoya Municipal Subway Higashiyama Line', 'Subway', 'Transportation Bureau City of Nagoya', 31, 32),
('Hiroshima Streetcar Ujina Line', 'Subway', 'Hiroshima Electric Railway', 34, 35),
('Sendai Subway Nanboku Line', 'Subway', 'Sendai City Transportation Bureau', 37, 38),
('Yokohama Municipal Subway Blue Line', 'Subway', 'Transportation Bureau City of Yokohama', 2, 40),
('Tokyo Metro Ginza Line', 'Subway', 'Tokyo Metro', 1, 41);

INSERT INTO LineStations (line_id, station_id, station_order) VALUES
(1, 1, 1),
(1, 2, 2),
(1, 7, 3),
(2, 7, 1),
(2, 6, 2),
(2, 5, 3),
(3, 1, 1),
(3, 14, 2),
(3, 9, 3),
(4, 4, 1),
(4, 17, 2),
(4, 39, 3),
(5, 1, 1),
(5, 13, 2),
(5, 12, 3),
(5, 36, 4),
(5, 1, 5),
(6, 3, 1),
(6, 7, 2),
(6, 21, 3),
(6, 3, 4),
(7, 16, 1),
(7, 18, 2),
(7, 19, 3),
(7, 20, 4),
(8, 24, 1),
(8, 25, 2),
(8, 26, 3),
(9, 27, 1),
(9, 28, 2),
(10, 31, 1),
(10, 32, 2),
(11, 34, 1),
(11, 35, 2),
(12, 37, 1),
(12, 38, 2),
(13, 2, 1),
(13, 40, 2),
(14, 1, 1),
(14, 41, 2);

INSERT INTO StationMaintenance (station_id, maintenance_date, cost, description) VALUES
(1, '2023-04-10', 50000.0, 'Repaired lighting system in the main concourse.'),
(1, '2023-03-20', 7500.0, 'Concourse floor polishing and cleaning.'),
(1, '2023-02-15', 15000.0, 'Upgraded security cameras in the parking area.'),
(2, '2023-04-05', 30000.0, 'Repainted station platforms and refreshed signage.'),
(2, '2023-03-18', 2500.0, 'Restroom renovations and plumbing repairs.'),
(3, '2023-04-12', 40000.0, 'Replaced damaged roof panels over the east entrance.'),
(3, '2023-03-05', 18000.0, 'Upgraded platform displays for better visibility.'),
(3, '2023-02-20', 6000.0, 'Deep cleaning and sanitation of the entire station.'),
(4, '2023-04-08', 20000.0, 'Repaired escalators and moving walkways.'),
(4, '2023-03-10', 12000.0, 'Installed new ticket gates for faster entry/exit.'),
(3, '2023-04-01', 35000.0, 'Refurbished waiting area seating and added charging ports.'),
(3, '2023-03-25', 9000.0, 'Exterior wall painting and weatherproofing.'),
(4, '2023-04-11', 15000.0, 'Improved station lighting and energy efficiency.'),
(4, '2023-03-15', 7000.0, 'Repaired heating system for winter months.'),
(5, '2023-04-03', 25000.0, 'Upgraded platform displays with real-time train info.'),
(5, '2023-03-08', 10000.0, 'Enhanced station security with additional cameras.'),
(6, '2023-04-06', 18000.0, 'Refurbished ticket counters and added self-service kiosks.'),
(6, '2023-03-22', 5000.0, 'Deep cleaning and sanitation of restrooms.'),
(7, '2023-04-09', 32000.0, 'Repaired water damage and leaks in the concourse.'),
(7, '2023-03-14', 8000.0, 'Improved accessibility with new ramps and signage.'),
(8, '2023-04-02', 28000.0, 'Installed energy-efficient lighting throughout the station.'),
(8, '2023-03-28', 12000.0, 'Refurbished waiting area with new seating and amenities.');

INSERT INTO TrainServices (line_id, name, frequency, start_time, end_time) VALUES
(1, 'Hikari', 'Every 30 minutes', '06:00:00', '23:00:00'),
(1, 'Kodama', 'Hourly', '05:00:00', '24:00:00'),
(2, 'Nozomi', 'Every 20 minutes', '05:30:00', '23:30:00'),
(2, 'Hikari Rail Star', 'Every 40 minutes', '06:00:00', '22:00:00'),
(3, 'Hayabusa', 'Every 25 minutes', '06:00:00', '23:30:00'),
(3, 'Yamabiko', 'Every hour', '05:00:00', '00:00:00'),
(4, 'Hayabusa', 'Every hour', '07:00:00', '21:00:00'),
(4, 'Hokuto', 'Every 90 minutes', '06:30:00', '22:30:00'),
(5, 'Inner Loop Express', 'Every 15 minutes', '06:00:00', '23:00:00'),
(5, 'Outer Loop Local', 'Every 10 minutes', '05:30:00', '00:30:00'),
(6, 'Outer Loop Express', 'Every 20 minutes', '06:30:00', '22:30:00'),
(6, 'Inner Loop Local', 'Every 15 minutes', '05:30:00', '23:30:00'),
(7, 'Northbound Train', 'Every 10 minutes', '06:00:00', '23:00:00'),
(7, 'Southbound Train', 'Every 10 minutes', '05:30:00', '22:30:00'),
(8, 'Airport Express', 'Every 15 minutes', '06:00:00', '22:00:00'),
(8, 'Local Train', 'Every 10 minutes', '05:30:00', '23:30:00'),
(9, 'North-South Express', 'Every 15 minutes', '06:30:00', '22:30:00'),
(9, 'Local Train', 'Every 10 minutes', '05:30:00', '23:30:00'),
(10, 'Higashiyama Express', 'Every 20 minutes', '06:00:00', '22:00:00'),
(10, 'Local Train', 'Every 10 minutes', '05:30:00', '23:30:00'),
(11, 'Ujina Line Streetcar', 'Every 15 minutes', '07:00:00', '21:00:00'),
(12, 'North-South Train', 'Every 12 minutes', '06:30:00', '22:30:00'),
(13, 'Blue Line Express', 'Every 20 minutes', '06:00:00', '23:00:00'),
(13, 'Local Train', 'Every 10 minutes', '05:30:00', '00:30:00'),
(14, 'Ginza Line Express', 'Every 15 minutes', '06:00:00', '23:30:00'),
(14, 'Local Train', 'Every 10 minutes', '05:30:00', '00:00:00');

INSERT INTO TrainCars (service_id, car_number, capacity, type) VALUES
(1, 1, 100, 'Standard'),
(1, 2, 80, 'Green Car'),
(1, 3, 100, 'Standard'),
(1, 4, 100, 'Standard'),
(1, 5, 80, 'Green Car'),
(1, 6, 100, 'Standard'),
(2, 1, 100, 'Standard'),
(2, 2, 100, 'Standard'),
(2, 3, 80, 'Green Car'),
(2, 4, 100, 'Standard'),
(3, 1, 100, 'Standard'),
(3, 2, 80, 'Green Car'),
(3, 3, 100, 'Standard'),
(3, 4, 120, 'Double-decker'),
(3, 5, 80, 'Green Car'),
(3, 6, 100, 'Standard'),
(4, 1, 100, 'Standard'),
(4, 2, 80, 'Green Car'),
(4, 3, 100, 'Standard'),
(5, 1, 100, 'Standard'),
(5, 2, 80, 'Green Car'),
(5, 3, 100, 'Standard'),
(5, 4, 120, 'Double-decker'),
(5, 5, 80, 'Green Car'),
(5, 6, 100, 'Standard'),
(6, 1, 100, 'Standard'),
(6, 2, 100, 'Standard'),
(6, 3, 80, 'Green Car'),
(7, 1, 100, 'Standard'),
(7, 2, 80, 'Green Car'),
(7, 3, 100, 'Standard'),
(8, 1, 100, 'Standard'),
(8, 2, 80, 'Green Car'),
(9, 1, 120, 'Commuter'),
(9, 2, 120, 'Commuter'),
(9, 3, 120, 'Commuter'),
(10, 1, 120, 'Commuter'),
(10, 2, 120, 'Commuter'),
(10, 3, 120, 'Commuter'),
(10, 4, 120, 'Commuter'),
(11, 1, 120, 'Commuter'),
(11, 2, 120, 'Commuter'),
(12, 1, 120, 'Commuter'),
(12, 2, 120, 'Commuter'),
(12, 3, 120, 'Commuter'),
(13, 1, 80, 'Subway'),
(13, 2, 80, 'Subway'),
(14, 1, 80, 'Subway'),
(14, 2, 80, 'Subway'),
(15, 1, 80, 'Subway'),
(15, 2, 80, 'Subway'),
(16, 1, 80, 'Subway'),
(16, 2, 80, 'Subway'),
(17, 1, 80, 'Subway'),
(17, 2, 80, 'Subway'),
(18, 1, 80, 'Subway'),
(18, 2, 80, 'Subway'),
(19, 1, 100, 'Subway'),
(19, 2, 100, 'Subway'),
(20, 1, 100, 'Subway'),
(20, 2, 100, 'Subway'),
(21, 1, 50, 'Streetcar'),
(22, 1, 80, 'Subway'),
(22, 2, 80, 'Subway'),
(23, 1, 100, 'Subway'),
(23, 2, 100, 'Subway'),
(24, 1, 100, 'Subway'),
(24, 2, 100, 'Subway'),
(25, 1, 100, 'Subway'),
(25, 2, 100, 'Subway'),
(26, 1, 100, 'Subway'),
(26, 2, 100, 'Subway');

INSERT INTO PassengerTraffic (station_id, date, total_entrances, total_exits) VALUES
-- tokyo
(1, '2023-04-10 08:00:00', 50000, 48000),
(1, '2023-04-11 08:00:00', 52000, 50500),
(1, '2023-04-12 08:00:00', 49500, 47800),
-- shinagawa
(2, '2023-04-10 09:00:00', 35000, 34200),
(2, '2023-04-11 09:00:00', 36500, 35800),
(2, '2023-04-12 09:00:00', 34800, 34000),
-- shinjuku
(3, '2023-04-10 10:00:00', 60000, 58500),
(3, '2023-04-11 10:00:00', 62500, 61000),
(3, '2023-04-12 10:00:00', 59000, 57800),
-- yokohama
(4, '2023-04-10 11:00:00', 42000, 40500),
(4, '2023-04-11 11:00:00', 44000, 42800),
(4, '2023-04-12 11:00:00', 41500, 40000),
-- osaka
(7, '2023-04-10 12:00:00', 45000, 43800),
(7, '2023-04-11 12:00:00', 47500, 46200),
(7, '2023-04-12 12:00:00', 46000, 44500),
-- sapporo
(16, '2023-04-10 13:00:00', 25000, 24000),
(16, '2023-04-11 13:00:00', 26500, 25800),
(16, '2023-04-12 13:00:00', 25500, 24800),
-- fukuoka
(5, '2023-04-10 14:00:00', 30000, 29000),
(5, '2023-04-11 14:00:00', 32000, 31200),
(5, '2023-04-12 14:00:00', 30500, 29800),
-- shibuya
(15, '2023-04-10 15:00:00', 40000, 38500),
(15, '2023-04-11 15:00:00', 42000, 40800),
(15, '2023-04-12 15:00:00', 39500, 38000),
-- kyoto
(6, '2023-04-10 16:00:00', 35000, 34000),
(6, '2023-04-11 16:00:00', 37000, 35800),
(6, '2023-04-12 16:00:00', 36000, 35000),
-- nagoya
(8, '2023-04-10 17:00:00', 40000, 39000),
(8, '2023-04-11 17:00:00', 42000, 40800),
(8, '2023-04-12 17:00:00', 41000, 39800);

-- Avg Daily Traffic
-- SELECT
--     s.name AS station_name,
--     c.name AS city_name,
--     AVG(pt.total_entrances + pt.total_exits) AS avg_daily_traffic
-- FROM
--     Stations s
--     JOIN Cities c ON s.city_id = c.city_id
--     JOIN PassengerTraffic pt ON s.station_id = pt.station_id
-- WHERE
--     pt.date BETWEEN DATE('now', '-18 months') AND DATE('now')
-- GROUP BY
--     s.station_id, s.name, c.name
-- ORDER BY
--     avg_daily_traffic DESC
-- LIMIT 3;

-- Maintenance costs
-- SELECT
--     s.name AS station_name,
--     SUM(sm.cost) AS total_maintenance_cost,
--     SUM(pt.total_entrances) AS total_entrances,
--     SUM(pt.total_exits) AS total_exits
-- FROM
--     Stations s
--     LEFT JOIN StationMaintenance sm ON s.station_id = sm.station_id
--     LEFT JOIN PassengerTraffic pt ON s.station_id = pt.station_id
-- WHERE
--     sm.maintenance_date BETWEEN DATE('now', '-18 months') AND DATE('now')
--     AND pt.date BETWEEN DATE('now', '-18 months') AND DATE('now')
-- GROUP BY
--     s.station_id
-- HAVING
--     SUM(pt.total_entrances) > 50000;

-- Shinkansen Utlizaion
-- SELECT
--     rl.name AS Line_Name,
--     ROUND(AVG(CAST(total_exits AS FLOAT) / total_capacity), 2) AS Average_Utilization_Rate
-- FROM
--     RailLines rl
--     JOIN LineStations ls ON rl.line_id = ls.line_id
--     JOIN PassengerTraffic pt ON ls.station_id = pt.station_id
--     JOIN TrainServices ts ON rl.line_id = ts.line_id
--     JOIN (
--         SELECT
--             service_id,
--             SUM(capacity) AS total_capacity
--         FROM
--             TrainCars
--         GROUP BY
--             service_id
--     ) tc ON ts.service_id = tc.service_id
-- WHERE
--     rl.type = 'Shinkansen'
-- GROUP BY
--     rl.line_id, rl.name;

-- Line efficiency
-- SELECT
--     RANK() OVER (ORDER BY AVG(pt.total_exits) / total_capacity DESC) AS rank,
--     rl.name AS line_name,
--     (AVG(pt.total_exits) / total_capacity) AS efficiency_ratio
-- FROM
--     RailLines rl
--     JOIN LineStations ls ON rl.line_id = ls.line_id
--     JOIN PassengerTraffic pt ON ls.station_id = pt.station_id
--     JOIN TrainServices ts ON rl.line_id = ts.line_id
--     JOIN (
--         SELECT
--             service_id,
--             SUM(capacity) AS total_capacity
--         FROM
--             TrainCars
--         GROUP BY
--             service_id
--     ) tc ON ts.service_id = tc.service_id
-- GROUP BY
--     rl.line_id;

-- Urgent Maintenance
-- WITH MaintenanceCosts AS (
--     SELECT
--         station_id,
--         SUM(cost) AS total_maintenance_cost
--     FROM
--         StationMaintenance
--     WHERE
--         maintenance_date BETWEEN DATE('now', '-18 months') AND DATE('now')
--     GROUP BY
--         station_id
-- ),
-- TrafficVolumes AS (
--     SELECT
--         station_id,
--         SUM(total_entrances) AS total_entrances
--     FROM
--         PassengerTraffic
--     WHERE
--         date BETWEEN DATE('now', '-18 months') AND DATE('now')
--     GROUP BY
--         station_id
-- ),
-- Averages AS (
--     SELECT
--         AVG(total_maintenance_cost) AS avg_maintenance_cost,
--         AVG(total_entrances) AS avg_entrances
--     FROM
--         MaintenanceCosts
--         JOIN TrafficVolumes USING (station_id)
-- )
-- SELECT
--     s.station_id,
--     s.name AS station_name,
--     mc.total_maintenance_cost,
--     tv.total_entrances
-- FROM
--     Stations s
--     JOIN MaintenanceCosts mc ON s.station_id = mc.station_id
--     JOIN TrafficVolumes tv ON s.station_id = tv.station_id,
--     Averages
-- WHERE
--     mc.total_maintenance_cost < Averages.avg_maintenance_cost
--     AND tv.total_entrances > Averages.avg_entrances;


-- Peak Hours
-- WITH HourlyTraffic AS (
--     SELECT
--         station_id,
--         strftime('%H', date) AS h,
--         SUM(total_entrances) AS entrances
--     FROM
--         PassengerTraffic
--     GROUP BY
--         station_id, h
-- ),
-- StationRanking AS (
--     SELECT
--         station_id,
--         SUM(entrances) AS total_entrances
--     FROM
--         HourlyTraffic
--     GROUP BY
--         station_id
--     ORDER BY
--         total_entrances DESC
--     LIMIT 5
-- )
-- SELECT
--     s.name AS station_name,
--     ht.h,
--     AVG(ht.entrances) AS average_entrances
-- FROM
--     HourlyTraffic ht
-- JOIN
--     StationRanking sr ON ht.station_id = sr.station_id
-- JOIN
--     Stations s ON ht.station_id = s.station_id
-- GROUP BY
--     ht.station_id, ht.h
-- ORDER BY
--     s.name, average_entrances DESC;

WITH MaintenanceCosts AS (
    SELECT
        station_id,
        SUM(cost) AS total_cost
    FROM
        StationMaintenance
    GROUP BY
        station_id
),
TrafficVolumes AS (
    SELECT
        station_id,
        SUM(total_entrances + total_exits) AS total_traffic
    FROM
        PassengerTraffic
    GROUP BY
        station_id
)
SELECT
    s.name AS station_name,
    IFNULL(mc.total_cost / NULLIF(tv.total_traffic, 0), 0) AS maintenance_efficiency_index
FROM
    Stations s
LEFT JOIN
    MaintenanceCosts mc ON s.station_id = mc.station_id
LEFT JOIN
    TrafficVolumes tv ON s.station_id = tv.station_id
ORDER BY
    maintenance_efficiency_index DESC;