-- =========================================================
-- LoungeOS — Full CartcelSystem feature parity additions
-- Run this once on your existing LoungeOS database.
-- =========================================================

-- ---------------------------------------------------------
-- Granular staff permissions
-- ---------------------------------------------------------
CREATE TABLE staff_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    permission_key VARCHAR(50) NOT NULL,
    UNIQUE KEY unique_staff_perm (staff_id, permission_key),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- ---------------------------------------------------------
-- Waiters (location-scoped) — separate from staff logins, just named for attribution
-- ---------------------------------------------------------
CREATE TABLE waiters (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    name VARCHAR(100) NOT NULL,
    phone VARCHAR(20) NULL,
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id)
);

-- ---------------------------------------------------------
-- Customers (shared across locations — a regular can visit any branch)
-- ---------------------------------------------------------
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    address TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE customer_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sale_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method ENUM('cash','card','transfer','pos') DEFAULT 'cash',
    staff_id INT NULL,
    paid_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (sale_id) REFERENCES sales(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- ---------------------------------------------------------
-- Extend sales: waiter, table, customer link, credit support
-- ---------------------------------------------------------
ALTER TABLE sales ADD COLUMN waiter_id INT NULL AFTER staff_id;
ALTER TABLE sales ADD COLUMN table_name VARCHAR(50) NULL AFTER waiter_id;
ALTER TABLE sales ADD COLUMN customer_id INT NULL AFTER table_name;
ALTER TABLE sales MODIFY payment_method ENUM('cash','card','transfer','pos','credit') NOT NULL DEFAULT 'cash';
ALTER TABLE sales ADD FOREIGN KEY (waiter_id) REFERENCES waiters(id);
ALTER TABLE sales ADD FOREIGN KEY (customer_id) REFERENCES customers(id);

-- ---------------------------------------------------------
-- Suppliers & Purchases (location-scoped — each location restocks its own supply)
-- ---------------------------------------------------------
CREATE TABLE suppliers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    phone VARCHAR(20) NULL,
    email VARCHAR(150) NULL,
    address TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE purchase_orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    supplier_id INT NULL,
    status ENUM('pending','received','cancelled') DEFAULT 'pending',
    order_date DATE NOT NULL,
    expected_date DATE NULL,
    received_date DATE NULL,
    notes TEXT NULL,
    staff_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

CREATE TABLE purchase_order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    po_id INT NOT NULL,
    product_id INT NULL,
    item_name VARCHAR(150) NOT NULL,
    qty INT NOT NULL,
    unit_cost DECIMAL(10,2) NOT NULL,
    subtotal DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE purchase_payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    po_id INT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    method ENUM('cash','card','transfer','pos') DEFAULT 'cash',
    staff_id INT NULL,
    paid_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (po_id) REFERENCES purchase_orders(id),
    FOREIGN KEY (staff_id) REFERENCES staff(id)
);

-- ---------------------------------------------------------
-- Day Close — cash reconciliation, per location
-- ---------------------------------------------------------
CREATE TABLE day_closings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    location_id INT NOT NULL,
    closing_date DATE NOT NULL,
    expected_cash DECIMAL(10,2) NOT NULL,
    counted_cash DECIMAL(10,2) NOT NULL,
    difference DECIMAL(10,2) NOT NULL,
    total_sales DECIMAL(10,2) NOT NULL,
    notes TEXT NULL,
    closed_by INT NOT NULL,
    closed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (location_id) REFERENCES locations(id),
    FOREIGN KEY (closed_by) REFERENCES staff(id)
);

-- ---------------------------------------------------------
-- Tax setting (business-wide, same pattern as HotelMS)
-- ---------------------------------------------------------
INSERT INTO settings (key_name, value) VALUES ('tax_enabled', '0');
INSERT INTO settings (key_name, value) VALUES ('tax_rate', '0');
INSERT INTO settings (key_name, value) VALUES ('tax_mode', 'exclusive');
ALTER TABLE sales ADD COLUMN tax_amount DECIMAL(10,2) NOT NULL DEFAULT 0 AFTER total;