· 4 years ago · Jul 23, 2021, 07:54 AM
1CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
2
3CREATE OR REPLACE FUNCTION update_modified_column()
4RETURNS TRIGGER AS $$
5BEGIN
6 NEW.updated_at = now();
7 RETURN NEW;
8END;
9$$ language 'plpgsql';
10
11create table files (
12 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
13 type TEXT NOT NULL CHECK (char_length(type) <= 20),
14 url TEXT NOT NULL CHECK (char_length(url) <= 255),
15 user_upload TEXT NOT NULL,
16 is_used bool DEFAULT true, -- ga perlu haruse
17 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
18 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
19 deleted_at TIMESTAMP WITH TIME ZONE
20);
21CREATE TRIGGER files BEFORE UPDATE ON files FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
22INSERT INTO files ("id", "type", "url", "user_upload", "is_used", "deleted_at")
23VALUES ('00000000-0000-0000-0000-000000000000', '', '', '', 't', now());
24
25create table countries (
26 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
27 name TEXT NOT NULL CHECK (char_length(name) <= 100),
28 country_code TEXT NOT NULL CHECK (char_length(country_code) <= 10),
29 flag_image_id uuid NOT NULL REFERENCES files(id) DEFAULT '00000000-0000-0000-0000-000000000000',
30 status BOOLEAN NOT NULL,
31 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
32 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
33 deleted_at TIMESTAMP WITH TIME ZONE
34);
35CREATE TRIGGER countries BEFORE UPDATE ON countries FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
36INSERT INTO countries (name, country_code, status) VALUES ('Indonesia', '62', true);
37INSERT INTO countries( "id", "name", "country_code", "flag_image_id", "status", "deleted_at" )
38VALUES ('00000000-0000-0000-0000-000000000000','','','00000000-0000-0000-0000-000000000000',true,now())
39
40create table provinces (
41 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
42 country_id uuid NOT NULL REFERENCES countries(id),
43 code TEXT NOT NULL CHECK (char_length(code) <= 50),
44 name TEXT NOT NULL CHECK (char_length(name) <= 50),
45 status BOOLEAN NOT NULL,
46 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
47 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
48 deleted_at TIMESTAMP WITH TIME ZONE
49);
50CREATE TRIGGER provinces BEFORE UPDATE ON provinces FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
51INSERT INTO provinces ("id", "country_id", "code", "name", "status", "deleted_at")
52VALUES ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '', '', 't', now());
53
54
55/* Enum type: 1 = Kota, 2 = Kabupaten */
56create table cities (
57 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
58 province_id uuid NOT NULL REFERENCES provinces(id),
59 code TEXT NOT NULL CHECK (char_length(code) <= 50),
60 name TEXT NOT NULL CHECK (char_length(name) <= 50),
61 type INT NOT NULL,
62 status BOOLEAN NOT NULL,
63 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
64 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
65 deleted_at TIMESTAMP WITH TIME ZONE
66);
67CREATE TRIGGER cities BEFORE UPDATE ON cities FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
68INSERT INTO cities ("id", "province_id", "code", "name", "type", "status", "deleted_at")
69VALUES ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '', '', 0, 't', now());
70
71create table districts (
72 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
73 city_id uuid NOT NULL REFERENCES cities(id),
74 code TEXT NOT NULL CHECK (char_length(code) <= 50),
75 name TEXT NOT NULL CHECK (char_length(name) <= 50),
76 status BOOLEAN NOT NULL,
77 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
78 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
79 deleted_at TIMESTAMP WITH TIME ZONE
80);
81CREATE TRIGGER districts BEFORE UPDATE ON districts FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
82INSERT INTO districts ("id", "city_id", "code", "name", "status", "deleted_at")
83VALUES ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '', '', 't', now());
84
85create table sub_districts (
86 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
87 district_id uuid NOT NULL REFERENCES districts(id),
88 code TEXT NOT NULL CHECK (char_length(code) <= 50),
89 name TEXT NOT NULL CHECK (char_length(name) <= 50),
90 postal_code TEXT NOT NULL CHECK (char_length(postal_code) <= 20),
91 status BOOLEAN NOT NULL,
92 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
93 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
94 deleted_at TIMESTAMP WITH TIME ZONE
95);
96CREATE TRIGGER sub_districts BEFORE UPDATE ON sub_districts FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
97INSERT INTO sub_districts ("id", "district_id", "code", "name", "postal_code", "status", "deleted_at")
98VALUES ('00000000-0000-0000-0000-000000000000', '00000000-0000-0000-0000-000000000000', '', '', '', 't', now());
99
100
101create table admin_menus (
102 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
103 code TEXT NOT NULL CHECK (char_length(code) <= 20),
104 name TEXT CHECK (char_length(name) <= 255),
105 status BOOLEAN NOT NULL DEFAULT true,
106 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
107 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
108 deleted_at TIMESTAMP WITH TIME ZONE
109);
110CREATE TRIGGER admin_menus BEFORE UPDATE ON admin_menus FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
111INSERT INTO admin_menus ("code","name") VALUES('master_admin','Master Admin');
112INSERT INTO admin_menus ("code","name") VALUES('master_category','Master Category');
113INSERT INTO admin_menus ("code","name") VALUES('master_homepage','Master Homepage');
114INSERT INTO admin_menus ("code","name") VALUES('master_seller','Master Seller');
115INSERT INTO admin_menus ("code","name") VALUES('master_rfq','Master RFQ');
116INSERT INTO admin_menus ("code","name") VALUES('master_faq','Master FAQ');
117
118
119create table admin_roles (
120 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
121 code TEXT NOT NULL CHECK (char_length(code) <= 20),
122 name TEXT CHECK (char_length(name) <= 255),
123 status BOOLEAN NOT NULL DEFAULT true,
124 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
125 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
126 deleted_at TIMESTAMP WITH TIME ZONE
127);
128CREATE TRIGGER admin_roles BEFORE UPDATE ON admin_roles FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
129INSERT INTO admin_roles("code","name") VALUES('superadmin','Superadmin')
130
131create table admin_role_menus (
132 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
133 admin_role_id uuid NOT NULL REFERENCES admin_roles(id),
134 admin_menu_id uuid NOT NULL REFERENCES admin_menus(id),
135 status BOOLEAN NOT NULL,
136 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
137 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
138 deleted_at TIMESTAMP WITH TIME ZONE
139);
140CREATE TRIGGER admin_role_menus BEFORE UPDATE ON admin_role_menus FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
141INSERT INTO admin_role_menus (admin_role_id,admin_menu_id,"status")
142SELECT def.id,am.id,true
143FROM admin_roles def
144CROSS JOIN admin_menus am
145
146
147create table admins (
148 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
149 code TEXT NOT NULL CHECK (char_length(code) <= 20),
150 name TEXT CHECK (char_length(name) <= 255),
151 email TEXT CHECK (char_length(email) <= 255),
152 password TEXT CHECK (char_length(password) <= 255),
153 profile_image_id uuid NOT NULL REFERENCES files(id),
154 admin_role_id uuid NOT NULL REFERENCES admin_roles(id),
155 status bool NOT NULL DEFAULT true,
156 email_valid_at TIMESTAMP WITH TIME ZONE NOT NULL,
157 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
158 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
159 deleted_at TIMESTAMP WITH TIME ZONE
160);
161CREATE TRIGGER admins BEFORE UPDATE ON admins FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
162
163create table categories (
164 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
165 parent_id uuid REFERENCES categories(id),
166 code TEXT NOT NULL CHECK (char_length(code) <= 20),
167 name TEXT CHECK (char_length(name) <= 255),
168 description TEXT CHECK (char_length(description) <= 500),
169 image_id uuid NOT NULL REFERENCES files(id),
170 sequence INTEGER NOT NULL,
171 status bool NOT NULL DEFAULT true,
172 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
173 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
174 deleted_at TIMESTAMP WITH TIME ZONE
175);
176CREATE TRIGGER categories BEFORE UPDATE ON categories FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
177
178
179create table banners (
180 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
181 name TEXT NOT NULL CHECK (char_length(name) <= 255),
182 url TEXT CHECK (char_length(url) <= 500),
183 sequence INTEGER NOT NULL,
184 start_at TIMESTAMP WITH TIME ZONE NOT NULL,
185 end_at TIMESTAMP WITH TIME ZONE NOT NULL,
186 image_id uuid NOT NULL REFERENCES files(id),
187 is_overwrite_banner bool NOT NULL,
188 is_new_tab bool NOT NULL DEFAULT 'false',
189 status bool NOT NULL,
190 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
191 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
192 deleted_at TIMESTAMP WITH TIME ZONE
193);
194CREATE TRIGGER banners BEFORE UPDATE ON banners FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
195
196create table widgets (
197 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
198 parent_id uuid REFERENCES widgets(id),
199 sequence INTEGER NOT NULL,
200 name TEXT CHECK (char_length(name) <= 255) DEFAULT '',
201 category_id uuid REFERENCES categories(id),
202 start_at TIMESTAMP WITH TIME ZONE NOT NULL,
203 end_at TIMESTAMP WITH TIME ZONE NOT NULL,
204 url TEXT CHECK (char_length(url) <= 500),
205 image_id uuid NOT NULL REFERENCES files(id),
206 status bool NOT NULL DEFAULT true,
207 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
208 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
209 deleted_at TIMESTAMP WITH TIME ZONE
210);
211CREATE TRIGGER widgets BEFORE UPDATE ON widgets FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
212
213create table business_fields (
214 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
215 code TEXT NOT NULL CHECK (char_length(code) <= 20),
216 name TEXT NOT NULL CHECK (char_length(name) <= 255),
217 description TEXT CHECK (char_length(description) <= 500) DEFAULT '',
218 status bool NOT NULL DEFAULT true,
219 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
220 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
221 deleted_at TIMESTAMP WITH TIME ZONE
222);
223CREATE TRIGGER business_fields BEFORE UPDATE ON business_fields FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
224
225create table sourcing_types (
226 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
227 parent_id uuid REFERENCES sourcing_types(id),
228 code TEXT NOT NULL CHECK (char_length(code) <= 20),
229 name TEXT NOT NULL CHECK (char_length(name) <= 255),
230 description TEXT CHECK (char_length(description) <= 500) DEFAULT '',
231 status bool NOT NULL DEFAULT true,
232 any_other bool NOT NULL DEFAULT false,
233 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
234 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
235 deleted_at TIMESTAMP WITH TIME ZONE
236);
237CREATE TRIGGER sourcing_types BEFORE UPDATE ON sourcing_types FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
238
239create table sourcing_purposes (
240 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
241 code TEXT NOT NULL CHECK (char_length(code) <= 20),
242 name TEXT NOT NULL CHECK (char_length(name) <= 255),
243 description TEXT CHECK (char_length(description) <= 500) DEFAULT '',
244 status bool NOT NULL DEFAULT true,
245 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
246 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
247 deleted_at TIMESTAMP WITH TIME ZONE
248);
249CREATE TRIGGER sourcing_purposes BEFORE UPDATE ON sourcing_purposes FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
250
251create table units (
252 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
253 code TEXT NOT NULL CHECK (char_length(code) <= 20),
254 name TEXT NOT NULL CHECK (char_length(name) <= 255),
255 unit_of_measure TEXT NOT NULL CHECK (char_length(name) <= 255),
256 description TEXT CHECK (char_length(description) <= 500) DEFAULT '',
257 status bool NOT NULL DEFAULT true,
258 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
259 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
260 deleted_at TIMESTAMP WITH TIME ZONE
261);
262CREATE TRIGGER units BEFORE UPDATE ON units FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
263
264create table trade_terms (
265 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
266 code TEXT NOT NULL CHECK (char_length(code) <= 20),
267 name TEXT NOT NULL CHECK (char_length(name) <= 255),
268 description TEXT CHECK (char_length(description) <= 500) DEFAULT '',
269 status bool NOT NULL DEFAULT true,
270 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
271 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
272 deleted_at TIMESTAMP WITH TIME ZONE
273);
274CREATE TRIGGER trade_terms BEFORE UPDATE ON trade_terms FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
275
276create table supplier_capabilities (
277 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
278 code TEXT NOT NULL CHECK (char_length(code) <= 20),
279 name TEXT NOT NULL CHECK (char_length(name) <= 255),
280 description TEXT CHECK (char_length(description) <= 500) DEFAULT '',
281 image_id uuid NOT NULL REFERENCES files(id),
282 status bool NOT NULL DEFAULT true,
283 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
284 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
285 deleted_at TIMESTAMP WITH TIME ZONE
286);
287CREATE TRIGGER supplier_capabilities BEFORE UPDATE ON supplier_capabilities FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
288
289create table faq (
290 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
291 code TEXT NOT NULL CHECK (char_length(code) <= 20),
292 name TEXT NOT NULL CHECK (char_length(name) <= 255),
293 question TEXT CHECK (char_length(question) <= 10000) DEFAULT '',
294 answer TEXT CHECK (char_length(answer) <= 10000) DEFAULT '',
295 status bool NOT NULL DEFAULT true,
296 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
297 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
298 deleted_at TIMESTAMP WITH TIME ZONE
299);
300CREATE TRIGGER faq BEFORE UPDATE ON faq FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
301
302create table users (
303 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
304 code TEXT NOT NULL CHECK (char_length(code) <= 20),
305 email TEXT CHECK (char_length(email) <= 255),
306 email_valid_at TIMESTAMP WITH TIME ZONE,
307 phone TEXT CHECK (char_length(phone) <= 255),
308 phone_valid_at TIMESTAMP WITH TIME ZONE,
309 name TEXT NOT NULL CHECK (char_length(name) <= 255),
310 password TEXT CHECK (char_length(password) <= 255),
311 profile_image_id uuid REFERENCES files(id) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
312 status TEXT NOT NULL CHECK (char_length(status) <= 20),
313 register_type TEXT NOT NULL CHECK (char_length(register_type) <= 100),
314 register_detail JSONB CHECK (char_length(register_detail::TEXT) <= 1000),
315 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
316 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
317 deleted_at TIMESTAMP WITH TIME ZONE
318);
319CREATE TRIGGER users BEFORE UPDATE ON users FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
320
321create table education_sellers (
322 id uuid PRIMARY KEY DEFAULT uuid_generate_v4 (),
323 code TEXT NOT NULL CHECK (char_length(code) <= 20),
324 title TEXT NOT NULL CHECK (char_length(description) <= 100),
325 sub_title TEXT NOT NULL CHECK (char_length(description) <= 100),
326 description TEXT NOT NULL CHECK (char_length(description) <= 500),
327 image_id uuid NOT NULL REFERENCES files(id),
328 sequence INTEGER NOT NULL,
329 status bool NOT NULL DEFAULT true,
330 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP ,
331 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
332 deleted_at TIMESTAMP WITH TIME ZONE
333);
334CREATE TRIGGER education_sellers BEFORE UPDATE ON education_sellers FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
335
336create table memberships (
337 id uuid PRIMARY KEY DEFAULT uuid_generate_v4 (),
338 code TEXT NOT NULL CHECK (char_length(code) <= 20),
339 name TEXT NOT NULL CHECK (char_length(description) <= 100),
340 price_year INTEGER NOT NULL,
341 product_posting INTEGER NOT NULL,
342 search_priority INTEGER NOT NULL,
343 product_showcase INTEGER NOT NULL,
344 answer_rfq INTEGER NOT NULL,
345 verified_icon bool NOT NULL DEFAULT true,
346 admin_setting INTEGER NOT NULL,
347 online_shop_setting bool NOT NULL DEFAULT false,
348 custom_sub_domain_website bool NOT NULL DEFAULT false,
349 direct_link_to_member_website bool NOT NULL DEFAULT false,
350 data_reporting bool NOT NULL DEFAULT true,
351 trade_show_online INTEGER NOT NULL
352 description TEXT NOT NULL CHECK (char_length(description) <= 500),
353 status bool NOT NULL DEFAULT true,
354 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP ,
355 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
356 deleted_at TIMESTAMP WITH TIME ZONE
357);
358CREATE TRIGGER memberships BEFORE UPDATE ON memberships FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
359
360/* status:
3611. verification_rocess
3623. rejected (+ reason_rejected)
3634. approved
364*/
365create table companies (
366 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
367 user_id uuid NOT NULL REFERENCES users(id),
368 name TEXT NOT NULL CHECK (char_length(name) <= 255),
369 email TEXT CHECK (char_length(email) <= 255),
370 website_url TEXT CHECK (char_length(website_url) <= 500),
371 since TIMESTAMP WITH TIME ZONE NOT NULL,
372 phone TEXT NOT NULL CHECK (char_length(phone) <= 255),
373 is_individual bool NOT NULL DEFAULT false,
374 business_type TEXT NOT NULL CHECK (char_length(business_type) <= 255),
375 business_field_id uuid NOT NULL REFERENCES business_fields(id),
376 sub_district_id uuid REFERENCES sub_districts(id) NOT NULL DEFAULT '00000000-0000-0000-0000-000000000000',
377 address TEXT CHECK (char_length(address) <= 500),
378 postal_code TEXT CHECK (char_length(postal_code) <= 50),
379 introduction TEXT CHECK (char_length(introduction) <= 5000),
380 introduction_video_id uuid REFERENCES files(id),
381 reject_reason TEXT CHECK (char_length(reject_reason) <= 255),
382 verified_at TIMESTAMP WITH TIME ZONE,
383 status TEXT NOT NULL CHECK (char_length(status) <= 50),
384 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
385 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
386 deleted_at TIMESTAMP WITH TIME ZONE
387);
388CREATE TRIGGER companies BEFORE UPDATE ON companies FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
389
390create table company_documents (
391 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
392 company_id uuid REFERENCES companies(id),
393 tax_registration_number_file_id uuid NOT NULL REFERENCES files(id),
394 id_card_file_id uuid NOT NULL REFERENCES files(id),
395 company_deed_file_id uuid NOT NULL REFERENCES files(id),
396 company_bank_account_file_id uuid NOT NULL REFERENCES files(id),
397 certificate_of_company_domicile_file_id uuid REFERENCES files(id),
398 certificate_of_company_registration_file_id uuid REFERENCES files(id),
399 letter_of_business_permit_file_id uuid REFERENCES files(id),
400 business_number_file_id uuid REFERENCES files(id),
401 reject_reason TEXT CHECK (char_length(reject_reason) <= 255),
402 verified_at TIMESTAMP WITH TIME ZONE,
403 status TEXT NOT NULL CHECK (char_length(status) <= 50),
404 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
405 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
406 deleted_at TIMESTAMP WITH TIME ZONE
407);
408CREATE TRIGGER company_documents BEFORE UPDATE ON company_documents FOR EACH ROW EXECUTE PROCEDURE update_modified_column();
409
410create table display_windows {
411 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
412 company_id uuid NOT NULL REFERENCES companies(id),
413 name TEXT NOT NULL CHECK (char_length(name) <= 255),
414 status bool NOT NULL,
415 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
416 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
417 deleted_at TIMESTAMP WITH TIME ZONE
418};
419CREATE TRIGGER display_windows BEFORE UPDATE ON display_windows F OR EACH ROW EXECUTE PROCEDURE update_modified_column();
420
421create table products (
422 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
423 company_id uuid NOT NULL REFERENCES companies(id),
424 display_window_id NOT NULL REFERENCES display_windows(id),
425 name TEXT NOT NULL CHECK (char_length(name) <= 255),
426 category_id uuid NOT NULL REFERENCES categories(id),
427 video_url TEXT CHECK (char_length(overview_description) <= 500),
428 overview_description TEXT NOT NULL CHECK (char_length(overview_description) <= 500),
429 weight NUMERIC(20,3),
430 made_in TEXT NOT NULL CHECK (char_length(made_in) <= 100),
431 sku TEXT CHECK (char_length(sku) <= 255),
432 status bool NOT NULL default true,
433 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
434 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
435 deleted_at TIMESTAMP WITH TIME ZONE
436);
437
438create table product_images (
439 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
440 product_id uuid NOT NULL REFERENCES products(id),
441 image_id uuid NOT NULL REFERENCES files(id),
442 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
443 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
444 deleted_at TIMESTAMP WITH TIME ZONE
445)
446
447create table product_prices (
448 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
449 product_id uuid NOT NULL REFERENCES products(id),
450 price NUMERIC(20,3) NOT NULL,
451 discount NUMERIC(20,3),
452 min_order INT NOT NULL,
453 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
454 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
455 deleted_at TIMESTAMP WITH TIME ZONE
456);
457
458create table stock_card (
459 id uuid PRIMARY KEY DEFAULT uuid_generate_v4(),
460 product_id uuid NOT NULL REFERENCES products(id),
461 type TEXT NOT NULL CHECK (char_length(type) <= 50), /* credit or debit*/
462 credit NUMERIC(20,3),
463 debit NUMERIC(20,3),
464 current_stock NUMERIC(20,3),
465 unit_id uuid NOT NULL REFERENCES units(id),
466 created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
467 updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
468 deleted_at TIMESTAMP WITH TIME ZONE
469);
470