Nye-TeeOff/init.sql

-- Aktiver utvidelsen for GPS og kartdata
CREATE EXTENSION IF NOT EXISTS postgis;

-- 1. Tabell for selve golfanlegget/klubben
CREATE TABLE facilities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    established_year INTEGER,
    season VARCHAR(100),
    coordinates GEOMETRY(Point, 4326),
    has_driving_range BOOLEAN DEFAULT FALSE,
    has_short_game_area BOOLEAN DEFAULT FALSE,
    rentals_clubs BOOLEAN DEFAULT FALSE,
    rentals_buggy BOOLEAN DEFAULT FALSE,
    website_url VARCHAR(255),
    golfbox_tournament_url VARCHAR(255),
    pro_url VARCHAR(255),
    proshop_url VARCHAR(255),
    cafe_url VARCHAR(255),
    nsg_agreement BOOLEAN DEFAULT FALSE,
    cooperating_clubs JSONB DEFAULT '[]'::jsonb,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 2. Tabell for selve golfbanene (knyttet til anlegget)
CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    facility_id INTEGER REFERENCES facilities(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    holes INTEGER NOT NULL,
    physical_hole_count INTEGER,
    include_in_physical_hole_total BOOLEAN NOT NULL DEFAULT TRUE,
    par INTEGER,
    length_meters INTEGER,
    course_type VARCHAR(100),
    architect VARCHAR(255),
    course_guide_url VARCHAR(255),
    status VARCHAR(50) DEFAULT 'Ukjent',
    is_visible BOOLEAN NOT NULL DEFAULT TRUE,
    status_updated_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Tabell for bildeslideren
CREATE TABLE facility_images (
    id SERIAL PRIMARY KEY,
    facility_id INTEGER REFERENCES facilities(id) ON DELETE CASCADE,
    image_url VARCHAR(255) NOT NULL,
    display_order INTEGER DEFAULT 0
);