USE db_abos_v0.1;

-- =============================================================================================
-- deal_templates_module.sql — Web-to-CRM deal automation, Phase D/E groundwork.
--
-- dim_deal_templates lets a tenant configure how a system-initiated web event (a paid web-store
-- order, a website service-request form submission) turns into a fact_opportunities row, without
-- hardcoding stage/status/title vocabulary in Java. Each row is looked up by (tenant_id,
-- template_key) at deal-creation time — see CrmDealFromWebService.
--
-- fact_web_orders.opportunity_id / fact_web_form_submissions.opportunity_id are the back-links
-- from the web-side row to the deal CrmDealFromWebService created for it. fact_web_orders also
-- gets invoice_id now (nullable, unused until Phase E wires invoice creation) so that column
-- doesn't need a second migration later.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS, guarded ALTERs, WHERE NOT EXISTS seed guards.
-- =============================================================================================

SET @db := DATABASE();

-- ── 1. dim_deal_templates ────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_deal_templates (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    template_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    default_stage VARCHAR(50) NOT NULL,
    default_status VARCHAR(50) NOT NULL,
    title_pattern VARCHAR(255) NOT NULL,
    line_item_type ENUM('Product','Service') NOT NULL,
    fulfillment_type ENUM('SHIPMENT','PROJECT','NONE') NOT NULL DEFAULT 'NONE',
    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_deal_templates_key (tenant_id, template_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 2. fact_web_orders — back-links to the deal/invoice it produced ─────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_web_orders' AND COLUMN_NAME='opportunity_id'),
  'ALTER TABLE fact_web_orders ADD COLUMN opportunity_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Unused until Phase E wires invoice creation from a paid web order — the column is added now
-- so that phase doesn't need its own migration just to add one nullable FK.
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_web_orders' AND COLUMN_NAME='invoice_id'),
  'ALTER TABLE fact_web_orders ADD COLUMN invoice_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 3. fact_web_form_submissions — back-link to the deal it produced ───────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_web_form_submissions' AND COLUMN_NAME='opportunity_id'),
  'ALTER TABLE fact_web_form_submissions ADD COLUMN opportunity_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 4. Seed default templates for tenant 1 (dev tenant convention — see
-- seed_larcare_business_and_site.sql's @tenant_id := 1) ────────────────────────────────────
SET @tenant_id := 1;

INSERT INTO dim_deal_templates (
    tenant_id, business_id, template_key, name, default_stage, default_status,
    title_pattern, line_item_type, fulfillment_type, created_by, updated_by, is_deleted
)
SELECT @tenant_id, NULL, 'web_store_order', 'Web Store Order', 'Negotiation', 'Qualified',
       'Store Order #{orderNo}', 'Product', 'SHIPMENT', NULL, NULL, 0
WHERE NOT EXISTS (
    SELECT 1 FROM dim_deal_templates WHERE tenant_id = @tenant_id AND template_key = 'web_store_order'
);

INSERT INTO dim_deal_templates (
    tenant_id, business_id, template_key, name, default_stage, default_status,
    title_pattern, line_item_type, fulfillment_type, created_by, updated_by, is_deleted
)
SELECT @tenant_id, NULL, 'web_service_request', 'Website Service Request', 'Discovery', 'New',
       'Service Request from {name}', 'Service', 'PROJECT', NULL, NULL, 0
WHERE NOT EXISTS (
    SELECT 1 FROM dim_deal_templates WHERE tenant_id = @tenant_id AND template_key = 'web_service_request'
);