-- Drop tables if they exist
DROP TABLE IF EXISTS stop_media;
DROP TABLE IF EXISTS route_stops;
DROP TABLE IF EXISTS stops;
DROP TABLE IF EXISTS routes;

-- Routes table
CREATE TABLE routes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    zipcode VARCHAR(20) NOT NULL,
    route_type VARCHAR(50) NOT NULL,
    difficulty INT NOT NULL,
    poi_count INT NOT NULL,
    pause_count INT NOT NULL,
    route_path TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Stops table
CREATE TABLE stops (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    stop_type ENUM('CARD_DRAW', 'POINT_OF_INTEREST', 'PAUSE') NOT NULL,
    latitude DECIMAL(10, 8) NOT NULL,
    longitude DECIMAL(11, 8) NOT NULL,
    description TEXT,
    cell_signal_strength DECIMAL(5, 2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Joining table for routes and stops
CREATE TABLE route_stops (
    id INT AUTO_INCREMENT PRIMARY KEY,
    route_id INT NOT NULL,
    stop_id INT NOT NULL,
    stop_order INT NOT NULL,
    FOREIGN KEY (route_id) REFERENCES routes(id) ON DELETE CASCADE,
    FOREIGN KEY (stop_id) REFERENCES stops(id) ON DELETE CASCADE,
    UNIQUE KEY unique_route_stop (route_id, stop_id)
);

-- Media for stops (images and videos)
CREATE TABLE stop_media (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stop_id INT NOT NULL,
    media_type ENUM('IMAGE', 'VIDEO', 'AUDIO') NOT NULL,
    url TEXT NOT NULL,
    is_generated BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (stop_id) REFERENCES stops(id) ON DELETE CASCADE
);

-- Create indexes
CREATE INDEX idx_routes_zipcode ON routes(zipcode);
CREATE INDEX idx_stops_type ON stops(stop_type);
CREATE INDEX idx_route_stops_route_id ON route_stops(route_id);
CREATE INDEX idx_route_stops_stop_id ON route_stops(stop_id);
CREATE INDEX idx_stop_media_stop_id ON stop_media(stop_id);