USE db_abos_v0.1;

-- =============================================================================
-- business_type_template_catalog.sql — catalog templates per business type (Phase L2).
--
-- Adds a nullable business_type_id to the three existing template tables
-- (dim_deal_templates, dim_workitem_templates, dim_document_templates). A row
-- with business_id IS NULL and business_type_id set is a catalog/starter
-- template for that business type — never used directly by CrmDealFromWebService
-- or TaskBoard (their lookups don't filter on business_type_id), so this is
-- purely additive scaffolding until BusinessTemplateSeedService (Phase L3)
-- clones matching catalog rows into business-owned rows on business creation.
--
-- Safe to re-run: guarded ALTERs, WHERE NOT EXISTS seed guards.
-- =============================================================================

SET @db := DATABASE();

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

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

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

-- dim_deal_templates.template_key is UNIQUE per (tenant_id, template_key) — catalog rows use a
-- 'catalog_<type>_deal' key so they never collide with tenant/business-owned clones (which get a
-- '_biz<id>' suffix at clone time, see BusinessTemplateSeedService).
SET @tenant_id := 1;

INSERT INTO dim_deal_templates (tenant_id, business_id, business_type_id, template_key, name, default_stage, default_status, title_pattern, line_item_type, fulfillment_type, is_deleted)
SELECT @tenant_id, NULL, bt.id, v.template_key, v.name, v.default_stage, v.default_status, v.title_pattern, v.line_item_type, v.fulfillment_type, 0
FROM (
    SELECT 'retail' type_key, 'catalog_retail_deal' template_key, 'Retail Sale' name, 'Negotiation' default_stage, 'Qualified' default_status, 'Sale — {clientName}' title_pattern, 'Product' line_item_type, 'SHIPMENT' fulfillment_type
    UNION ALL SELECT 'service', 'catalog_service_deal', 'Service Engagement', 'Discovery', 'New', 'Service Engagement — {clientName}', 'Service', 'PROJECT'
    UNION ALL SELECT 'healthcare', 'catalog_healthcare_deal', 'Patient Care Plan', 'Discovery', 'New', 'Care Plan — {clientName}', 'Service', 'PROJECT'
    UNION ALL SELECT 'real_estate', 'catalog_real_estate_deal', 'Property Listing', 'Discovery', 'New', 'Listing — {clientName}', 'Service', 'PROJECT'
    UNION ALL SELECT 'logistics', 'catalog_logistics_deal', 'Freight Shipment', 'Discovery', 'New', 'Shipment — {clientName}', 'Service', 'PROJECT'
) v
JOIN dim_business_types bt ON bt.tenant_id IS NULL AND bt.type_key = v.type_key
WHERE NOT EXISTS (
    SELECT 1 FROM dim_deal_templates d WHERE d.tenant_id = @tenant_id AND d.template_key = v.template_key
);

INSERT INTO dim_workitem_templates (tenant_id, business_id, business_type_id, type, name, default_title, default_description, default_priority, is_deleted)
SELECT @tenant_id, NULL, bt.id, v.type, v.name, v.default_title, v.default_description, v.default_priority, 0
FROM (
    SELECT 'healthcare' type_key, 'ToDo' type, 'Patient Intake' name, 'Patient intake — {name}' default_title, 'Collect patient history, insurance, and consent forms.' default_description, 'High' default_priority
    UNION ALL SELECT 'logistics', 'ToDo', 'Shipment Tracking', 'Track shipment — {name}', 'Confirm carrier pickup and monitor delivery milestones.', 'Medium'
) v
JOIN dim_business_types bt ON bt.tenant_id IS NULL AND bt.type_key = v.type_key
WHERE NOT EXISTS (
    SELECT 1 FROM dim_workitem_templates w
    WHERE w.tenant_id = @tenant_id AND w.business_id IS NULL AND w.business_type_id = bt.id AND w.name = v.name
);