sql-commands

create table doctor(
	id uuid NOT NULL primary key,
	name VARCHAR(50) not null,
	role VARCHAR(25) not null,
	data_register timestamp without time zone,
    data_update timestamp without time zone,
    id_user uuid,
    constraint chk_role check (role in ('SURGEON', 'ORTHOPEDIST',
    	'PHARMACIST', 'OTORHINOLARYNGOLOGIST', 'OPHTHALMOLOGIST'))
);

create table patient(
	id uuid not null primary key,
	name VARCHAR(50) not null,
	age INTEGER not null,
	address varchar(50),
	data_register timestamp without time zone,
    data_update timestamp without time zone,
    id_user uuid
);

create table appointment(
	id uuid not null primary key,
	fk_doctor uuid not null,
	fk_patient uuid not null,
	time TIMESTAMP not null,
	data_register timestamp without time zone,
    data_update timestamp without time zone,
    id_user uuid,
	CONSTRAINT c_appointment_doctor FOREIGN key (fk_doctor) REFERENCES doctor(id),
	constraint c_appointment_patient foreign key (fk_patient) references patient(id)
);

create table sys_user(
    id uuid not null primary key,
    login varchar(20) not null unique,
    pwd varchar(300) not null,
    roles varchar[]
);

ALTER TABLE doctor
ADD CONSTRAINT fk_doctor_sys_user
FOREIGN KEY (id_user)
REFERENCES sys_user (id);

ALTER TABLE patient
ADD CONSTRAINT fk_patient_sys_user
FOREIGN KEY (id_user)
REFERENCES sys_user (id);

ALTER TABLE appointment
ADD CONSTRAINT fk_appointment_sys_user
FOREIGN KEY (id_user)
REFERENCES sys_user (id);

ALTER TABLE sys_user ADD COLUMN email VARCHAR(150) DEFAULT 'emailo@example.com';
UPDATE sys_user SET email = 'email_padrao@example.com' WHERE email IS NULL;
ALTER TABLE sys_user ALTER COLUMN email SET NOT NULL;

ALTER TABLE sys_user ALTER COLUMN login TYPE VARCHAR(150);

create table client(
	id uuid not null primary key,
	client_id varchar(150) not null,
	client_secret varchar(400) not null,
	redirect_uri varchar(200) not null,
	scope varchar(50)
);

alter table client add column fk_sys_user uuid not null
ALTER TABLE client ADD CONSTRAINT client_fk_user foreign KEY (fk_sys_user) references sys_user(id);