init.sql

-- =========================
-- DROP (for reset)
-- =========================
DROP TABLE IF EXISTS audit_logs CASCADE;
DROP TABLE IF EXISTS transactions CASCADE;
DROP TABLE IF EXISTS batches CASCADE;
DROP TABLE IF EXISTS products CASCADE;
DROP TABLE IF EXISTS app_user CASCADE;

-- =========================
-- USERS
-- =========================
CREATE TABLE app_user (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL,
    role VARCHAR(20) NOT NULL CHECK (role IN ('ADMIN', 'PHARMACIAN', 'USER')),
    email VARCHAR(255),
    password VARCHAR(255) NOT NULL,
    active BOOLEAN DEFAULT TRUE
);

-- =========================
-- PRODUCTS
-- =========================
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    nsn_code VARCHAR(100) NOT NULL,
    description TEXT,
    minimum_stock_level INTEGER DEFAULT 0,
    active BOOLEAN DEFAULT TRUE,
    version BIGINT DEFAULT 0
);

-- =========================
-- BATCHES (LOT TRACKING)
-- =========================
CREATE TABLE batches (
    id BIGSERIAL PRIMARY KEY,
    product_id BIGINT NOT NULL,
    lot_number VARCHAR(100) NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    expiration_date TIMESTAMP NOT NULL,
    location VARCHAR(100) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'QUARANTINE', 'RETIRED')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    version BIGINT DEFAULT 0,

    CONSTRAINT fk_batch_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE CASCADE
);

CREATE INDEX idx_batch_lot_number ON batches(lot_number);
CREATE INDEX idx_batch_expiration_date ON batches(expiration_date);
CREATE INDEX idx_batch_product_id ON batches(product_id);
CREATE INDEX idx_batch_status ON batches(status);

