-- =============START USER SCHEMA==================

DROP SCHEMA IF EXISTS "user";
CREATE SCHEMA "user"


-- ----------------------------
-- Table structure for role
-- ----------------------------

DROP TABLE IF EXISTS "user"."role" CASCADE;

-- Ver 0.1.0 ( Design )
-- Ver 0.1.0 ( Current & Implemented )
CREATE TABLE "user"."role"(
  "id"            UUID  	    NOT NULL DEFAULT uuid_generate_v4(),
  "code"          VARCHAR(5)     NOT NULL ,
  "name"          VARCHAR(100)     NOT NULL ,
  "desc"          TEXT        NULL ,
  "created_at"    TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP ,
  "updated_at"    TIMESTAMP   NULL ,
  PRIMARY KEY ("id")
);

-- INDEX TABLE role
CREATE INDEX "pkey_urole" ON "user"."role" ("id");


-- ----------------------------
-- Table structure for account
-- ----------------------------

DROP TABLE IF EXISTS "user"."account" CASCADE;

-- Ver 0.1.0 ( Design )
-- Ver 0.1.0 ( Current & Implemented )
CREATE TABLE "user"."account"(
  "id"            UUID  	         NOT NULL DEFAULT uuid_generate_v4(),
  "urole_id"      UUID             NOT NULL ,
  "username"      varchar(255)     NOT NULL UNIQUE,
  "pwd"           TEXT             NOT NULL ,
  "fullname"      varchar(255)     NOT NULL ,
  "shortname"     varchar(255)     NOT NULL ,
  "email"         varchar(255)     NOT NULL UNIQUE ,
  "avatar"        varchar(255)     NOT NULL ,
  "note"          varchar(255)     NULL ,
  "status"        BOOLEAN     NOT NULL  DEFAULT FALSE ,
  "is_ban"        BOOLEAN     NOT NULL  DEFAULT FALSE ,
  "last_active"   TIMESTAMP   NOT NULL  DEFAULT CURRENT_TIMESTAMP ,
  "created_at"    TIMESTAMP   NOT NULL  DEFAULT CURRENT_TIMESTAMP ,
  "updated_at"    TIMESTAMP   NULL ,
  "deleted_at"    TIMESTAMP   NULL ,
  PRIMARY KEY ("id"),
  FOREIGN KEY ("urole_id")  REFERENCES "user"."role"("id")  ON UPDATE CASCADE ON DELETE CASCADE
);

-- INDEX TABLE account
CREATE INDEX "pkey_uaccount" ON "user"."account" ("id");
CREATE INDEX "fkey_uaccount_urole" ON "user"."account" ("urole_id");



-- ----------------------------
-- Table structure for api_tokens
-- ----------------------------

DROP TABLE IF EXISTS "user"."api_tokens" CASCADE;

-- Ver 0.1.0 ( Design )
-- Ver 0.1.0 ( Current & Implemented )
CREATE TABLE "user"."api_tokens"(
  "id"                UUID  	      NOT NULL DEFAULT uuid_generate_v4(),
  "user_id"           UUID          NOT NULL ,
  "name"              VARCHAR(255)  NOT NULL ,
  "type"              VARCHAR(255)  NOT NULL ,
  "token"             VARCHAR(255)  NOT NULL ,
  "created_at"        TIMESTAMP     NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  "expires_at"        TIMESTAMP ,
  PRIMARY KEY ("id"),
  FOREIGN KEY ("user_id")  REFERENCES "user"."account"("id")
);

-- INDEX TABLE account
CREATE INDEX "pkey_uapi_tokens" ON "user"."api_tokens" ("id");
CREATE INDEX "fkey_uapi_tokens_uaccount" ON "user"."api_tokens" ("user_id");



-- ----------------------------
-- Table structure for event
-- ----------------------------

DROP TABLE IF EXISTS "user"."event" CASCADE;
DROP SEQUENCE IF EXISTS event_id_seq;

-- Ver 1
-- ( Current & Implemented )
CREATE TABLE "user"."event"(
  "id"                UUID  	    NOT NULL DEFAULT uuid_generate_v4(),
  "uaccount_id"       UUID        NOT NULL ,
  "project_id"         UUID        NULL ,
  "fullname"          VARCHAR     NOT NULL ,
  "title"             VARCHAR     NOT NULL ,
  "body"              TEXT        NULL ,
  "start"             DATE        NOT NULL ,
  "end"               DATE        NOT NULL ,
  "created_at"        TIMESTAMP   NOT NULL  DEFAULT CURRENT_TIMESTAMP,
  "updated_at"        TIMESTAMP   NULL,
  "deleted_at"        TIMESTAMP   NULL,
  PRIMARY KEY ("id"),
  FOREIGN KEY ("uaccount_id")  REFERENCES "user"."account"("id")
);

-- =============END USER SCHEMA==================