-- Inventory Asset Categories: master/lookup table for Digital Asset `category` values.
-- Replaces the hardcoded frontend category list with a real CRUD-managed table so tenants can
-- add their own categories (Insurance, State Reports, Trademarks IP, SaaS-Tech, Federal License,
-- Compliance, Dept of Labor, Reports, etc.) without a code change.
-- STRUCTURE ONLY — see seed_asset_categories.sql for the starter category rows.
-- Idempotent — safe to re-run.
-- Deploy (local): mysql -u root -p db_abos_v0.1 < db/add_asset_categories_master.sql

USE db_abos_v0.1;

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)
);