-- Pharmacy POS & Inventory Management System
-- Database Schema for MySQL/MariaDB

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS `pharmacy_pos` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `pharmacy_pos`;

-- ----------------------------
-- Users & Roles
-- ----------------------------
CREATE TABLE `roles` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(50) NOT NULL UNIQUE,
    `permissions` JSON NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `users` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `role_id` INT UNSIGNED NOT NULL,
    `username` VARCHAR(50) NOT NULL UNIQUE,
    `email` VARCHAR(255) NOT NULL UNIQUE,
    `password_hash` VARCHAR(255) NOT NULL,
    `full_name` VARCHAR(100) NOT NULL,
    `phone` VARCHAR(20) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `last_login` TIMESTAMP NULL DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_users_role` (`role_id`),
    INDEX `idx_users_active` (`is_active`),
    CONSTRAINT `fk_users_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`)
) ENGINE=InnoDB;

-- ----------------------------
-- Medicine Categories & Manufacturers
-- ----------------------------
CREATE TABLE `categories` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL UNIQUE,
    `description` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `manufacturers` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(150) NOT NULL UNIQUE,
    `contact_info` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ----------------------------
-- Medicines (Master File)
-- ----------------------------
CREATE TABLE `medicines` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `barcode` VARCHAR(50) DEFAULT NULL,
    `name` VARCHAR(200) NOT NULL,
    `generic_name` VARCHAR(200) DEFAULT NULL,
    `category_id` INT UNSIGNED DEFAULT NULL,
    `manufacturer_id` INT UNSIGNED DEFAULT NULL,
    `unit` ENUM('strip','box','bottle','tablet','capsule','tube','sachet','vial','piece') NOT NULL DEFAULT 'piece',
    `cost_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `selling_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `tax_rate` DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    `low_stock_threshold` INT UNSIGNED NOT NULL DEFAULT 10,
    `shelf_location` VARCHAR(50) DEFAULT NULL,
    `requires_prescription` TINYINT(1) NOT NULL DEFAULT 0,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE INDEX `idx_medicines_barcode` (`barcode`),
    INDEX `idx_medicines_name` (`name`),
    INDEX `idx_medicines_generic` (`generic_name`),
    INDEX `idx_medicines_category` (`category_id`),
    INDEX `idx_medicines_manufacturer` (`manufacturer_id`),
    INDEX `idx_medicines_active` (`is_active`),
    CONSTRAINT `fk_medicines_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL,
    CONSTRAINT `fk_medicines_manufacturer` FOREIGN KEY (`manufacturer_id`) REFERENCES `manufacturers` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------
-- Batches (Expiry & Stock Tracking)
-- ----------------------------
CREATE TABLE `batches` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `medicine_id` INT UNSIGNED NOT NULL,
    `batch_number` VARCHAR(100) NOT NULL,
    `expiry_date` DATE NOT NULL,
    `quantity` INT NOT NULL DEFAULT 0,
    `cost_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `received_date` DATE NOT NULL,
    `supplier_info` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_batches_medicine` (`medicine_id`),
    INDEX `idx_batches_expiry` (`expiry_date`),
    INDEX `idx_batches_number` (`batch_number`),
    INDEX `idx_batches_fefo` (`medicine_id`, `expiry_date`, `quantity`),
    CONSTRAINT `fk_batches_medicine` FOREIGN KEY (`medicine_id`) REFERENCES `medicines` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------
