-- ============================================================
--  Micro-Business Inventory Manager – Phase 1 Database Schema
-- ============================================================



-- ─── USERS ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)        NOT NULL,
    email       VARCHAR(150)        NOT NULL UNIQUE,
    password    VARCHAR(255)        NOT NULL,
    business_name VARCHAR(150)      DEFAULT NULL,
    created_at  TIMESTAMP           DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP           DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- ─── PRODUCTS ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS products (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT             NOT NULL,
    name            VARCHAR(200)    NOT NULL,
    description     TEXT            DEFAULT NULL,
    sku             VARCHAR(100)    DEFAULT NULL,
    barcode         VARCHAR(100)    DEFAULT NULL,
    category        VARCHAR(100)    DEFAULT NULL,
    buying_price    DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    selling_price   DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    quantity        INT             NOT NULL DEFAULT 0,
    low_stock_alert INT             NOT NULL DEFAULT 5,
    unit            VARCHAR(50)     DEFAULT 'piece',
    is_active       TINYINT(1)      DEFAULT 1,
    created_at      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_barcode (barcode)
);

-- ─── SALES ───────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sales (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    user_id         INT             NOT NULL,
    sale_reference  VARCHAR(50)     NOT NULL,
    total_amount    DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    total_cost      DECIMAL(10,2)   NOT NULL DEFAULT 0.00,
    profit          DECIMAL(10,2)   GENERATED ALWAYS AS (total_amount - total_cost) STORED,
    payment_method  VARCHAR(50)     DEFAULT 'cash',
    notes           TEXT            DEFAULT NULL,
    sold_at         TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    created_at      TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_sold_at (sold_at)
);

-- ─── SALES ITEMS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sales_items (
    id              INT AUTO_INCREMENT PRIMARY KEY,
    sale_id         INT             NOT NULL,
    product_id      INT             NOT NULL,
    product_name    VARCHAR(200)    NOT NULL,
    quantity        INT             NOT NULL,
    unit_price      DECIMAL(10,2)   NOT NULL,
    unit_cost       DECIMAL(10,2)   NOT NULL,
    subtotal        DECIMAL(10,2)   GENERATED ALWAYS AS (quantity * unit_price) STORED,
    FOREIGN KEY (sale_id)    REFERENCES sales(id)    ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT,
    INDEX idx_sale_id    (sale_id),
    INDEX idx_product_id (product_id)
);

-- ─── BACKUPS (Phase 3) ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS backups (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    user_id       INT             NOT NULL,
    label         VARCHAR(200)    NOT NULL,
    size_kb       INT             NOT NULL DEFAULT 0,
    product_count INT             NOT NULL DEFAULT 0,
    sale_count    INT             NOT NULL DEFAULT 0,
    created_at    TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
);