trainingFull/schema.sql at main · work-owlo/trainingFull · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
CREATE TABLE IF NOT EXISTS public.auth_token
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    refresh_token text COLLATE pg_catalog."default" NOT NULL,
    access_token text COLLATE pg_catalog."default" NOT NULL,
    updated timestamp without time zone,
    CONSTRAINT auth_token_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.company
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    company_id text COLLATE pg_catalog."default" NOT NULL,
    company_name text COLLATE pg_catalog."default" NOT NULL,
    company_description text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT company_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.employee_user
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    user_id text COLLATE pg_catalog."default" NOT NULL,
    first_name text COLLATE pg_catalog."default" NOT NULL,
    last_name text COLLATE pg_catalog."default" NOT NULL,
    email text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT employee_user_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.invoice
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    invoice_id text COLLATE pg_catalog."default" NOT NULL,
    invoice_month integer NOT NULL,
    invoice_year integer NOT NULL,
    company_id text COLLATE pg_catalog."default" NOT NULL,
    compensation double precision NOT NULL,
    invoice_amount double precision NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT invoice_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.job_roles
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    company_id text COLLATE pg_catalog."default" NOT NULL,
    role_id text COLLATE pg_catalog."default" NOT NULL,
    role_name text COLLATE pg_catalog."default" NOT NULL,
    role_description text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    completion_rate double precision,
    average_score double precision,
    average_time double precision,
    CONSTRAINT job_roles_pkey PRIMARY KEY (id)
);


CREATE TABLE IF NOT EXISTS public.manager_permissions
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    user_id text COLLATE pg_catalog."default" NOT NULL,
    add_managers boolean DEFAULT false,
    edit_managers boolean DEFAULT false,
    delete_managers boolean DEFAULT false,
    read_managers boolean DEFAULT false,
    add_roles boolean DEFAULT false,
    edit_roles boolean DEFAULT false,
    delete_roles boolean DEFAULT false,
    add_shifts boolean DEFAULT false,
    edit_shifts boolean DEFAULT false,
    delete_shifts boolean DEFAULT false,
    edit_company boolean DEFAULT false,
    delete_company boolean DEFAULT false,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT manager_permissions_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.manager_user
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    user_id text COLLATE pg_catalog."default" NOT NULL,
    company_id text COLLATE pg_catalog."default",
    first_name text COLLATE pg_catalog."default" NOT NULL,
    last_name text COLLATE pg_catalog."default" NOT NULL,
    email text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT manager_user_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.module
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    module_id text COLLATE pg_catalog."default" NOT NULL,
    company_id text COLLATE pg_catalog."default" NOT NULL,
    module_title text COLLATE pg_catalog."default" NOT NULL,
    module_description text COLLATE pg_catalog."default" NOT NULL,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    tool_id text COLLATE pg_catalog."default" NOT NULL,
    access text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT module_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.role_module
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    module_id text COLLATE pg_catalog."default" NOT NULL,
    role_id text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT role_module_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.role_tools
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    role_id text COLLATE pg_catalog."default" NOT NULL,
    tool_id text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    rt_id text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT role_tools_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.section
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    section_id text COLLATE pg_catalog."default" NOT NULL,
    module_id text COLLATE pg_catalog."default" NOT NULL,
    section_title text COLLATE pg_catalog."default" NOT NULL,
    section_description text COLLATE pg_catalog."default" NOT NULL,
    minutes_to_complete double precision NOT NULL,
    training_order integer NOT NULL,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    updated timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT section_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.team
(
    employee_id text COLLATE pg_catalog."default",
    company_id text COLLATE pg_catalog."default",
    role_id text COLLATE pg_catalog."default" NOT NULL,
    employment_type text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    created timestamp with time zone DEFAULT CURRENT_TIMESTAMP,
    id_input text COLLATE pg_catalog."default",
    first_name text COLLATE pg_catalog."default",
    last_name text COLLATE pg_catalog."default",
    email text COLLATE pg_catalog."default" NOT NULL,
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    team_id text COLLATE pg_catalog."default",
    CONSTRAINT team_pkey PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS public.tools
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    tool_id text COLLATE pg_catalog."default" NOT NULL,
    tool_name text COLLATE pg_catalog."default" NOT NULL,
    tool_icon text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT tools_pkey PRIMARY KEY (id)
);


CREATE TABLE IF NOT EXISTS public.training
(
    id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    training_id text COLLATE pg_catalog."default" NOT NULL,
    team_id text COLLATE pg_catalog."default" NOT NULL,
    module_id text COLLATE pg_catalog."default" NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT training_pkey PRIMARY KEY (id)
);