-- Migration: 007_create_inventory_tables.sql
-- Implementa o modulo de produtos e estoque com isolamento multi-tenant

USE saas_barbearia;

CREATE TABLE IF NOT EXISTS product_categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_product_categories_name (company_id, name),
    INDEX idx_product_categories_company_status (company_id, status),
    CONSTRAINT fk_product_categories_company
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS suppliers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    name VARCHAR(180) NOT NULL,
    document VARCHAR(50) NULL,
    email VARCHAR(180) NULL,
    phone VARCHAR(30) NULL,
    contact_name VARCHAR(180) NULL,
    notes TEXT NULL,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_suppliers_document (company_id, document),
    INDEX idx_suppliers_company_status (company_id, status),
    INDEX idx_suppliers_name (name),
    CONSTRAINT fk_suppliers_company
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    category_id INT NULL,
    supplier_id INT NULL,
    name VARCHAR(180) NOT NULL,
    description TEXT NULL,
    sku VARCHAR(80) NULL,
    barcode VARCHAR(80) NULL,
    unit VARCHAR(30) NOT NULL DEFAULT 'un',
    cost_price DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    sale_price DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    current_stock DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    minimum_stock DECIMAL(15,4) NOT NULL DEFAULT 0.0000,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_products_sku (company_id, sku),
    UNIQUE KEY unique_products_barcode (company_id, barcode),
    INDEX idx_products_company_status (company_id, status),
    INDEX idx_products_company_stock (company_id, current_stock, minimum_stock),
    INDEX idx_products_name (name),
    CONSTRAINT fk_products_company
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id) REFERENCES product_categories(id) ON DELETE SET NULL,
    CONSTRAINT fk_products_supplier
        FOREIGN KEY (supplier_id) REFERENCES suppliers(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS stock_movements (
    id INT PRIMARY KEY AUTO_INCREMENT,
    company_id INT NOT NULL,
    product_id INT NOT NULL,
    movement_type ENUM('entry', 'exit', 'adjustment', 'sale', 'reversal') NOT NULL,
    quantity DECIMAL(15,4) NOT NULL,
    stock_before DECIMAL(15,4) NOT NULL,
    stock_after DECIMAL(15,4) NOT NULL,
    unit_cost DECIMAL(15,4) NULL,
    reference_type VARCHAR(50) NULL,
    reference_id INT NULL,
    notes TEXT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_stock_movements_company_product (company_id, product_id),
    INDEX idx_stock_movements_reference (company_id, reference_type, reference_id),
    INDEX idx_stock_movements_type_date (company_id, movement_type, created_at),
    CONSTRAINT fk_stock_movements_company
        FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    CONSTRAINT fk_stock_movements_product
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    CONSTRAINT fk_stock_movements_created_by
        FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
);

ALTER TABLE order_items
    ADD CONSTRAINT fk_order_items_product
        FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL;