create_db.sql

CREATE SCHEMA IF NOT EXISTS justice_firm;

SET search_path TO justice_firm;

SET session_replication_role = 'replica';
DROP VIEW IF EXISTS lawyer_appointment_statistics;
DROP VIEW IF EXISTS lawyer_case_statistics;
DROP TABLE IF EXISTS "justice_firm"."user";
DROP TABLE IF EXISTS client;
DROP TABLE IF EXISTS lawyer;
DROP TABLE IF EXISTS administrator;
DROP TABLE IF EXISTS login_history;
DROP TABLE IF EXISTS case_type;
DROP TABLE IF EXISTS lawyer_specialization;
DROP TABLE IF EXISTS appointment;
DROP TABLE IF EXISTS "justice_firm"."case";
DROP TABLE IF EXISTS case_document;
DROP TABLE IF EXISTS "justice_firm"."group";
DROP TABLE IF EXISTS message;

DROP TYPE IF EXISTS USER_TYPE;
DROP TYPE IF EXISTS LAWYER_STATUS;
DROP TYPE IF EXISTS APPOINTMENT_STATUS;
DROP TYPE IF EXISTS CASE_STATUS;
SET session_replication_role = 'origin';

CREATE TYPE USER_TYPE AS ENUM ('client', 'lawyer', 'admin');
CREATE TYPE LAWYER_STATUS AS ENUM ('waiting', 'rejected', 'confirmed');
CREATE TYPE APPOINTMENT_STATUS AS ENUM ('waiting', 'rejected', 'confirmed');
CREATE TYPE CASE_STATUS AS ENUM ('waiting', 'open', 'closed');

CREATE TABLE "justice_firm"."user" (
	id            SERIAL PRIMARY KEY,
	name          VARCHAR(1024) NOT NULL,
	email         VARCHAR(1024) NOT NULL UNIQUE,
	phone         VARCHAR(1024),
	address       TEXT,
	password_hash VARCHAR(2048) NOT NULL,
	photo_path    VARCHAR(1024),
	type          USER_TYPE NOT NULL DEFAULT 'client'::USER_TYPE,
	gender        VARCHAR(16)   NOT NULL
);
CREATE INDEX ft_address ON "justice_firm"."user" USING GIN (TO_TSVECTOR('english'::REGCONFIG, address));
CREATE INDEX ft_email ON "justice_firm"."user" USING GIN (TO_TSVECTOR('english'::REGCONFIG, email));
CREATE INDEX ft_name ON "justice_firm"."user" USING GIN (TO_TSVECTOR('english'::REGCONFIG, name));
CREATE INDEX i_type ON "justice_firm"."user" USING BTREE (type);

CREATE TABLE client (
	id INT PRIMARY KEY,
	FOREIGN KEY (id)
		REFERENCES "justice_firm"."user" (id)
);

CREATE TABLE lawyer (
	id                 INT PRIMARY KEY,
	latitude           DECIMAL(6, 3) NOT NULL,
	longitude          DECIMAL(6, 3) NOT NULL,
	certification_link VARCHAR(1024) NOT NULL,
	status             LAWYER_STATUS DEFAULT 'waiting'::LAWYER_STATUS,
	rejection_reason   VARCHAR(1024) NULL,
	FOREIGN KEY (id)
		REFERENCES "justice_firm"."user" (id)
);
CREATE INDEX i_status ON "lawyer" USING BTREE (status);

CREATE TABLE administrator (
	id       INT PRIMARY KEY,
	job_post VARCHAR(1024) NOT NULL,
	FOREIGN KEY (id)
		REFERENCES "justice_firm"."user" (id)
);

CREATE TABLE login_history (
	id        BIGSERIAL PRIMARY KEY,
	user_id   INT       NOT NULL,
	timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	FOREIGN KEY (user_id)
		REFERENCES "justice_firm"."user" (id)
);

CREATE TABLE case_type (
	id   BIGSERIAL PRIMARY KEY,
	name VARCHAR(256) NOT NULL
);

CREATE TABLE lawyer_specialization (
	id           BIGSERIAL PRIMARY KEY,
	lawyer_id    INT NOT NULL,
	case_type_id INT NOT NULL,
	FOREIGN KEY (lawyer_id)
		REFERENCES lawyer (id),
	FOREIGN KEY (case_type_id)
		REFERENCES case_type (id)
);

