CREATE SEQUENCE IF NOT EXISTS parkings_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    CACHE 1;

CREATE TABLE IF NOT EXISTS parkings (
	id bigint NOT NULL DEFAULT nextval('parkings_id_seq'::regclass),
	"source" varchar(255) NOT NULL,
	source_id varchar(255) NOT NULL,
	data_provider text NULL,
	"name" varchar(255) NULL,
	category varchar(100) NULL,
	date_modified int8 NULL,
	address json NULL,
	"location" geometry NULL,
	area_served varchar(255) NULL,
	web_app_payment_url text NULL,
	total_spot_number int4 NULL,
	create_batch_id int8 NULL,
	created_at timestamptz NULL,
	created_by varchar(150) NULL,
	update_batch_id int8 NULL,
	updated_at timestamptz NULL,
	updated_by varchar(150) NULL,
	tariff_id varchar(50) NULL,
	valid_from timestamptz NULL,
	valid_to timestamptz NULL,
	parking_type varchar(100) NULL,
	zone_type varchar(100) NULL,
	centroid geometry NULL,
	android_app_payment_url text NULL,
	ios_app_payment_url text NULL,

	CONSTRAINT parkings_id_key UNIQUE (id),
	CONSTRAINT parkings_pk PRIMARY KEY (source, source_id)
);

CREATE TABLE IF NOT EXISTS parkings_location (
	id varchar NOT NULL,
	"source" varchar(255) NOT NULL,
	source_id varchar(255) NOT NULL,
	data_provider varchar(255) NOT NULL,
	"location" geometry NOT NULL,
	centroid geometry NOT NULL,
	address json NULL,
	total_spot_number int4 NOT NULL,
	create_batch_id int8 NULL,
	created_at timestamptz NULL,
	created_by varchar(150) NULL,
	update_batch_id int8 NULL,
	updated_at timestamptz NULL,
	updated_by varchar(150) NULL,

	CONSTRAINT parkings_location_pkey PRIMARY KEY (id),
	CONSTRAINT parkings_location_fkey FOREIGN KEY ("source",source_id) REFERENCES parkings("source",source_id)
);

CREATE INDEX IF NOT EXISTS parkings_location_parking_idx ON parkings_location USING btree (source, source_id);

CREATE SEQUENCE IF NOT EXISTS parkings_occupancies_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1
  CACHE 1;

CREATE TABLE IF NOT EXISTS parkings_occupancies (
	id bigint NOT NULL DEFAULT nextval('parkings_occupancies_id_seq'::regclass),
	capacity int4 NULL,
	occupation int4 NULL,
	parking_id int4 NULL,
	reservedcapacity int4 NULL,
	reservedoccupation int4 NULL,
	create_batch_id int8 NULL,
	created_at timestamptz NULL,
	created_by varchar(150) NULL,
	update_batch_id int8 NULL,
	updated_at timestamptz NULL,
	updated_by varchar(150) NULL,

	CONSTRAINT parkings_occupancies_pkey PRIMARY KEY (id)
);
