-- ============================================================================================
-- PRODUCTION MIGRATION — Workitem Templates (saved creation presets per backlog-item type)
-- ============================================================================================
-- Context: Projects/Tasks module. Lets an admin define saved presets ("templates") per
-- backlog-item type (Feature/PBI/Task/Bug/Spike/Milestone/Activity/Deliverable/Goal/ToDo/
-- ActionItem — see WorkitemService.VALID_TASK_TYPES). When starting a new item of a given
-- type in TaskBoard, if templates exist for that type they can be picked to pre-fill
-- title/description/priority/estimate. Same tenant + nullable-business scoping convention as
-- fact_activities/dim_projects (business_id IS NULL = applies to all businesses).
--
-- Idempotent — safe to re-run if a deployment is interrupted partway through.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/dim_workitem_templates.sql
-- ============================================================================================

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS dim_workitem_templates (
    id                     BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id              INT NOT NULL,
    business_id            INT NULL,
    type                   VARCHAR(50) NOT NULL,
    name                   VARCHAR(255) NOT NULL,
    default_title          VARCHAR(255) NULL,
    default_description    TEXT NULL,
    default_priority       VARCHAR(20) NULL,
    default_estimate_hours DECIMAL(8,2) NULL,
    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,
    INDEX idx_dwt_tenant_type (tenant_id, type),
    INDEX idx_dwt_tenant (tenant_id)
);