-- Create pharmacy_batches table
CREATE TABLE IF NOT EXISTS pharmacy_batches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    drug_id INT NOT NULL,
    batch_number VARCHAR(50) NOT NULL,
    expiry_date DATE NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    cost_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    sale_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    supplier_id INT NULL,
    received_date DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (drug_id) REFERENCES drugs(id) ON DELETE CASCADE,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
);

-- Create pharmacy_sales table
CREATE TABLE IF NOT EXISTS pharmacy_sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT NULL,
    visit_id INT NULL,
    total_amount DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    payment_method VARCHAR(50) DEFAULT 'cash',
    status VARCHAR(20) DEFAULT 'completed', -- completed, refunded, cancelled
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE SET NULL,
    FOREIGN KEY (visit_id) REFERENCES visits(id) ON DELETE SET NULL
);

-- Create pharmacy_sale_items table
CREATE TABLE IF NOT EXISTS pharmacy_sale_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    drug_id INT NOT NULL,
    batch_id INT NULL, -- Can be null if we track sales without specific batch selection (though we should enforce it for stock deduction)
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    total_price DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sale_id) REFERENCES pharmacy_sales(id) ON DELETE CASCADE,
    FOREIGN KEY (drug_id) REFERENCES drugs(id),
    FOREIGN KEY (batch_id) REFERENCES pharmacy_batches(id) ON DELETE SET NULL
);

-- Add stock management columns to drugs table
ALTER TABLE drugs ADD COLUMN IF NOT EXISTS min_stock_level INT DEFAULT 10;
ALTER TABLE drugs ADD COLUMN IF NOT EXISTS reorder_level INT DEFAULT 20;
ALTER TABLE drugs ADD COLUMN IF NOT EXISTS unit VARCHAR(50) DEFAULT 'pack';