CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50),
    password VARCHAR(255),
    role ENUM('ADMIN','PHARMACIST','STORE_KEEPER')
);
CREATE TABLE drugs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    drug_name VARCHAR(150),
    generic_name VARCHAR(150),
    batch_no VARCHAR(50),
    stock INT DEFAULT 0,
    min_stock INT DEFAULT 20,
    expiry_date DATE,
    rack_location VARCHAR(50),
    supplier VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    CREATE TABLE stock_ledger (
    id INT AUTO_INCREMENT PRIMARY KEY,
    drug_id INT,
    type ENUM('IN','OUT'),
    quantity INT,
    department VARCHAR(50),
    reference_no VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE op_pharmacy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id VARCHAR(50),
    drug_id INT,
    qty INT,
    issued_by VARCHAR(50),
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE ip_pharmacy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ward VARCHAR(50),
    drug_id INT,
    qty INT,
    issued_by VARCHAR(50),
    date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);