CREATE TABLE IF NOT EXISTS parkomats (
	transaction_id varchar(255) NOT NULL,
	ticket_bought timestamptz NULL,
	validity_from timestamptz NULL,
	validity_to timestamptz NULL,
	parking_zone varchar(255) NULL,
	price numeric NOT NULL,
	channel varchar(255) 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 parkomats_pkey PRIMARY KEY (transaction_id)
);

CREATE OR REPLACE VIEW v_parkomats_sales
AS SELECT date_part('year'::text, parkomats.ticket_bought) AS year,
    "substring"(split_part(parkomats.parking_zone::text, '-'::text, 1), 2, 2) AS parking_zone,
    'Praha '::text || "substring"(split_part(parkomats.parking_zone::text, '-'::text, 1), 2, 2) AS city_district,
        CASE
            WHEN parkomats.channel::text = 'PARKMACHINE'::text THEN 'Parkovací automat'::text
            ELSE 'VPH'::text
        END AS channel,
    sum(parkomats.price) AS sale
   FROM parkomats
  GROUP BY (date_part('year'::text, parkomats.ticket_bought)), ("substring"(split_part(parkomats.parking_zone::text, '-'::text, 1), 2, 2)), ('Praha '::text || "substring"(split_part(parkomats.parking_zone::text, '-'::text, 1), 2, 2)), (
        CASE
            WHEN parkomats.channel::text = 'PARKMACHINE'::text THEN 'Parkovací automat'::text
            ELSE 'VPH'::text
        END);
