scms_db.sql

-- ============================================================================
-- PostgreSQL / Supabase Schema for SCMS (Smart Clinic Management System)
-- ============================================================================
DROP TABLE IF EXISTS Tbl_Follow_Up CASCADE;
DROP TABLE IF EXISTS Tbl_Prescription_Template_Item CASCADE;
DROP TABLE IF EXISTS Tbl_Prescription_Template CASCADE;
DROP TABLE IF EXISTS Tbl_Role_Permission CASCADE;
DROP TABLE IF EXISTS Tbl_Permission CASCADE;
DROP TABLE IF EXISTS Tbl_Payment CASCADE;
DROP TABLE IF EXISTS Tbl_Prescription_Item_Schedule CASCADE;
DROP TABLE IF EXISTS Tbl_Prescription_Item CASCADE;
DROP TABLE IF EXISTS Tbl_Medicine_Batch CASCADE;
DROP TABLE IF EXISTS Tbl_Medicine CASCADE;
DROP TABLE IF EXISTS Tbl_Medicine_Category CASCADE;
DROP TABLE IF EXISTS Tbl_Prescription CASCADE;
DROP TABLE IF EXISTS Tbl_Disease CASCADE;
DROP TABLE IF EXISTS Tbl_Appointment CASCADE;
DROP TABLE IF EXISTS Tbl_Patient CASCADE;
DROP TABLE IF EXISTS Tbl_Notification CASCADE;
DROP TABLE IF EXISTS Tbl_User_Role CASCADE;
DROP TABLE IF EXISTS Tbl_User_Token CASCADE;
DROP TABLE IF EXISTS Tbl_User CASCADE;

-- 1. Create Tables
-- ----------------------------------------------------------------------------

