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

CREATE TABLE IF NOT EXISTS branches (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    address VARCHAR(255) DEFAULT '',
    phone VARCHAR(30) DEFAULT '',
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(150) NOT NULL,
    username VARCHAR(60) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','pharmacist') DEFAULT 'pharmacist',
    branch_id INT DEFAULT NULL,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS medicines (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    barcode VARCHAR(100) DEFAULT '',
    category_id INT DEFAULT NULL,
    buying_price DECIMAL(10,2) DEFAULT 0,
    selling_price DECIMAL(10,2) DEFAULT 0,
    description TEXT,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS inventory (
    id INT AUTO_INCREMENT PRIMARY KEY,
    medicine_id INT NOT NULL,
    branch_id INT NOT NULL,
    quantity INT DEFAULT 0,
    min_quantity INT DEFAULT 10,
    expiry_date DATE DEFAULT NULL,
    batch_number VARCHAR(100) DEFAULT '',
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_med_branch (medicine_id, branch_id),
    FOREIGN KEY (medicine_id) REFERENCES medicines(id),
    FOREIGN KEY (branch_id) REFERENCES branches(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) NOT NULL UNIQUE,
    branch_id INT NOT NULL,
    user_id INT NOT NULL,
    subtotal DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) DEFAULT 0,
    amount_paid DECIMAL(10,2) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sale_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    medicine_id INT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE CASCADE,
    FOREIGN KEY (medicine_id) REFERENCES medicines(id)
) ENGINE=InnoDB;

-- ── Seed Data ──────────────────────────────────────────────────────
INSERT INTO branches (name, address, phone) VALUES
('الفرع الرئيسي', 'شارع التحرير، القاهرة', '01000000001'),
('فرع المهندسين', 'شارع جامعة الدول، الجيزة', '01000000002'),
('فرع المعادي', 'شارع 9، المعادي', '01000000003');

-- password for all users = "password"
INSERT INTO users (full_name, username, password, role, branch_id) VALUES
('مدير النظام', 'admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 1),
('علي محمد',    'ali',   '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'pharmacist', 1),
('فاطمة أحمد', 'fatima','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'pharmacist', 2);

INSERT INTO categories (name) VALUES
('مسكنات'),('مضادات حيوية'),('قلب وأوعية'),('سكر وضغط'),('فيتامينات'),('جهاز هضمي'),('أعصاب'),('جلدية'),('أطفال'),('عيون وأنف');

INSERT INTO medicines (name, barcode, category_id, buying_price, selling_price) VALUES
('باراسيتامول 500mg',  '6223001001', 1,  3.00,  6.00),
('إيبوبروفين 400mg',   '6223001002', 1,  4.00,  8.00),
('أموكسيسيلين 500mg',  '6223001003', 2,  8.00, 15.00),
('أزيثروميسين 500mg',  '6223001004', 2, 18.00, 32.00),
('أتورفاستاتين 20mg',  '6223001005', 3, 12.00, 22.00),
('إنالابريل 5mg',      '6223001006', 3,  7.00, 13.00),
('ميتفورمين 500mg',    '6223001007', 4,  5.00, 10.00),
('جليبنكلاميد 5mg',    '6223001008', 4,  6.00, 11.00),
('فيتامين سي 1000mg',  '6223001009', 5,  7.00, 14.00),
('أوميجا 3',           '6223001010', 5, 20.00, 35.00),
('أوميبرازول 20mg',    '6223001011', 6,  6.00, 12.00),
('سيرترالين 50mg',     '6223001012', 7, 15.00, 28.00),
('هيدروكورتيزون كريم', '6223001013', 8,  9.00, 16.00),
('برونامين شراب',      '6223001014', 9, 10.00, 18.00),
('ناظارين قطرة',       '6223001015', 10, 4.00,  8.00);

INSERT INTO inventory (medicine_id, branch_id, quantity, min_quantity, expiry_date) VALUES
(1,1,150,20,'2026-12-31'),(2,1,110,20,'2026-12-31'),(3,1,80,15,'2026-09-30'),
(4,1,45,10,'2027-02-28'),(5,1,60,10,'2027-03-31'),(6,1,65,10,'2027-04-30'),
(7,1,100,20,'2026-12-31'),(8,1,80,15,'2026-11-30'),(9,1,120,20,'2027-06-30'),
(10,1,35,8,'2027-05-31'),(11,1,90,15,'2026-10-31'),(12,1,40,10,'2027-01-31'),
(13,1,50,10,'2026-11-30'),(14,1,70,15,'2026-09-30'),(15,1,200,30,'2026-08-31'),
(1,2,100,20,'2026-12-31'),(3,2,50,15,'2026-09-30'),(7,2,80,20,'2026-12-31'),
(9,2,90,20,'2027-06-30'),(11,2,60,15,'2026-10-31'),
(1,3,8,20,'2026-12-31'),(7,3,5,20,'2026-12-31'),(11,3,60,15,'2026-10-31');
