-- =========================================================
-- LoungeOS — Multi-location POS with offline support
-- Run once on a fresh database.
-- =========================================================

-- ---------------------------------------------------------
-- Locations — each is either a selling point or a central store
-- ---------------------------------------------------------
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type ENUM('outlet','store') NOT NULL DEFAULT 'outlet',   -- 'store' = warehouse, doesn't sell directly
    address VARCHAR(255) NULL,
    phone VARCHAR(20) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ---------------------------------------------------------
-- Staff — tied to exactly one location
-- ---------------------------------------------------------
CREATE TABLE staff (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin','manager','cashier') DEFAULT 'cashier',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id)
);
-- 'admin' role can see/manage every location; manager/cashier only see their own.
-- No default admin is inserted here — run setup.php once to create the first one.

-- ---------------------------------------------------------
-- Product catalog — shared across all locations (same product list everywhere)
-- ---------------------------------------------------------
CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_id INT NULL,
    name VARCHAR(150) NOT NULL,
    barcode VARCHAR(50) NULL UNIQUE,
    unit VARCHAR(30) DEFAULT 'pcs',
    price DECIMAL(10,2) NOT NULL,
    cost_price DECIMAL(10,2) NULL,
    reorder_level INT NOT NULL DEFAULT 5,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

-- ---------------------------------------------------------
-- Per-location stock — same product, different quantity at each location
-- ---------------------------------------------------------
CREATE TABLE location_stock (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 0,
    UNIQUE KEY unique_location_product (location_id, product_id),
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE stock_transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    product_id INT NOT NULL,
    change_qty INT NOT NULL,           -- positive = added, negative = removed
    reason VARCHAR(255) NOT NULL,      -- 'Sale', 'Restock', 'Transfer In', 'Transfer Out', 'Adjustment', etc.
    staff_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- ---------------------------------------------------------
-- Stock transfers between locations
-- ---------------------------------------------------------
CREATE TABLE stock_transfers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    from_location_id INT NOT NULL,
    to_location_id INT NOT NULL,
    status ENUM('pending','in_transit','received','cancelled') DEFAULT 'pending',
    notes TEXT NULL,
    initiated_by INT NULL,
    received_by INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    received_at DATETIME NULL,
    FOREIGN KEY (from_location_id) REFERENCES locations(id),
    FOREIGN KEY (to_location_id) REFERENCES locations(id),
    FOREIGN KEY (initiated_by) REFERENCES staff(id),
    FOREIGN KEY (received_by) REFERENCES staff(id)
);

CREATE TABLE stock_transfer_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    transfer_id INT NOT NULL,
    product_id INT NOT NULL,
    qty INT NOT NULL,
    FOREIGN KEY (transfer_id) REFERENCES stock_transfers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- ---------------------------------------------------------
-- Sales — offline-capable (client_uuid dedupes retried/offline syncs)
-- ---------------------------------------------------------
CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    client_uuid VARCHAR(64) NOT NULL UNIQUE,
    location_id INT NOT NULL,
    staff_id INT NOT NULL,
    customer_name VARCHAR(150) NULL,
    payment_method ENUM('cash','card','transfer','pos') NOT NULL DEFAULT 'cash',
    total DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL,        -- when the sale actually happened (may predate sync time)
    synced_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

CREATE TABLE sale_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    product_id INT NULL,
    product_name VARCHAR(150) NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    qty INT NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- ---------------------------------------------------------
-- Business-wide settings
-- ---------------------------------------------------------
CREATE TABLE settings (
    key_name VARCHAR(50) PRIMARY KEY,
    value VARCHAR(255)
);
INSERT INTO settings (key_name, value) VALUES ('business_name', 'Sweeet Fry\'s');