USE db_abos_v0.1;

-- =============================================================================
-- business_types_module.sql — Business Type catalog (Phase L1).
--
-- dim_business_types is a lookup table, not a hardcoded Java enum, so a tenant
-- can add its own custom type alongside the system-seeded ones. tenant_id NULL
-- marks a system type (visible to every tenant); a tenant-owned row has
-- tenant_id set. default_active_processes reuses the exact comma-separated
-- module-key vocabulary already stored on businesses.active_processes
-- (CRM, FINANCE, HRM, PROJECT, PROCUREMENT, REAL_ESTATE, LEGAL, WEB) so
-- picking a type can pre-fill that field.
--
-- businesses.business_type_id is nullable — existing businesses are
-- unaffected until an admin explicitly sets a type (see BusinessTemplateSeedService,
-- Phase L3, which reacts to that column being set).
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS, guarded ALTER, WHERE NOT EXISTS seed guards.
-- =============================================================================

SET @db := DATABASE();

CREATE TABLE IF NOT EXISTS dim_business_types (
    id                       BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id                INT NULL,
    type_key                 VARCHAR(50) NOT NULL,
    name                     VARCHAR(255) NOT NULL,
    description              VARCHAR(500) NULL,
    default_active_processes VARCHAR(255) NULL,
    sort_order               INT NOT NULL DEFAULT 0,
    is_system                TINYINT NOT NULL DEFAULT 0,
    created_at               TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by               INT NULL,
    updated_at               TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by               INT NULL,
    is_deleted               TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_business_types_key (tenant_id, type_key),
    INDEX idx_dim_business_types_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='businesses' AND COLUMN_NAME='business_type_id'),
  'ALTER TABLE businesses ADD COLUMN business_type_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── Seed system business types (tenant_id NULL = visible to every tenant) ────
INSERT INTO dim_business_types (tenant_id, type_key, name, description, default_active_processes, sort_order, is_system, is_deleted)
SELECT NULL, v.type_key, v.name, v.description, v.default_active_processes, v.sort_order, 1, 0
FROM (
    SELECT 'retail' type_key, 'Retail / Merchandise Store' name, 'Physical or online product sales' description, 'CRM,FINANCE,PROCUREMENT,WEB' default_active_processes, 10 sort_order
    UNION ALL SELECT 'service', 'Service Business', 'Consulting, agencies, home-care style services', 'CRM,FINANCE,PROJECT', 20
    UNION ALL SELECT 'healthcare', 'Healthcare / Clinic', 'Patient-facing service business', 'CRM,FINANCE,PROJECT,WEB', 30
    UNION ALL SELECT 'real_estate', 'Real Estate / Property', 'Listings, leases, property-linked deals', 'CRM,FINANCE,REAL_ESTATE', 40
    UNION ALL SELECT 'logistics', 'Logistics', 'Freight, shipment tracking, distribution', 'CRM,FINANCE,PROCUREMENT', 50
    UNION ALL SELECT 'professional_services', 'Professional Services / Agency', 'Agencies, consultancies, freelance studios', 'CRM,FINANCE,PROJECT', 60
    UNION ALL SELECT 'manufacturing', 'Manufacturing', 'Production and assembly businesses', 'CRM,FINANCE,PROCUREMENT,HRM', 70
    UNION ALL SELECT 'hospitality', 'Hospitality / Food Service', 'Restaurants, hotels, food & beverage', 'CRM,FINANCE,PROCUREMENT,HRM', 80
    UNION ALL SELECT 'construction', 'Construction / Trades', 'Contractors, trades, project-based builds', 'CRM,FINANCE,PROJECT,PROCUREMENT,HRM', 90
    UNION ALL SELECT 'general', 'General / Other', 'Anything that does not fit a specific type', 'CRM,FINANCE', 999
) v
WHERE NOT EXISTS (
    SELECT 1 FROM dim_business_types t WHERE t.tenant_id IS NULL AND t.type_key = v.type_key
);