CREATE TABLE Tbl_User (
    user_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    mobile_no VARCHAR(50) UNIQUE,
    email VARCHAR(100) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_User_Token (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    token_hash VARCHAR(500) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    revoked BOOLEAN NOT NULL DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Tbl_User_Role (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    role VARCHAR(50) NOT NULL
);

CREATE TABLE Tbl_Patient (
    patient_id SERIAL PRIMARY KEY,
    user_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    mobile_no VARCHAR(50),
    email VARCHAR(100),
    date_of_birth DATE,
    gender VARCHAR(20),
    blood_type VARCHAR(5),
    address TEXT,
    allergies TEXT,
    chronic_conditions TEXT,
    past_surgeries TEXT,
    family_history TEXT,
    vaccination_history TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Appointment (
    id SERIAL PRIMARY KEY,
    appointment_code VARCHAR(50) UNIQUE NOT NULL,
    patient_id INT NOT NULL,
    datetime TIMESTAMP NOT NULL,
    status VARCHAR(50) NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE Tbl_Disease (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL,
    description VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Prescription (
    id SERIAL PRIMARY KEY,
    appointment_id INT NOT NULL,
    patient_id INT NOT NULL,
    disease_id INT,
    weight_kg DOUBLE PRECISION,
    blood_pressure_systolic INT,
    blood_pressure_diastolic INT,
    notes TEXT,
    temperature_c DOUBLE PRECISION,
    pulse_bpm INT,
    spo2_percent INT,
    height_cm DOUBLE PRECISION,
    bmi DOUBLE PRECISION,
    lab_test_requests TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Medicine_Category (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE Tbl_Medicine (
    medicine_id SERIAL PRIMARY KEY,
    category_id INT,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    image_url VARCHAR(500),
    image_id VARCHAR(255),
    unit_price DECIMAL(10,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Medicine_Batch (
    id SERIAL PRIMARY KEY,
    med_id INT NOT NULL,
    batch_no VARCHAR(100) NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    expiry_date DATE NOT NULL,
    received_date DATE,
    supplier_name VARCHAR(255),
    status VARCHAR(50) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Prescription_Item (
    id SERIAL PRIMARY KEY,
    prescription_id INT NOT NULL,
    medicine_id INT NOT NULL,
    medicine_batch_id INT,
    dosage VARCHAR(100),
    days INT NOT NULL,
    quantity INT NOT NULL,
    instruction TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Prescription_Item_Schedule (
    id SERIAL PRIMARY KEY,
    prescription_item_id INT NOT NULL,
    start_date DATE,
    end_date DATE,
    dose_time VARCHAR(50),
    dose_quantity DECIMAL(10,2) NOT NULL,
    dose_unit VARCHAR(50),
    meal_timing VARCHAR(50),
    route VARCHAR(50),
    interval_hours INT,
    interval_days INT,
    day_of_week VARCHAR(20),
    is_as_needed BOOLEAN DEFAULT FALSE,
    body_site VARCHAR(100),
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Payment (
    id SERIAL PRIMARY KEY,
    appointment_id INT NOT NULL,
    prescription_id INT,
    amount DECIMAL(10,2) NOT NULL DEFAULT 0,
    tax DECIMAL(10,2) NOT NULL DEFAULT 0,
    charges DECIMAL(10,2) NOT NULL DEFAULT 0,
    payment_method VARCHAR(50) NOT NULL,
    payment_status VARCHAR(50) NOT NULL,
    payment_screenshot VARCHAR(500),
    paid_at TIMESTAMP,
    updated_at TIMESTAMP
);

CREATE TABLE Tbl_Permission (
    id SERIAL PRIMARY KEY,
    menu VARCHAR(100) NOT NULL,
    action VARCHAR(100) NOT NULL
);

CREATE TABLE Tbl_Role_Permission (
    id SERIAL PRIMARY KEY,
    role_id INT NOT NULL,
    permission_id INT NOT NULL
);

CREATE TABLE Tbl_Notification (
    id SERIAL PRIMARY KEY,
    user_id INT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    action_route VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Prescription_Template (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    disease_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Prescription_Template_Item (
    id SERIAL PRIMARY KEY,
    template_id INT NOT NULL,
    medicine_id INT NOT NULL,
    dosage VARCHAR(100),
    days INT NOT NULL,
    quantity INT NOT NULL,
    instruction TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

CREATE TABLE Tbl_Follow_Up (
    id SERIAL PRIMARY KEY,
    patient_id INT NOT NULL,
    appointment_id INT,
    prescription_id INT,
    due_at TIMESTAMP NOT NULL,
    recommendation TEXT NOT NULL,
    status VARCHAR(50) NOT NULL,
    completed_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP,
    delete_flag BOOLEAN DEFAULT FALSE
);

-- 2. Create Unique Constraints
-- ----------------------------------------------------------------------------

ALTER TABLE Tbl_User_Role
    ADD CONSTRAINT uq_user_role UNIQUE (user_id, role);

ALTER TABLE Tbl_Medicine_Batch
    ADD CONSTRAINT uq_med_batch UNIQUE (med_id, batch_no);

ALTER TABLE Tbl_Permission
    ADD CONSTRAINT uq_menu_action UNIQUE (menu, action);

ALTER TABLE Tbl_Role_Permission
    ADD CONSTRAINT uq_role_permission UNIQUE (role_id, permission_id);

-- 3. Create Foreign Key Relationships
-- ----------------------------------------------------------------------------

ALTER TABLE Tbl_User_Token
    ADD CONSTRAINT fk_token_user FOREIGN KEY (user_id) REFERENCES Tbl_User(user_id) ON DELETE CASCADE;

ALTER TABLE Tbl_User_Role
    ADD CONSTRAINT fk_role_user FOREIGN KEY (user_id) REFERENCES Tbl_User(user_id) ON DELETE CASCADE;

ALTER TABLE Tbl_Patient
    ADD CONSTRAINT fk_patient_user FOREIGN KEY (user_id) REFERENCES Tbl_User(user_id) ON DELETE CASCADE;

ALTER TABLE Tbl_Notification
    ADD CONSTRAINT fk_notification_user FOREIGN KEY (user_id) REFERENCES Tbl_User(user_id) ON DELETE CASCADE;

ALTER TABLE Tbl_Appointment
    ADD CONSTRAINT fk_appointment_patient FOREIGN KEY (patient_id) REFERENCES Tbl_Patient(patient_id);

ALTER TABLE Tbl_Prescription
    ADD CONSTRAINT fk_prescription_appointment FOREIGN KEY (appointment_id) REFERENCES Tbl_Appointment(id);

ALTER TABLE Tbl_Prescription
    ADD CONSTRAINT fk_prescription_patient FOREIGN KEY (patient_id) REFERENCES Tbl_Patient(patient_id);

ALTER TABLE Tbl_Prescription
    ADD CONSTRAINT fk_prescription_disease FOREIGN KEY (disease_id) REFERENCES Tbl_Disease(id) ON DELETE SET NULL;

ALTER TABLE Tbl_Prescription_Item
    ADD CONSTRAINT fk_item_prescription FOREIGN KEY (prescription_id) REFERENCES Tbl_Prescription(id) ON DELETE CASCADE;

ALTER TABLE Tbl_Prescription_Item
    ADD CONSTRAINT fk_item_medicine FOREIGN KEY (medicine_id) REFERENCES Tbl_Medicine(medicine_id);

ALTER TABLE Tbl_Prescription_Item
    ADD CONSTRAINT fk_item_batch FOREIGN KEY (medicine_batch_id) REFERENCES Tbl_Medicine_Batch(id) ON DELETE SET NULL;

ALTER TABLE Tbl_Prescription_Item_Schedule
    ADD CONSTRAINT fk_schedule_item FOREIGN KEY (prescription_item_id) REFERENCES Tbl_Prescription_Item(id) ON DELETE CASCADE;

ALTER TABLE Tbl_Medicine
    ADD CONSTRAINT fk_medicine_category FOREIGN KEY (category_id) REFERENCES Tbl_Medicine_Category(id) ON DELETE SET NULL;

ALTER TABLE Tbl_Medicine_Batch
    ADD CONSTRAINT fk_batch_medicine FOREIGN KEY (med_id) REFERENCES Tbl_Medicine(medicine_id) ON DELETE CASCADE;

ALTER TABLE Tbl_Payment
    ADD CONSTRAINT fk_payment_appointment FOREIGN KEY (appointment_id) REFERENCES Tbl_Appointment(id);

ALTER TABLE Tbl_Payment
    ADD CONSTRAINT fk_payment_prescription FOREIGN KEY (prescription_id) REFERENCES Tbl_Prescription(id) ON DELETE SET NULL;

ALTER TABLE Tbl_Role_Permission
    ADD CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES Tbl_User_Role(id) ON DELETE CASCADE;

ALTER TABLE Tbl_Role_Permission
    ADD CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES Tbl_Permission(id) ON DELETE CASCADE;

ALTER TABLE Tbl_Prescription_Template
    ADD CONSTRAINT fk_template_disease FOREIGN KEY (disease_id) REFERENCES Tbl_Disease(id);

ALTER TABLE Tbl_Prescription_Template_Item
    ADD CONSTRAINT fk_template_item_template FOREIGN KEY (template_id) REFERENCES Tbl_Prescription_Template(id) ON DELETE CASCADE;

ALTER TABLE Tbl_Prescription_Template_Item
    ADD CONSTRAINT fk_template_item_medicine FOREIGN KEY (medicine_id) REFERENCES Tbl_Medicine(medicine_id);

ALTER TABLE Tbl_Follow_Up
    ADD CONSTRAINT fk_follow_up_patient FOREIGN KEY (patient_id) REFERENCES Tbl_Patient(patient_id);

ALTER TABLE Tbl_Follow_Up
    ADD CONSTRAINT fk_follow_up_appointment FOREIGN KEY (appointment_id) REFERENCES Tbl_Appointment(id) ON DELETE SET NULL;

ALTER TABLE Tbl_Follow_Up
    ADD CONSTRAINT fk_follow_up_prescription FOREIGN KEY (prescription_id) REFERENCES Tbl_Prescription(id) ON DELETE SET NULL;

-- 4. PostgreSQL / Supabase Column Documentation (Comments)
-- ----------------------------------------------------------------------------

COMMENT ON COLUMN Tbl_User_Role.role IS 'admin / user';
COMMENT ON COLUMN Tbl_Patient.user_id IS 'User can create family member patient profile';
COMMENT ON COLUMN Tbl_Appointment.status IS 'pending / confirmed / cancelled / completed';
COMMENT ON COLUMN Tbl_Medicine_Batch.status IS 'active / expired / disposed';
COMMENT ON COLUMN Tbl_Prescription_Item_Schedule.dose_time IS 'morning / afternoon / evening / night / bedtime / custom';
COMMENT ON COLUMN Tbl_Prescription_Item_Schedule.dose_unit IS 'tablet / capsule / ml / drop / puff / injection';
COMMENT ON COLUMN Tbl_Prescription_Item_Schedule.meal_timing IS 'before_meal / after_meal / with_meal / anytime';
COMMENT ON COLUMN Tbl_Prescription_Item_Schedule.route IS 'oral / topical / injection / eye_drop / ear_drop / inhalation';
COMMENT ON COLUMN Tbl_Prescription_Item_Schedule.interval_hours IS 'Every X hours';
COMMENT ON COLUMN Tbl_Prescription_Item_Schedule.interval_days IS 'Every X days';
COMMENT ON COLUMN Tbl_Prescription_Item_Schedule.is_as_needed IS 'Take when needed';
COMMENT ON COLUMN Tbl_Prescription_Item_Schedule.body_site IS 'left eye / right ear / skin area';
COMMENT ON COLUMN Tbl_Payment.payment_method IS 'cash / online';
COMMENT ON COLUMN Tbl_Payment.payment_status IS 'pending / paid / partial / failed / refunded';
COMMENT ON COLUMN Tbl_Follow_Up.status IS 'pending / completed';