--
-- Basic Schema for Librelio Web Services
--


-- Started on 2017-04-14 10:11:37

CREATE TABLE "issue" (
    "id" integer NOT NULL,
    "code" character varying(50) NOT NULL,
    "name" character varying(50),
    "sys_clientId" integer NOT NULL
);

CREATE SEQUENCE "issue_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE "issue_id_seq" OWNED BY "issue"."id";

CREATE TABLE "sys_client" (
    "id" integer NOT NULL,
    "name" character varying(50),
    "code" character varying(50) NOT NULL
);

CREATE SEQUENCE "sys_client_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE "sys_client_id_seq" OWNED BY "sys_client"."id";

CREATE TABLE "sys_log" (
    "id" integer NOT NULL,
    "time" timestamp without time zone DEFAULT "now"() NOT NULL,
    "clientName" character varying(20),
    "sys_clientId" integer,
    "app" character varying(20),
    "username" character varying(50),
    "userId" integer,
    "issue" character varying,
    "issueId" integer,
    "deviceId" character varying(50)
);

CREATE SEQUENCE "sys_log_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE "sys_log_id_seq" OWNED BY "sys_log"."id";

CREATE TABLE "user" (
    "username" character varying(50) NOT NULL,
    "password" character varying(50) NOT NULL,
    "fullAccess" boolean DEFAULT false NOT NULL,
    "sys_clientId" integer,
    "id" integer NOT NULL,
    "nbDownload" integer
);

CREATE SEQUENCE "user_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE "user_id_seq" OWNED BY "user"."id";

CREATE TABLE "user_issue" (
    "userId" integer NOT NULL,
    "issueId" integer NOT NULL,
    "nbDownload" integer
);

CREATE TABLE "whiteIP" (
    "id" integer NOT NULL,
    "userId" integer NOT NULL,
    "ip" "inet" NOT NULL
);

CREATE SEQUENCE "whiteIP_id_seq"
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE "whiteIP_id_seq" OWNED BY "whiteIP"."id";

ALTER TABLE ONLY "issue" ALTER COLUMN "id" SET DEFAULT "nextval"('"issue_id_seq"'::"regclass");

ALTER TABLE ONLY "sys_client" ALTER COLUMN "id" SET DEFAULT "nextval"('"sys_client_id_seq"'::"regclass");

ALTER TABLE ONLY "sys_log" ALTER COLUMN "id" SET DEFAULT "nextval"('"sys_log_id_seq"'::"regclass");

ALTER TABLE ONLY "user" ALTER COLUMN "id" SET DEFAULT "nextval"('"user_id_seq"'::"regclass");

ALTER TABLE ONLY "whiteIP" ALTER COLUMN "id" SET DEFAULT "nextval"('"whiteIP_id_seq"'::"regclass");

ALTER TABLE ONLY "issue"
    ADD CONSTRAINT "issue_primary_key_id" PRIMARY KEY ("id");

ALTER TABLE ONLY "sys_client"
    ADD CONSTRAINT "sys_client_primary_key_id" PRIMARY KEY ("id");

ALTER TABLE ONLY "sys_log"
    ADD CONSTRAINT "sys_log_primary_key_id" PRIMARY KEY ("id");

ALTER TABLE ONLY "sys_client"
    ADD CONSTRAINT "unique_code" UNIQUE ("code");

ALTER TABLE ONLY "user_issue"
    ADD CONSTRAINT "user_issue_primary_key" PRIMARY KEY ("userId", "issueId");

ALTER TABLE ONLY "user"
    ADD CONSTRAINT "user_primary_key_id" PRIMARY KEY ("id");

ALTER TABLE ONLY "whiteIP"
    ADD CONSTRAINT "white_ip_primary_key_id" PRIMARY KEY ("id");

ALTER TABLE ONLY "issue"
    ADD CONSTRAINT "issue_sys_client_foreign_key" FOREIGN KEY ("sys_clientId") REFERENCES "sys_client"("id");

ALTER TABLE ONLY "user_issue"
    ADD CONSTRAINT "user_issue_issue_foreign_key" FOREIGN KEY ("issueId") REFERENCES "issue"("id");

ALTER TABLE ONLY "user_issue"
    ADD CONSTRAINT "user_issue_user_foreign_key" FOREIGN KEY ("userId") REFERENCES "user"("id");

ALTER TABLE ONLY "whiteIP"
    ADD CONSTRAINT "white_ip_foreign_key_user_id" FOREIGN KEY ("userId") REFERENCES "user"("id");