-- Sales & Transactions
-- ----------------------------
CREATE TABLE `sales` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `invoice_number` VARCHAR(30) NOT NULL UNIQUE,
    `user_id` INT UNSIGNED NOT NULL,
    `customer_name` VARCHAR(100) DEFAULT NULL,
    `customer_phone` VARCHAR(20) DEFAULT NULL,
    `subtotal` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `discount_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `payment_method` ENUM('cash','card','mobile_money','split') NOT NULL DEFAULT 'cash',
    `amount_paid` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `change_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `status` ENUM('completed','refunded','voided') NOT NULL DEFAULT 'completed',
    `notes` TEXT DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_sales_user` (`user_id`),
    INDEX `idx_sales_date` (`created_at`),
    INDEX `idx_sales_status` (`status`),
    INDEX `idx_sales_invoice` (`invoice_number`),
    CONSTRAINT `fk_sales_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `sale_items` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `sale_id` INT UNSIGNED NOT NULL,
    `medicine_id` INT UNSIGNED NOT NULL,
    `batch_id` INT UNSIGNED NOT NULL,
    `quantity` INT NOT NULL,
    `unit_price` DECIMAL(12,2) NOT NULL,
    `tax_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `discount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `total` DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `idx_sale_items_sale` (`sale_id`),
    INDEX `idx_sale_items_medicine` (`medicine_id`),
    INDEX `idx_sale_items_batch` (`batch_id`),
    CONSTRAINT `fk_sale_items_sale` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_sale_items_medicine` FOREIGN KEY (`medicine_id`) REFERENCES `medicines` (`id`),
    CONSTRAINT `fk_sale_items_batch` FOREIGN KEY (`batch_id`) REFERENCES `batches` (`id`)
) ENGINE=InnoDB;

-- ----------------------------
-- Purchase Orders
-- ----------------------------
CREATE TABLE `purchase_orders` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `order_number` VARCHAR(30) NOT NULL UNIQUE,
    `supplier_name` VARCHAR(150) DEFAULT NULL,
    `status` ENUM('draft','ordered','received','cancelled') NOT NULL DEFAULT 'draft',
    `total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `notes` TEXT DEFAULT NULL,
    `created_by` INT UNSIGNED NOT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_po_created_by` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `purchase_order_items` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `purchase_order_id` INT UNSIGNED NOT NULL,
    `medicine_id` INT UNSIGNED NOT NULL,
    `quantity` INT NOT NULL,
    `cost_price` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    PRIMARY KEY (`id`),
    CONSTRAINT `fk_poi_order` FOREIGN KEY (`purchase_order_id`) REFERENCES `purchase_orders` (`id`) ON DELETE CASCADE,
    CONSTRAINT `fk_poi_medicine` FOREIGN KEY (`medicine_id`) REFERENCES `medicines` (`id`)
) ENGINE=InnoDB;

-- ----------------------------
-- Audit Logs
-- ----------------------------
CREATE TABLE `audit_logs` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `user_id` INT UNSIGNED DEFAULT NULL,
    `action` VARCHAR(100) NOT NULL,
    `entity_type` VARCHAR(50) NOT NULL,
    `entity_id` INT UNSIGNED DEFAULT NULL,
    `old_values` JSON DEFAULT NULL,
    `new_values` JSON DEFAULT NULL,
    `ip_address` VARCHAR(45) DEFAULT NULL,
    `user_agent` VARCHAR(255) DEFAULT NULL,
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_audit_user` (`user_id`),
    INDEX `idx_audit_action` (`action`),
    INDEX `idx_audit_entity` (`entity_type`, `entity_id`),
    INDEX `idx_audit_date` (`created_at`),
    CONSTRAINT `fk_audit_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ----------------------------
-- System Settings
-- ----------------------------
CREATE TABLE `settings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `setting_key` VARCHAR(100) NOT NULL UNIQUE,
    `setting_value` TEXT DEFAULT NULL,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- ----------------------------
-- Seed Data
-- ----------------------------
INSERT INTO `roles` (`name`, `permissions`) VALUES
('admin', '{"all": true}'),
('pharmacist', '{"pos": true, "inventory": true, "reports.expiry": true, "reports.sales": true}'),
('cashier', '{"pos": true, "reports.own_shift": true}');

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('pharmacy_name', 'PharmaCare Pharmacy'),
('pharmacy_address', '123 Health Street, Medical District'),
('pharmacy_phone', '+233 000 000 000'),
('pharmacy_email', 'info@pharmacare.com'),
('currency_symbol', 'GH₵'),
('tax_rate', '0'),
('receipt_width', '80'),
('expiry_alert_days', '90'),
('session_timeout_minutes', '30');

-- Default admin user (password: admin123)
INSERT INTO `users` (`role_id`, `username`, `email`, `password_hash`, `full_name`) VALUES
(1, 'admin', 'admin@pharmacare.com', '$2y$12$6RmdtHODkLzhAKw90fqPc.0ICwPP90ku8FB.ypS23CMdnmjL/a6PO', 'System Administrator');

SET FOREIGN_KEY_CHECKS = 1;