-- Command line to run:
-- psql -d test_db -U test -f .\create_schema.sql -v _schemaName="Hello"
-- SET search_path = pg_temp;
DROP SCHEMA IF EXISTS public CASCADE;
CREATE SCHEMA public;

-- Create our custom types
DROP TYPE IF EXISTS status;
CREATE TYPE status AS ENUM ('disabled', 'active', 'moderate');

--
-- Create Tables
--
-- Create dummy meta table for client
CREATE TABLE metas(
    id                       serial PRIMARY KEY
);

-- Create client Table --
CREATE TABLE clients(
   id                       serial PRIMARY KEY NOT NULL,
   meta_id                  integer references metas(id) NOT NULL
);

ALTER TABLE metas
    ADD COLUMN client_id                integer references clients(id),
    ADD COLUMN name                     text NOT NULL,
    ADD COLUMN description              text NOT NULL,
    ADD COLUMN created_at               timestamp without time zone default (now() at time zone 'utc'),
    ADD COLUMN updated_at               timestamp without time zone default (now() at time zone 'utc');

-- Create tags Table --
CREATE TABLE tags(
   id                       serial PRIMARY KEY NOT NULL,
   name                     text NOT NULL,
   parent_tag_id            integer references tags(id)
);

-- Create tags Table --
CREATE TABLE meta_tag_mapping(
   id                       serial PRIMARY KEY NOT NULL,
   tag_id                   integer references tags(id) NOT NULL,
   meta_id                  integer references metas(id) NOT NULL
);

CREATE TABLE sets(
    id                      serial PRIMARY KEY NOT NULL,
    meta_id                 integer references metas(id) NOT NULL
);

CREATE TABLE groups(
    id                      serial PRIMARY KEY NOT NULL,
    meta_id                 integer references metas(id) NOT NULL
);

CREATE TABLE set_group_mapping(
    id                      serial PRIMARY KEY NOT NULL,
    set_id                  integer references sets(id) NOT NULL,
    group_id                integer references groups(id) NOT NULL
);

CREATE TABLE schemas(
    id                      serial PRIMARY KEY NOT NULL,
    meta_id                 integer references metas(id) NOT NULL,
    root_schema_id          integer references schemas(id),
    version                 integer NOT NULL,
    data                    json,
    rendering               json
);

CREATE TABLE items(
    id                      serial PRIMARY KEY NOT NULL,
    meta_id                 integer references metas(id) NOT NULL,
    schema_id               integer references schemas(id)
);

CREATE TABLE group_item_mapping(
    id                      serial PRIMARY KEY NOT NULL,
    group_id                integer references groups(id) NOT NULL,
    item_id                 integer references items(id) NOT NULL
);

CREATE TABLE regions(
    id                      serial PRIMARY KEY NOT NULL,
    meta_id                 integer references metas(id) NOT NULL,
    parent_region_id        integer references regions(id)
);

CREATE TABLE datas(
    id                      serial PRIMARY KEY NOT NULL,
    item_id                 integer references items(id) NOT NULL,
    region_id               integer references regions(id) NOT NULL,
    root_data_id            integer references datas(id),
    status                  status NOT NULL,
    data                    json,
    rendering               json    
);