-- MediFlash SOS+ Database Schema
-- PHP 8 + MySQL 5.7+ compatible

-- Drop existing tables if they exist (for clean setup)
DROP TABLE IF EXISTS ledger;
DROP TABLE IF EXISTS wallets;
DROP TABLE IF EXISTS order_events;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS pharmacy_stock;
DROP TABLE IF EXISTS medicines;
DROP TABLE IF EXISTS couriers;
DROP TABLE IF EXISTS pharmacies;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS settings;

-- Create users table
CREATE TABLE users (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    role ENUM('client', 'pharmacy', 'courier', 'admin') NOT NULL,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20) NOT NULL,
    email VARCHAR(255) UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    lat DECIMAL(10,8) NULL,
    lng DECIMAL(11,8) NULL,
    address TEXT NULL,
    active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_users_email (email),
    INDEX idx_users_role (role)
);

-- Create pharmacies table
CREATE TABLE pharmacies (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    user_id VARCHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    lat DECIMAL(10,8) NOT NULL,
    lng DECIMAL(11,8) NOT NULL,
    address TEXT NOT NULL,
    verified BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_pharmacies_user (user_id),
    INDEX idx_pharmacies_location (lat, lng),
    INDEX idx_pharmacies_verified (verified)
);

-- Create couriers table
CREATE TABLE couriers (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    user_id VARCHAR(36) NOT NULL,
    vehicle_type VARCHAR(50) DEFAULT 'motorcycle',
    lat DECIMAL(10,8) NULL,
    lng DECIMAL(11,8) NULL,
    active BOOLEAN DEFAULT TRUE,
    available BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_couriers_user (user_id),
    INDEX idx_couriers_location (lat, lng),
    INDEX idx_couriers_availability (active, available)
);

-- Create medicines table
CREATE TABLE medicines (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(255) NOT NULL,
    atc_code VARCHAR(20) NULL,
    INDEX idx_medicines_name (name),
    FULLTEXT KEY ft_medicines_name (name)
);

-- Create pharmacy stock table
CREATE TABLE pharmacy_stock (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    pharmacy_id VARCHAR(36) NOT NULL,
    medicine_id VARCHAR(36) NOT NULL,
    stock INT DEFAULT 0,
    unit_price INT NOT NULL COMMENT 'Price in FCFA centimes',
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id) ON DELETE CASCADE,
    FOREIGN KEY (medicine_id) REFERENCES medicines(id) ON DELETE CASCADE,
    UNIQUE KEY uk_pharmacy_medicine (pharmacy_id, medicine_id),
    INDEX idx_stock_pharmacy (pharmacy_id),
    INDEX idx_stock_medicine (medicine_id),
    INDEX idx_stock_available (stock)
);

-- Create orders table
CREATE TABLE orders (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    client_id VARCHAR(36) NULL COMMENT 'NULL for anonymous orders',
    pharmacy_id VARCHAR(36) NULL,
    courier_id VARCHAR(36) NULL,
    medicine_id VARCHAR(36) NULL,
    medicine_name VARCHAR(255) NOT NULL,
    qty INT DEFAULT 1,
    med_price_total INT DEFAULT 0 COMMENT 'Total medicine price in FCFA',
    service_fee INT DEFAULT 500 COMMENT 'MediFlash service fee in FCFA',
    delivery_fee INT DEFAULT 600 COMMENT 'Delivery fee in FCFA',
    distance_km DECIMAL(6,2) NULL,
    status ENUM('CREATED', 'PHARMACY_CONFIRMED', 'COURIER_ASSIGNED', 'PICKED_UP', 'DELIVERED', 'CLOSED') DEFAULT 'CREATED',
    client_lat DECIMAL(10,8) NOT NULL,
    client_lng DECIMAL(11,8) NOT NULL,
    client_address TEXT NOT NULL,
    client_phone VARCHAR(20) NOT NULL,
    prescription_url TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE SET NULL,
    FOREIGN KEY (pharmacy_id) REFERENCES pharmacies(id) ON DELETE SET NULL,
    FOREIGN KEY (courier_id) REFERENCES couriers(id) ON DELETE SET NULL,
    FOREIGN KEY (medicine_id) REFERENCES medicines(id) ON DELETE SET NULL,
    INDEX idx_orders_client (client_id),
    INDEX idx_orders_pharmacy (pharmacy_id),
    INDEX idx_orders_courier (courier_id),
    INDEX idx_orders_status (status),
    INDEX idx_orders_created (created_at),
    INDEX idx_orders_location (client_lat, client_lng)
);

-- Create order events table for tracking
CREATE TABLE order_events (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    order_id VARCHAR(36) NOT NULL,
    event VARCHAR(100) NOT NULL,
    meta JSON NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_events_order (order_id),
    INDEX idx_events_created (created_at)
);

-- Create wallets table for internal accounting
CREATE TABLE wallets (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    owner_type ENUM('PHARMACY', 'COURIER', 'MEDIFLASH') NOT NULL,
    owner_id VARCHAR(36) NULL COMMENT 'NULL for MEDIFLASH wallet',
    balance INT DEFAULT 0 COMMENT 'Balance in FCFA',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_owner_wallet (owner_type, owner_id),
    INDEX idx_wallets_owner (owner_type, owner_id)
);

-- Create ledger table for transaction tracking
CREATE TABLE ledger (
    id VARCHAR(36) PRIMARY KEY DEFAULT (UUID()),
    order_id VARCHAR(36) NOT NULL,
    entry_type ENUM('DEBIT', 'CREDIT') NOT NULL,
    account ENUM('PHARMACY', 'COURIER', 'MEDIFLASH', 'CLIENT') NOT NULL,
    amount INT NOT NULL COMMENT 'Amount in FCFA',
    note TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    INDEX idx_ledger_order (order_id),
    INDEX idx_ledger_account (account),
    INDEX idx_ledger_created (created_at)
);

-- Create settings table for configuration
CREATE TABLE settings (
    `key` VARCHAR(100) PRIMARY KEY,
    `value` TEXT NOT NULL,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default settings
INSERT INTO settings (`key`, `value`) VALUES
('SERVICE_FEE', '500'),
('DELIVERY_MIN', '600'),
('DELIVERY_PER_KM', '100'),
('PHARMACY_BONUS_FROM_DELIVERY', '100');

-- Create MEDIFLASH wallet
INSERT INTO wallets (owner_type, owner_id, balance) VALUES ('MEDIFLASH', NULL, 0);