-- =========================
-- TRANSACTIONS
-- =========================
CREATE TABLE transactions (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    batch_id BIGINT NOT NULL,
    type VARCHAR(10) NOT NULL CHECK (type IN ('IN', 'OUT', 'RETURN')),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    reason VARCHAR(500),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_transaction_user
        FOREIGN KEY (user_id)
        REFERENCES app_user(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_transaction_product
        FOREIGN KEY (product_id)
        REFERENCES products(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_transaction_batch
        FOREIGN KEY (batch_id)
        REFERENCES batches(id)
        ON DELETE CASCADE
);

CREATE INDEX idx_transaction_batch_id ON transactions(batch_id);
CREATE INDEX idx_transaction_user_id ON transactions(user_id);
CREATE INDEX idx_transaction_created_at ON transactions(created_at);
CREATE INDEX idx_transaction_product_id ON transactions(product_id);

-- =========================
-- AUDIT LOGS
-- =========================
CREATE TABLE audit_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT,
    action VARCHAR(20) NOT NULL CHECK (action IN ('CREATE', 'UPDATE', 'DELETE')),
    table_name VARCHAR(100) NOT NULL,
    old_value TEXT,
    new_value TEXT,
    ip_address VARCHAR(45),
    reason VARCHAR(500),
    timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_audit_user
        FOREIGN KEY (user_id)
        REFERENCES app_user(id)
        ON DELETE SET NULL
);

CREATE INDEX idx_audit_timestamp ON audit_logs(timestamp);
CREATE INDEX idx_audit_user_id ON audit_logs(user_id);
CREATE INDEX idx_audit_action ON audit_logs(action);
CREATE INDEX idx_audit_table_name ON audit_logs(table_name);

-- =========================
-- TEST DATA
-- =========================

-- USERS
INSERT INTO app_user (username, role, email, password, active) VALUES
('admin', 'ADMIN', 'admin@mstock.com', '$2a$10$dummyhash', TRUE),
('pharmacist_1', 'PHARMACIAN', 'pharm1@mstock.com', '$2a$10$dummyhash', TRUE),
('pharmacist_2', 'PHARMACIAN', 'pharm2@mstock.com', '$2a$10$dummyhash', TRUE);

-- PRODUCTS
INSERT INTO products (name, nsn_code, description, minimum_stock_level, active, version) VALUES
('Saline Solution 0.9%', 'NSN-001', 'IV saline fluid', 50, TRUE, 0),
('Surgical Gloves', 'NSN-002', 'Latex sterile gloves', 200, TRUE, 0),
('Syringe 5ml', 'NSN-003', 'Disposable syringe', 150, TRUE, 0),
('Paracetamol 500mg', 'NSN-004', 'Pain relief tablets', 100, TRUE, 0);

-- BATCHES
INSERT INTO batches (product_id, lot_number, quantity, expiration_date, location, status, version) VALUES
(1, 'LOT-SAL-001', 40, CURRENT_TIMESTAMP + INTERVAL '20 days', 'A1', 'ACTIVE', 0),
(1, 'LOT-SAL-002', 30, CURRENT_TIMESTAMP + INTERVAL '90 days', 'A2', 'ACTIVE', 0),
(1, 'LOT-SAL-003', 60, CURRENT_TIMESTAMP - INTERVAL '5 days', 'A3', 'RETIRED', 0),
(2, 'LOT-GLV-001', 150, CURRENT_TIMESTAMP + INTERVAL '180 days', 'B1', 'ACTIVE', 0),
(2, 'LOT-GLV-002', 75, CURRENT_TIMESTAMP + INTERVAL '30 days', 'B2', 'QUARANTINE', 0),
(3, 'LOT-SYR-001', 120, CURRENT_TIMESTAMP + INTERVAL '365 days', 'C1', 'ACTIVE', 0),
(3, 'LOT-SYR-002', 90, CURRENT_TIMESTAMP + INTERVAL '400 days', 'C2', 'ACTIVE', 0),
(4, 'LOT-PAR-001', 80, CURRENT_TIMESTAMP + INTERVAL '15 days', 'D1', 'ACTIVE', 0),
(4, 'LOT-PAR-002', 200, CURRENT_TIMESTAMP + INTERVAL '180 days', 'D2', 'ACTIVE', 0);

-- TRANSACTIONS
INSERT INTO transactions (user_id, product_id, batch_id, type, quantity, reason) VALUES
(1, 1, 1, 'IN', 40, 'Initial stock'),
(1, 1, 2, 'IN', 30, 'Initial stock'),
(1, 1, 3, 'IN', 60, 'Initial stock - now expired'),
(1, 2, 3, 'IN', 150, 'Initial stock'),
(1, 2, 4, 'IN', 75, 'Incoming shipment - under quarantine'),
(1, 3, 4, 'IN', 120, 'Initial stock'),
(1, 3, 5, 'IN', 90, 'Incoming shipment'),
(1, 4, 5, 'IN', 80, 'Initial stock'),
(1, 4, 6, 'IN', 200, 'Incoming shipment'),
(2, 1, 1, 'OUT', 5, 'Dispensed to ward A'),
(2, 1, 2, 'OUT', 10, 'Dispensed to ward B'),
(2, 4, 5, 'OUT', 10, 'Dispensed to ward B'),
(2, 2, 3, 'OUT', 25, 'Dispensed to OR'),
(2, 3, 4, 'OUT', 30, 'Dispensed to ward C'),
(3, 3, 4, 'OUT', 15, 'Dispensed to ER'),
(2, 1, 1, 'RETURN', 3, 'Returned from ward A - unused'),
(3, 4, 5, 'RETURN', 5, 'Returned from ER - unused');

-- AUDIT LOGS
INSERT INTO audit_logs (user_id, action, table_name, old_value, new_value, ip_address, reason) VALUES
(1, 'CREATE', 'products', NULL, '{"name":"Saline Solution 0.9%"}', '127.0.0.1', 'Initial product creation'),
(2, 'UPDATE', 'batches', '{"quantity":45}', '{"quantity":40}', '127.0.0.1', 'Stock adjustment'),
(1, 'DELETE', 'transactions', '{"id":10}', NULL, '127.0.0.1', 'Erroneous entry removed');