CREATE TABLE IF NOT EXISTS system_information (
    "operator_id" serial,
    "system_id" varchar(50) NOT NULL,
    "language" varchar(5) NOT NULL,
    "logo" text NOT NULL,
    "name" text NOT NULL,
    "short_name" text,
    "operator" text,
    "url" text,
    "purchase_url" text,
    "start_date" timestamp,
    "phone_number" text,
    "email" text,
    "feed_contact_email" text,
    "timezone" varchar(50) NOT NULL,
    "license_id" text,
    "license_url" text,
    "attribution_organization_name" text,
    "attribution_url" text,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT system_information_pkey PRIMARY KEY (operator_id),
    CONSTRAINT system_information_system_id_uniq UNIQUE (system_id)
);

CREATE INDEX system_information_name_idx ON system_information USING btree ("name");

CREATE TABLE IF NOT EXISTS rental_apps (
    "id" varchar(50),
    "android_store_url" text,
    "android_discovery_url" text,
    "ios_store_url" text,
    "ios_discovery_url" text,
    "web_url" text,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT rental_apps_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS rental_apps_system_info (
    "rental_app_id" varchar(50) NOT NULL,
    "system_id" varchar(50) NOT NULL,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT rental_apps_system_info_rental_app_id_fkey FOREIGN KEY (rental_app_id) REFERENCES rental_apps(id),
    CONSTRAINT rental_apps_system_info_system_id_fkey FOREIGN KEY (system_id) REFERENCES system_information(system_id)
);

CREATE TABLE IF NOT EXISTS geofencing_zones (
    "id" text,
    "system_id" varchar(50) NOT NULL,
    "name" text,
    "start" timestamp,
    "end" timestamp,
    "geom" geometry NOT NULL,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT geofencing_zones_pkey PRIMARY KEY (id),
    CONSTRAINT geofencing_zones_system_id_fkey FOREIGN KEY (system_id) REFERENCES system_information(system_id)
);

CREATE TABLE IF NOT EXISTS geofencing_rules (
    "geofencing_zone_id" text NOT NULL,
    "ride_allowed" boolean NOT NULL,
    "ride_through_allowed" boolean NOT NULL,
    "maximum_speed_kph" integer,
    "parking_allowed" boolean NOT NULL,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT geofencing_rules_zone_id_fkey FOREIGN KEY (geofencing_zone_id) REFERENCES geofencing_zones(id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS pricing_plans (
    "id" varchar(50),
    "system_id" varchar(50) NOT NULL,
    "url" text,
    "last_updated" timestamp NOT NULL,
    "name" text NOT NULL,
    "currency" varchar(8) NOT NULL,
    "price" float NOT NULL,
    "is_taxable" boolean NOT NULL,
    "description" text NOT NULL,
    "surge_pricing" boolean,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT pricing_plans_pkey PRIMARY KEY (id),
    CONSTRAINT pricing_plans_system_id_fkey FOREIGN KEY (system_id) REFERENCES system_information(system_id)
);

CREATE TABLE IF NOT EXISTS pricings (
    "pricing_plan_id" varchar(50) NOT NULL,
    "pricing_type" text,
    "pricing_order" integer,
    "start" integer NOT NULL,
    "rate" float NOT NULL,
    "interval" integer NOT NULL,
    "end" integer,
    "start_time_of_period" text,
    "end_time_of_period" text,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT pricings_plan_id_fkey FOREIGN KEY (pricing_plan_id) REFERENCES pricing_plans(id)
);

CREATE TABLE IF NOT EXISTS station_information (
    "id" text,
    "name" text NOT NULL,
    "short_name" text,
    "point" geometry NOT NULL,
    "address" text,
    "post_code" text,
    "cross_street" text,
    "region_id" text,
    "rental_methods" text,
    "is_virtual_station" boolean,
    "station_area" geometry,
    "vehicle_capacity" integer,
    "vehicle_type_capacity" text,
    "is_valet_station" boolean,
    "rental_app_id" varchar(50),

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT station_information_pkey PRIMARY KEY (id),
    CONSTRAINT station_information_rental_app_id_fkey FOREIGN KEY (rental_app_id) REFERENCES rental_apps(id)
);

CREATE TABLE IF NOT EXISTS station_status (
    "station_id" text,
    "num_bikes_available" integer NOT NULL,
    "num_bikes_disabled" integer,
    "num_docks_available" integer,
    "is_installed" boolean,
    "is_renting" boolean,
    "is_returning" boolean,
    "last_reported" timestamp,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT station_status_station_id_fkey FOREIGN KEY (station_id) REFERENCES station_information(id)
        ON DELETE CASCADE
);

CREATE INDEX station_status_last_reported_idx ON station_status USING btree ("last_reported");

CREATE TABLE IF NOT EXISTS vehicle_types (
    "id" text,
    "form_factor" text,
    "propulsion_type" text,
    "max_range_meters" float,
    "name" text,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT vehicle_types_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS station_status_vehicle_type (
    "station_id" text NOT NULL,
    "vehicle_type_id" text NOT NULL,
    "num_bikes_available" integer NOT NULL,
    "num_bikes_disabled" integer,
    "num_docks_available" integer,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT station_status_vehicle_type_station_id_fkey FOREIGN KEY (station_id) REFERENCES station_information(id)
        ON DELETE CASCADE,
    CONSTRAINT station_status_vehicle_type_vehicle_type_id_fkey FOREIGN KEY (vehicle_type_id) REFERENCES vehicle_types(id)
        ON DELETE CASCADE,
    CONSTRAINT station_status_vehicle_type_station_id_uniq UNIQUE (station_id)
);

CREATE TABLE IF NOT EXISTS bike_status (
    "id" text,
    "system_id" varchar(50) NOT NULL,
    "point" geometry,
    "helmets" integer,
    "passengers" integer,
    "damage_description" text,
    "description" text,
    "vehicle_registration" text,
    "is_reserved" boolean NOT NULL,
    "is_disabled" boolean NOT NULL,
    "vehicle_type_id" text NOT NULL,
    "last_reported" timestamp,
    "current_range_meters" float,
    "charge_percent" integer,
    "rental_app_id" varchar(50) NOT NULL,
    "station_id" text,

    -- Audit fields
    create_batch_id bigint,
    created_at timestamp with time zone,
    created_by character varying(150),
    update_batch_id bigint,
    updated_at timestamp with time zone,
    updated_by character varying(150),

    CONSTRAINT bike_status_pkey PRIMARY KEY (id),
    CONSTRAINT bike_status_system_id_fkey FOREIGN KEY (system_id) REFERENCES system_information(system_id),
    CONSTRAINT bike_status_vehicle_type_id_fkey FOREIGN KEY (vehicle_type_id) REFERENCES vehicle_types(id),
    CONSTRAINT bike_status_rental_app_id_fkey FOREIGN KEY (rental_app_id) REFERENCES rental_apps(id),
    CONSTRAINT bike_status_station_id_fkey FOREIGN KEY (station_id) REFERENCES station_information(id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

CREATE INDEX bike_status_last_reported_idx ON bike_status USING btree ("last_reported");
CREATE INDEX bike_status_point_idx ON bike_status USING gist ("point");

-- Static data for Rekola
INSERT INTO system_information ("system_id", "language", "logo", "name", "purchase_url", "start_date", "email", "timezone", "created_at", "updated_at")
    VALUES('d727bb19-9755-40b2-9615-01fbb6180b8b', 'cz', 'https://mobile.rekola.cz/static/icons/vehicles/png/bike.png', 'Rekola', 'https://www.rekola.cz', NOW(), 'info@rekola.cz', 'UTC', NOW(), NOW())
    ON CONFLICT (system_id) DO NOTHING;

INSERT INTO rental_apps ("id", "android_store_url", "ios_store_url", "web_url", "created_at", "updated_at")
    VALUES ('bb622abc-84a1-11ec-a8a3-0242ac120002', 'https://play.google.com/store/apps/details?id=cz.rekola.app&hl=cs', 'https://itunes.apple.com/cz/app/rekola/id888759232?mt=8', 'https://www.rekola.cz', NOW(), NOW())
    ON CONFLICT (id) DO NOTHING;

INSERT INTO rental_apps_system_info ("rental_app_id", "system_id", "created_at", "updated_at")
    VALUES ('bb622abc-84a1-11ec-a8a3-0242ac120002', 'd727bb19-9755-40b2-9615-01fbb6180b8b', NOW(), NOW());

INSERT INTO pricing_plans ("id", "system_id", "url", "last_updated", "name", "currency", "price", "is_taxable", "description", "surge_pricing", "created_at", "updated_at")
    VALUES('4b72f960-84cf-11ec-a8a3-0242ac120002', 'd727bb19-9755-40b2-9615-01fbb6180b8b', 'https://www.rekola.cz/cenik', NOW(), 'Jednotlivé jízdy', 'CZK', 0, FALSE, '24 Kč za 30 min -- Platíte za každou jízdu samostatně', FALSE, NOW(), NOW())
    ON CONFLICT (id) DO NOTHING;

INSERT INTO pricings ("pricing_plan_id", "start", "rate", "interval", "created_at", "updated_at")
    VALUES('4b72f960-84cf-11ec-a8a3-0242ac120002', 0, 24/30, 0, NOW(), NOW());