CREATE TABLE appointment (
	id          BIGSERIAL PRIMARY KEY,
	client_id   INT                NOT NULL,
	lawyer_id   INT                NOT NULL,
	group_id    INT                NOT NULL,
	case_id     INT                NULL,
	description TEXT               NOT NULL,
	timestamp   TIMESTAMP          NULL,
	opened_on   TIMESTAMP          NOT NULL DEFAULT CURRENT_TIMESTAMP,
	status      APPOINTMENT_STATUS NOT NULL DEFAULT 'waiting'::APPOINTMENT_STATUS,
	FOREIGN KEY (client_id)
		REFERENCES client (id),
	FOREIGN KEY (lawyer_id)
		REFERENCES lawyer (id),
	CONSTRAINT IfStatusConfirmedThenTimestampIsNotNullCheck CHECK ( status != 'confirmed' OR timestamp IS NOT NULL )
);

CREATE TABLE "justice_firm"."case" (
	id          BIGSERIAL PRIMARY KEY,
	client_id   INT         NOT NULL,
	lawyer_id   INT         NOT NULL,
	type_id     INT         NOT NULL,
	group_id    INT         NOT NULL,
	description TEXT        NOT NULL,
	opened_on   TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP,
	status      CASE_STATUS NOT NULL DEFAULT 'waiting'::CASE_STATUS,
	FOREIGN KEY (client_id) REFERENCES client (id),
	FOREIGN KEY (lawyer_id) REFERENCES lawyer (id),
	FOREIGN KEY (type_id) REFERENCES case_type (id)
);

CREATE TABLE case_document (
	id             BIGSERIAL PRIMARY KEY,
	case_id        INT           NOT NULL,
	uploaded_on    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
	file_link      VARCHAR(2048) NOT NULL,
	file_mime      VARCHAR(1024) NOT NULL,
	file_name      VARCHAR(1024) NOT NULL,
	description    VARCHAR(1024) NOT NULL,
	uploaded_by_id INT           NOT NULL,
	FOREIGN KEY (case_id)
		REFERENCES "justice_firm"."case" (id),
	FOREIGN KEY (uploaded_by_id)
		REFERENCES "justice_firm"."user" (id)
);

CREATE TABLE "justice_firm"."group" (
	id        BIGSERIAL PRIMARY KEY,
	NAME      VARCHAR(1024) NOT NULL DEFAULT '',
	case_id   INT           NULL,
	client_id INT           NOT NULL,
	lawyer_id INT           NOT NULL,
	FOREIGN KEY (case_id) REFERENCES "justice_firm"."case" (id),
	FOREIGN KEY (client_id) REFERENCES client (id),
	FOREIGN KEY (lawyer_id) REFERENCES lawyer (id)
);

ALTER TABLE appointment
	ADD FOREIGN KEY (group_id)
		REFERENCES "justice_firm"."group" (id);
ALTER TABLE appointment
	ADD FOREIGN KEY (case_id)
		REFERENCES "justice_firm"."case" (id);
ALTER TABLE "justice_firm"."case"
	ADD FOREIGN KEY (group_id)
		REFERENCES "justice_firm"."group" (id);

CREATE VIEW lawyer_case_statistics AS
SELECT l.id                        AS lawyer_id,
       COUNT(c.id)                 AS total_cases,
       COUNT(DISTINCT c.client_id) AS total_clients
FROM lawyer                           l
LEFT OUTER JOIN "justice_firm"."case" c ON l.id = c.lawyer_id
GROUP BY l.id;

CREATE VIEW lawyer_appointment_statistics AS
SELECT l.id        AS lawyer_id,
       COUNT(CASE
	             WHEN a.status = 'waiting' THEN a.id
	       END)    AS waiting_appointments,
       COUNT(CASE
	             WHEN a.status = 'confirmed' THEN a.id
	       END)    AS confirmed_appointments,
       COUNT(CASE
	             WHEN a.status = 'rejected' THEN a.id
	       END)    AS rejected_appointments,
       COUNT(a.id) AS total_appointments
FROM lawyer                 l
LEFT OUTER JOIN appointment a ON l.id = a.lawyer_id
GROUP BY l.id;

INSERT INTO case_type(name)
VALUES ('Bankruptcy'),
       ('Business/Corporate'),
       ('Constitutional'),
       ('Criminal Defense'),
       ('Employment and Labor'),
       ('Entertainment'),
       ('Estate Planning'),
       ('Family'),
       ('Immigration'),
       ('Intellectual Property'),
       ('Personal Injury'),
       ('Tax');