apiVersion: v1
kind: ConfigMap
metadata:
  name: custom-init-scripts
  namespace: hypercloud5-system
data:
  INIT_DB_SQL.sh: |-
    #!/bin/bash
    psql -d "$1" <<__SQL__
    CREATE TABLE AUDIT (ID VARCHAR(40) NOT NULL, USERNAME VARCHAR(255), USERAGENT VARCHAR(255), NAMESPACE VARCHAR(255), APIGROUP VARCHAR(255), APIVERSION VARCHAR(32), RESOURCE VARCHAR(64), NAME VARCHAR(255), STAGE VARCHAR(32), STAGETIMESTAMP TIMESTAMPTZ NOT NULL, VERB VARCHAR(32), CODE INT, STATUS VARCHAR(255), REASON VARCHAR(255), MESSAGE VARCHAR(255));
    SELECT create_hypertable('audit', 'stagetimestamp', chunk_time_interval => INTERVAL '1 days', if_not_exists => TRUE);
    SELECT add_retention_policy('audit', INTERVAL '7 days', if_not_exists => TRUE);
    SELECT alter_job(1000, schedule_interval => INTERVAL '1 hours');
    CREATE TABLE CLUSTER_MEMBER (ID SERIAL, NAMESPACE VARCHAR(255) NOT NULL, CLUSTER VARCHAR(255) NOT NULL, MEMBER_ID VARCHAR(255) NOT NULL, MEMBER_NAME VARCHAR(255), ATTRIBUTE VARCHAR(255), ROLE VARCHAR(255), STATUS VARCHAR(255), CREATEDTIME TIMESTAMPTZ NOT NULL DEFAULT NOW(), UPDATEDTIME TIMESTAMPTZ NOT NULL DEFAULT NOW());
    CREATE OR REPLACE PROCEDURE DELETE_PENDING_MEMBER(job_id int, config jsonb) LANGUAGE PLPGSQL AS \$$ BEGIN DELETE FROM cluster_member WHERE STATUS = 'pending' and createdtime < now() - interval '1 days'; END\$$;
    SELECT add_job('DELETE_PENDING_MEMBER','1 hours');
    ALTER TABLE CLUSTER_MEMBER ADD CONSTRAINT MEMBER_CONSTRAINT UNIQUE (NAMESPACE, CLUSTER, MEMBER_ID, ATTRIBUTE);
    CREATE UNIQUE INDEX CLUSTER_STATUS ON CLUSTER_MEMBER (NAMESPACE, CLUSTER, STATUS) WHERE ( STATUS = 'owner');


    CREATE TABLE METERING (ID VARCHAR(64) NOT NULL,NAMESPACE VARCHAR(255) NOT NULL,CPU DOUBLE PRECISION,MEMORY BIGINT,STORAGE BIGINT,GPU DOUBLE PRECISION,PUBLIC_IP INT,PRIVATE_IP INT, TRAFFIC_IN BIGINT, TRAFFIC_OUT BIGINT, METERING_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,STATUS VARCHAR(255),PRIMARY KEY (ID));
    ALTER TABLE METERING ADD CONSTRAINT UNIQUE_METERING UNIQUE (NAMESPACE, METERING_TIME);
    CREATE TABLE METERING_HOUR (ID VARCHAR(64) NOT NULL,NAMESPACE VARCHAR(255) NOT NULL,CPU DOUBLE PRECISION,MEMORY BIGINT,STORAGE BIGINT,GPU DOUBLE PRECISION,PUBLIC_IP INT,PRIVATE_IP INT, TRAFFIC_IN BIGINT, TRAFFIC_OUT BIGINT, METERING_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,STATUS VARCHAR(255),PRIMARY KEY (ID));
    ALTER TABLE METERING_HOUR ADD CONSTRAINT UNIQUE_METERING_HOUR UNIQUE (NAMESPACE, METERING_TIME);
    CREATE TABLE METERING_DAY (ID VARCHAR(64) NOT NULL,NAMESPACE VARCHAR(255) NOT NULL,CPU DOUBLE PRECISION,MEMORY BIGINT,STORAGE BIGINT,GPU DOUBLE PRECISION,PUBLIC_IP INT,PRIVATE_IP INT, TRAFFIC_IN BIGINT, TRAFFIC_OUT BIGINT, METERING_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,STATUS VARCHAR(255),PRIMARY KEY (ID));
    ALTER TABLE METERING_DAY ADD CONSTRAINT UNIQUE_METERING_DAY UNIQUE (NAMESPACE, METERING_TIME);
    CREATE TABLE METERING_MONTH (ID VARCHAR(64) NOT NULL,NAMESPACE VARCHAR(255) NOT NULL,CPU DOUBLE PRECISION,MEMORY BIGINT,STORAGE BIGINT,GPU DOUBLE PRECISION,PUBLIC_IP INT,PRIVATE_IP INT, TRAFFIC_IN BIGINT, TRAFFIC_OUT BIGINT, METERING_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,STATUS VARCHAR(255),PRIMARY KEY (ID));
    ALTER TABLE METERING_MONTH ADD CONSTRAINT UNIQUE_METERING_MONTH UNIQUE (NAMESPACE, METERING_TIME);
    CREATE TABLE METERING_YEAR (ID VARCHAR(64) NOT NULL,NAMESPACE VARCHAR(255) NOT NULL,CPU DOUBLE PRECISION,MEMORY BIGINT,STORAGE BIGINT,GPU DOUBLE PRECISION,PUBLIC_IP INT,PRIVATE_IP INT, TRAFFIC_IN BIGINT, TRAFFIC_OUT BIGINT, METERING_TIME TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,STATUS VARCHAR(255),PRIMARY KEY (ID));
    ALTER TABLE METERING_YEAR ADD CONSTRAINT UNIQUE_METERING_YEAR UNIQUE (NAMESPACE, METERING_TIME);
    __SQL__
    #SELECT cron.schedule('* * * * *', $$DELETE FROM CLUSTER_MEMBER WHERE STATUS = 'pending' AND CREATEDTIME < now() - interval '1days'$$);
    #CREATE OR REPLACE FUNCTION UPDATE_TIME() RETURNS TRIGGER AS $$ BEGIN NEW.updatedtime = NOW(); RETURN NEW; END; $$ LANGUAGE plpgsql;
    #CREATE TRIGGER TRIGGER_UPDATE_TIME AFTER UPDATE ON CLUSTER_MEMBER FOR EACH ROW EXECUTE PROCEDURE UPDATE_TIME();
