-- FASE 06 - catálogo, estoque, fotos e banners
ALTER TABLE products
    ADD COLUMN IF NOT EXISTS sku VARCHAR(80) NULL AFTER category_id,
    ADD COLUMN IF NOT EXISTS unit VARCHAR(40) NULL DEFAULT 'un' AFTER price,
    ADD COLUMN IF NOT EXISTS min_stock INT NOT NULL DEFAULT 0 AFTER stock,
    ADD COLUMN IF NOT EXISTS updated_at DATETIME NULL AFTER created_at;

CREATE TABLE IF NOT EXISTS stock_movements (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    product_id BIGINT UNSIGNED NOT NULL,
    movement_type ENUM('ENTRADA','SAIDA','AJUSTE') NOT NULL,
    quantity INT NOT NULL,
    reason VARCHAR(255) NULL,
    admin_id BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY(admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_stock_product(product_id),
    INDEX idx_stock_created(created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS banners (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(160) NOT NULL,
    subtitle VARCHAR(255) NULL,
    image_url VARCHAR(500) NULL,
    action_type ENUM('NONE','PRODUCT','CATEGORY','URL') NOT NULL DEFAULT 'NONE',
    action_value VARCHAR(500) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO banners(title,subtitle,action_type,sort_order,active)
SELECT 'Economia que chega na sua porta','Cestas básicas com entrega rápida em Valença','CATEGORY',1,1
WHERE NOT EXISTS (SELECT 1 FROM banners WHERE title='Economia que chega na sua porta');