EqngXjNb

1const { Pool } = require('pg');
2const dotenv = require('dotenv');
3
4dotenv.config();
5
6const pool = new Pool({
7  connectionString: process.env.DATABASE_URL
8});
9
10pool.on('connect', () => {
11  console.log('connected to the db');
12});
13
14/**
15 * Create Tables
16 */
17const createTables = () => {
18  const createUsersTable = `
19    CREATE TABLE IF NOT EXISTS users(
20      id SERIAL PRIMARY KEY NOT NULL,
21      email VARCHAR(30) NOT NULL,
22      firstname VARCHAR (40) NOT NULL,
23      lastname VARCHAR(40) NOT NULL,
24      password VARCHAR(255) NOT NULL,
25      address VARCHAR(40) NOT NULL,
26      role INTEGER DEFAULT 0
27    );
28  `;
29  const createOrderTable = `
30    CREATE TABLE IF NOT EXISTS order(
31      id SERIAL PRIMARY KEY NOT NULL,
32      buyer_id SERIAL PRIMARY KEY NOT NULL,
33      car_id SERIAL PRIMARY KEY NOT NULL,
34      amount VARCHAR(14) NOT NULL,
35      status VARCHAR(13) DEFAULT 'pending' 
36    );
37  `;
38  const createCarsTable = `
39    CREATE TABLE IF NOT EXISTS cars(
40      id SERIAL PRIMARY KEY NOT NULL,
41      owner INTEGER REFERENCES users(id),
42      created_on TIMESTAMP WITH TIME ZONE DEFAULT now(),
43      state VARCHAR (40) NOT NULL,
44      status VARCHAR (40) NOT NULL,
45      price VARCHAR(14) NOT NULL,
46      manufacturer VARCHAR(40) NOT NULL,
47      model VARCHAR(14) NOT NULL,
48      body_type SERIAL PRIMARY KEY NOT NULL
49    );
50  `;
51  const createFlagTable = `
52    CREATE TABLE IF NOT EXISTS flag(
53      id SERIAL PRIMARY KEY NOT NULL,
54      car_id INTEGER REFERENCES cars(id),
55      created_on TIMESTAMP WITH TIME ZONE DEFAULT now(),
56      reason VARCHAR(40) NOT NULL,
57      description VARCHAR(40) NOT NULL
58    );
59  `;
60
61const tablesCreated = `${createUsersTable}${createOrderTable}${createCarsTable}${createFlagTable}`;
62
63  pool.query(tablesCreated)
64    .then((res) => {
65      console.log(res);
66      pool.end();
67    })
68    .catch((err) => {
69      console.log(err);
70      pool.end();
71    });
72}
73
74/**
75 * Drop Tables
76 */
77const dropTables = () => {
78  const dropUsers = 'DROP TABLE IF EXISTS users CASCADE; ';
79  const dropOrder = 'DROP TABLE IF EXISTS order CASCADE; ';
80  const dropCars = 'DROP TABLE IF EXISTS cars CASCADE; ';
81  const dropFlag = 'DROP TABLE IF EXISTS flag CASCADE; ';
82
83  const dropQuery = `${dropUsers}${dropOrder}${dropCars}${dropFlag}`;
84
85  pool.query(dropQuery)
86    .then((res) => {
87      console.log(res);
88      pool.end();
89    })
90    .catch((err) => {
91      console.log(err);
92      pool.end();
93    });
94}
95
96pool.on('remove', () => {
97  console.log('client removed');
98  process.exit(0);
99});
100
101module.exports = {
102  createTables,
103  dropTables
104};
105
106require('make-runnable');