-- Aggie-server counterpart of add_asset_categories_master.sql (structure only).
-- Idempotent — safe to re-run.
-- Deploy: mysql -u <user> -p 'stacie_Aggie_v1.0' < db/aggie_add_asset_categories_master.sql

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS dim_asset_categories (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT NOT NULL,
    name        VARCHAR(150) NOT NULL,
    is_active   TINYINT(1) NOT NULL DEFAULT 1,
    is_deleted  TINYINT(1) NOT NULL DEFAULT 0,
    created_at  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by  INT NULL,
    updated_at  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by  INT NULL,
    UNIQUE KEY uq_asset_category_tenant_name (tenant_id, name),
    KEY idx_asset_categories_tenant (tenant_id, is_deleted)
);