init.sql

-- PostgreSQL Database Schema for German Streets API
-- Migrated from MariaDB

-- Create cities table first
CREATE TABLE IF NOT EXISTS cities (
    id SERIAL PRIMARY KEY,
    city_name VARCHAR(255) NOT NULL,
    zip_code VARCHAR(20),
    regional_key VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_city UNIQUE (city_name, zip_code)
);

-- Create indexes for cities table
CREATE INDEX IF NOT EXISTS idx_city_name ON cities(city_name);
CREATE INDEX IF NOT EXISTS idx_regional_key ON cities(regional_key);

-- Create borough table - borough belongs to a city
CREATE TABLE IF NOT EXISTS borough (
    id SERIAL PRIMARY KEY,
    borough_name VARCHAR(255) NOT NULL,
    city_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_borough_city UNIQUE (borough_name, city_id),
    CONSTRAINT fk_borough_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE
);

-- Create indexes for borough table
CREATE INDEX IF NOT EXISTS idx_borough_name ON borough(borough_name);
CREATE INDEX IF NOT EXISTS idx_city_id ON borough(city_id);

-- Create streets table
CREATE TABLE IF NOT EXISTS streets (
    id SERIAL PRIMARY KEY,
    street_name VARCHAR(255) NOT NULL,
    city_id INT NOT NULL,
    borough_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_street_city UNIQUE (street_name, city_id),
    CONSTRAINT fk_city FOREIGN KEY (city_id) REFERENCES cities(id) ON DELETE CASCADE,
    CONSTRAINT fk_street_borough FOREIGN KEY (borough_id) REFERENCES borough(id) ON DELETE SET NULL
);

-- Create indexes for streets table
CREATE INDEX IF NOT EXISTS idx_street_name ON streets(street_name);
CREATE INDEX IF NOT EXISTS idx_borough_id ON streets(borough_id);

-- Table for CSV integrity tracking
CREATE TABLE IF NOT EXISTS csv_integrity (
    id SERIAL PRIMARY KEY,
    file_url VARCHAR(500) NOT NULL,
    file_hash VARCHAR(64) NOT NULL,
    file_size BIGINT,
    last_modified VARCHAR(100),
    fetch_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'downloaded',
    CONSTRAINT chk_csv_status CHECK (status IN ('downloaded', 'processed', 'failed'))
);

-- Table for API usage logging
CREATE TABLE IF NOT EXISTS api_log (
    id SERIAL PRIMARY KEY,
    request_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    client_ip VARCHAR(45) NOT NULL,
    user_agent VARCHAR(1000),
    request_method VARCHAR(10) NOT NULL,
    request_uri VARCHAR(2000) NOT NULL,
    query_params JSON,
    response_status INT NOT NULL,
    response_time_ms INT,
    results_count INT,
    error_message VARCHAR(1000)
);

-- Create indexes for api_log table
CREATE INDEX IF NOT EXISTS idx_client_ip ON api_log(client_ip);
CREATE INDEX IF NOT EXISTS idx_request_time ON api_log(request_time);
CREATE INDEX IF NOT EXISTS idx_response_status ON api_log(response_status);

-- Table for tracking fetches
CREATE TABLE IF NOT EXISTS fetch_log (
    id SERIAL PRIMARY KEY,
    fetch_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50),
    records_processed INT,
    records_added INT DEFAULT 0,
    records_updated INT DEFAULT 0,
    records_deleted INT DEFAULT 0,
    csv_hash VARCHAR(64),
    error_message TEXT
);

-- ===================================
-- Triggers for automatic updated_at
-- ===================================

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger for cities table
DROP TRIGGER IF EXISTS update_cities_updated_at ON cities;
CREATE TRIGGER update_cities_updated_at
    BEFORE UPDATE ON cities
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Trigger for borough table
DROP TRIGGER IF EXISTS update_borough_updated_at ON borough;
CREATE TRIGGER update_borough_updated_at
    BEFORE UPDATE ON borough
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Trigger for streets table
DROP TRIGGER IF EXISTS update_streets_updated_at ON streets;
CREATE TRIGGER update_streets_updated_at
    BEFORE UPDATE ON streets
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();