USE db_abos_v0.1;

-- =============================================================================================
-- project_deal_link.sql — back-link from a fulfillment project to the deal that spawned it.
--
-- CrmDealFromWebService.createProjectForServiceDeal creates a starter dim_projects row when a
-- service-request web deal's dim_deal_templates row has fulfillment_type='PROJECT' (see
-- deal_templates_module.sql's 'web_service_request' seed row). dim_projects.opportunity_id is
-- the back-link from that project to the fact_opportunities row it was created for — mirrors
-- fact_web_orders.opportunity_id / fact_web_form_submissions.opportunity_id's existing
-- convention for this exact kind of back-link (see deal_templates_module.sql). A 'SHIPMENT'
-- fulfillment_type deal (e.g. 'web_store_order') never gets a project — its fulfillment already
-- lives on fact_web_orders.status — so this column stays NULL for those.
--
-- Safe to re-run: information_schema-guarded ALTER (no "already exists" error on MySQL/MariaDB
-- for ADD COLUMN), and the index is added the same guarded way since there's no
-- CREATE INDEX/ADD INDEX IF NOT EXISTS either.
-- =============================================================================================

SET @db := DATABASE();

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

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND INDEX_NAME='idx_dim_projects_opportunity'),
  'ALTER TABLE dim_projects ADD INDEX idx_dim_projects_opportunity (opportunity_id)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;