apiVersion: v1
kind: ConfigMap
metadata:
  labels:
    io.kompose.service: postgres-initdb-config
  name: postgres-initdb-config
data:
  postgres_DataSetup.sql: |-
    -- Create the primary key generator

    create sequence public.table_id_seq;

    CREATE OR REPLACE FUNCTION public.next_id(OUT result bigint) AS $$
    DECLARE
        our_epoch bigint := 1314220021721;
        seq_id bigint;
        now_millis bigint;
        shard_id int := 5;
    BEGIN
        SELECT nextval('public.table_id_seq') % 1024 INTO seq_id;

        SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
        result := (now_millis - our_epoch) << 23;
        result := result | (shard_id << 10);
        result := result | (seq_id);
    END;
    $$ LANGUAGE PLPGSQL;

    -- Create table for promotions

    CREATE TABLE "public"."promotion" (
        id  bigint not null default next_id(),
        name varchar(50) NOT NULL,
        description varchar(255),
        validfrom timestamp ,
        validthru timestamp ,
        active boolean DEFAULT false,
        customerid varchar(50)  NOT NULL,
        approvalstatus integer  DEFAULT 0,
        prevapprovalstatus integer DEFAULT 0,
        PRIMARY KEY (id)
    );


    -- Insert sample promotion data
    insert into promotion (name, description, validfrom, validthru, customerid)
    VALUES ('Promo1', 'Promo1 Desc', TIMESTAMP '2000-01-01 00:00:00', TIMESTAMP '2200-01-01 00:00:00','ducksrus'),
           ('Promo2', 'Promo2 Desc', TIMESTAMP '2000-01-01 00:00:00', TIMESTAMP '2200-01-01 00:00:00','patoloco'),
           ('Promo3', 'Promo3 Desc', TIMESTAMP '2000-01-01 00:00:00', TIMESTAMP '2200-01-01 00:00:00','ducksrus')
  userSchema_sql.txt: |-
    -- This file is used to create the app user tables in the appuser DB and it is called in userdb.sh.
    -- It does not have .sql extension to avoid postgres automatically running it against the default DB as well

    -- Create the primary key generator

    create sequence public.table_id_seq;

    CREATE OR REPLACE FUNCTION public.next_id(OUT result bigint) AS $$
    DECLARE
        our_epoch bigint := 1314220021721;
        seq_id bigint;
        now_millis bigint;
        shard_id int := 5;
    BEGIN
        SELECT nextval('public.table_id_seq') % 1024 INTO seq_id;

        SELECT FLOOR(EXTRACT(EPOCH FROM clock_timestamp()) * 1000) INTO now_millis;
        result := (now_millis - our_epoch) << 23;
        result := result | (shard_id << 10);
        result := result | (seq_id);
    END;
    $$ LANGUAGE PLPGSQL;

    -- Create table for appusers

    CREATE TABLE "public"."appuser" (
        id  bigint not null default next_id(),
        firstname varchar(100) NOT NULL,
        lastname varchar(100) NOT NULL,
        validfrom timestamp NOT NULL,
        validthru timestamp NOT NULL,
        active boolean DEFAULT false,
        pwd varchar(255)  NOT NULL,
        name varchar(200) GENERATED ALWAYS AS (firstname || ' ' || lastname) STORED,
        email varchar(255),
        company varchar(255),
        createdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        updatedate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (id)
    );


    -- Insert sample appuser data
    insert into appuser (firstname , lastname, validfrom, validthru, pwd, email, company )
    VALUES ('Super', 'Duck', TIMESTAMP '2000-01-01 00:00:00', TIMESTAMP '2200-01-01 00:00:00','$2a$10$BNGbOtnCI6GD1boXDKyt1uYbweZyXc2473Q1VwRn/DzCTS4xWeGea','duck@mymail.com', 'Duck Inc.'),
           ('Mighty', 'Ant', TIMESTAMP '2000-01-01 00:00:00', TIMESTAMP '2200-01-01 00:00:00','$2a$10$BNGbOtnCI6GD1boXDKyt1uYbweZyXc2473Q1VwRn/DzCTS4xWeGea', 'ant@mymail.com', 'Ant Corp'),
           ('Awesome', 'Cow', TIMESTAMP '2000-01-01 00:00:00', TIMESTAMP '2200-01-01 00:00:00','$2a$10$BNGbOtnCI6GD1boXDKyt1uYbweZyXc2473Q1VwRn/DzCTS4xWeGea', 'cow@mymail.com', 'Cow LLC')
  userdb.sh: |
    #!/bin/bash
    set -e

    POSTGRES="psql --username postgres"

    echo "Creating database: appuser"

    $POSTGRES <<EOSQL
    CREATE DATABASE appuser;
    EOSQL

    echo "Creating schema..."
    psql -d appuser -a -U postgres -f ./docker-entrypoint-initdb.d/userSchema_sql.txt