schema_based_on_diagram.sql

-- Create patients table
CREATE TABLE patients(
    id INT GENERATED ALWAYS AS IDENTITY,
    name VARCHAR(255) NOT NULL,
    date_of_birth DATE NOT NULL,
    PRIMARY KEY (id),
);
-- Create invoices table
CREATE TABLE invoices(
    id INT GENERATED ALWAYS AS IDENTITY,
    total_amount DECIMAL(10,2) NOT NULL,
    generated_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    payed_at TIMESTAMP(0) WITHOUT TIME ZONE,
    medical_histories_id INT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_invoices_medical_histories FOREIGN KEY (medical_histories_id) REFERENCES medical_histories (id),
);
-- Create invoice_items table
CREATE TABLE invoice_items(
    id INT GENERATED ALWAYS AS IDENTITY,
    unit_price DECIMAL(10,2) NOT NULL,
    quantity INT NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    invoice_id INT NOT NULL,
    treatment_id INT NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_invoice_items_invoices FOREIGN KEY (invoice_id) REFERENCES invoices (id),
    CONSTRAINT fk_invoice_items_treatments FOREIGN KEY (treatment_id) REFERENCES treatments (id),
);

-- Create medical_histories table

CREATE TABLE medical_histories(
    id INT,
    admitted_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    patient_id INT NOT NULL,
    status VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_medical_histories_patients FOREIGN KEY (patient_id) REFERENCES patients (id),
);

-- Create treatments table

CREATE TABLE treatments(
    id INTEGER NOT NULL,
    type VARCHAR(255) NOT NULL,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (id),
);

-- Create medical_histories_treatments JOIN table

CREATE TABLE medical_histories_treatments(
    medical_history_id INT NOT NULL,
    treatment_id INT NOT NULL,
    CONSTRAINT fk_medical_histories_treatments_medical_histories 
    FOREIGN KEY (medical_history_id) REFERENCES medical_histories (id),
    CONSTRAINT fk_medical_histories_treatments_treatments 
    FOREIGN KEY (treatment_id) REFERENCES treatments (id)
);

-- CREATE INDEX on every foreign key column

-- Create index on invoice_items.invoice_id
CREATE INDEX idx_invoice_items_invoice_id ON invoice_items (invoice_id);

-- Create index on invoice_items.treatment_id
CREATE INDEX idx_invoice_items_treatment_id ON invoice_items (treatment_id);

-- Create index on invoices.medical_histories_id
CREATE INDEX idx_invoices_medical_histories_id ON invoices (medical_histories_id);

-- Create index on medical_histories.patient_id
CREATE INDEX idx_medical_histories_patient_id ON medical_histories (patient_id);

-- Create index on medical_histories_treatments.medical_history_id
CREATE INDEX idx_medical_histories_treatments_medical_history_id ON medical_histories_treatments (medical_history_id);

-- Create index on medical_histories_treatments.treatment_id
CREATE INDEX idx_medical_histories_treatments_treatment_id ON medical_histories_treatments (treatment_id);