-- Create database
CREATE DATABASE IF NOT EXISTS pharmacy_inventory;
USE pharmacy_inventory;

-- Create inventory table
CREATE TABLE IF NOT EXISTS inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    generic_name VARCHAR(100),
    ndc VARCHAR(20),
    sku VARCHAR(50) UNIQUE NOT NULL,
    dosage_strength VARCHAR(50),
    form VARCHAR(50),
    manufacturer VARCHAR(100),
    unit_of_measure VARCHAR(20),
    category VARCHAR(50),
    storage_requirements TEXT,
    quantity INT NOT NULL DEFAULT 0,
    reorder_point INT DEFAULT 10,
    max_stock_level INT,
    expiry_date DATE,
    lot_number VARCHAR(50),
    product_image VARCHAR(255),
    description TEXT,
    unit_price DECIMAL(10,2) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_product_name (product_name),
    INDEX idx_sku (sku),
    INDEX idx_ndc (ndc),
    INDEX idx_category (category),
    INDEX idx_manufacturer (manufacturer),
    INDEX idx_expiry_date (expiry_date),
    INDEX idx_quantity (quantity)
);

-- Create inventory_history table
CREATE TABLE IF NOT EXISTS inventory_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventory_id INT NOT NULL,
    user_id INT NOT NULL,
    action ENUM('add', 'remove', 'adjust') NOT NULL,
    quantity INT NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inventory_id) REFERENCES inventory(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_inventory_id (inventory_id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- Create audit_logs table
CREATE TABLE IF NOT EXISTS audit_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    action VARCHAR(50) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at)
);

-- Create barcodes table
CREATE TABLE IF NOT EXISTS barcodes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventory_id INT NOT NULL,
    barcode VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inventory_id) REFERENCES inventory(id) ON DELETE CASCADE,
    INDEX idx_barcode (barcode),
    INDEX idx_inventory_id (inventory_id)
);

-- Create receiving table
CREATE TABLE IF NOT EXISTS receiving (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventory_id INT NOT NULL,
    user_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    lot_number VARCHAR(50),
    expiry_date DATE,
    supplier VARCHAR(100),
    invoice_number VARCHAR(50),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inventory_id) REFERENCES inventory(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_inventory_id (inventory_id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- Create dispensing table
CREATE TABLE IF NOT EXISTS dispensing (
    id INT AUTO_INCREMENT PRIMARY KEY,
    inventory_id INT NOT NULL,
    user_id INT NOT NULL,
    quantity INT NOT NULL,
    prescription_number VARCHAR(50),
    patient_name VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (inventory_id) REFERENCES inventory(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_inventory_id (inventory_id),
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at)
);

-- Create suppliers table
CREATE TABLE IF NOT EXISTS suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    contact_person VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
);

-- Create categories table
CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create manufacturers table
CREATE TABLE IF NOT EXISTS manufacturers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    contact_person VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20),
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create settings table
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(50) UNIQUE NOT NULL,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create receiving_records table
CREATE TABLE IF NOT EXISTS receiving_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    supplier_id INT NOT NULL,
    receipt_date DATE NOT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (created_by) REFERENCES users(id)
);

-- Create receiving_items table
CREATE TABLE IF NOT EXISTS receiving_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    receiving_record_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    lot_number VARCHAR(50),
    expiration_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (receiving_record_id) REFERENCES receiving_records(id),
    FOREIGN KEY (product_id) REFERENCES inventory(id)
);

-- Create dispensing_records table
CREATE TABLE IF NOT EXISTS dispensing_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_name VARCHAR(100),
    dispensing_date DATE NOT NULL,
    prescription_number VARCHAR(50),
    dispensed_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dispensed_by) REFERENCES users(id)
);

-- Create dispensing_items table
CREATE TABLE IF NOT EXISTS dispensing_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    dispensing_record_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (dispensing_record_id) REFERENCES dispensing_records(id),
    FOREIGN KEY (product_id) REFERENCES inventory(id)
);

-- Insert default settings
INSERT INTO settings (setting_key, setting_value) VALUES
('company_name', 'Pharmacy Inventory System'),
('company_address', '123 Pharmacy Street'),
('company_phone', '(555) 123-4567'),
('company_email', 'info@pharmacy.com'),
('low_stock_threshold', '10'),
('expiry_warning_days', '30'),
('date_format', 'Y-m-d'),
('currency_symbol', '$'),
('timezone', 'UTC'); 