CREATE TABLE IF NOT EXISTS commercial_policy_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  policy_code VARCHAR(100) NOT NULL,
  version VARCHAR(40) NOT NULL,
  status VARCHAR(24) NOT NULL DEFAULT 'active',
  sessions_per_day SMALLINT UNSIGNED NOT NULL,
  session_value_idr BIGINT UNSIGNED NOT NULL,
  offshore_buffer_sessions SMALLINT UNSIGNED NOT NULL,
  delegation_default SMALLINT UNSIGNED NOT NULL,
  travel_days_min SMALLINT UNSIGNED NOT NULL,
  travel_days_max SMALLINT UNSIGNED NOT NULL,
  operations_review_after_days SMALLINT UNSIGNED NOT NULL,
  travel_costs_borne_by_inviter TINYINT(1) NOT NULL DEFAULT 1,
  effective_at DATETIME NOT NULL,
  retired_at DATETIME NULL,
  created_by VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_commercial_policy_version (policy_code, version),
  KEY idx_commercial_policy_active (policy_code, status, effective_at),
  CONSTRAINT chk_commercial_policy_status CHECK (status IN ('draft', 'active', 'retired')),
  CONSTRAINT chk_commercial_policy_days CHECK (
    travel_days_min >= 1
    AND travel_days_max >= travel_days_min
    AND operations_review_after_days BETWEEN travel_days_min AND travel_days_max
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS crm_opportunities (
  opportunity_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  owner_user_id BIGINT UNSIGNED NULL,
  journey_id VARCHAR(120) NULL,
  chat_id VARCHAR(120) NULL,
  session_id VARCHAR(120) NULL,
  opportunity_type VARCHAR(40) NOT NULL DEFAULT 'workshop',
  stage VARCHAR(40) NOT NULL DEFAULT 'workshop_offered',
  idempotency_key VARCHAR(200) NOT NULL,
  source VARCHAR(40) NOT NULL DEFAULT 'gia_chat_handoff',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (opportunity_id),
  UNIQUE KEY uniq_crm_opportunity_idempotency (idempotency_key),
  KEY idx_crm_opportunities_owner (owner_user_id),
  KEY idx_crm_opportunities_journey (journey_id),
  KEY idx_crm_opportunities_stage (stage, updated_at),
  CONSTRAINT fk_crm_opportunities_owner FOREIGN KEY (owner_user_id)
    REFERENCES auth_users (id) ON DELETE SET NULL,
  CONSTRAINT chk_crm_opportunity_stage CHECK (stage IN (
    'identified', 'workshop_offered', 'slot_held', 'invoice_pending',
    'payment_pending', 'paid', 'scheduled', 'intake', 'delivered',
    'executive_report', 'audit_opportunity', 'pilot_opportunity',
    'retainer_opportunity', 'won', 'nurture', 'lost'
  ))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS commercial_estimates (
  estimate_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  owner_user_id BIGINT UNSIGNED NULL,
  actor_scope VARCHAR(190) NOT NULL,
  journey_id VARCHAR(120) NULL,
  opportunity_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NULL,
  policy_id BIGINT UNSIGNED NOT NULL,
  policy_version VARCHAR(40) NOT NULL,
  delivery_mode VARCHAR(16) NOT NULL,
  destination_country CHAR(2) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  destination_city VARCHAR(160) NOT NULL,
  total_blocked_travel_days SMALLINT UNSIGNED NOT NULL,
  delegation_size SMALLINT UNSIGNED NOT NULL,
  sessions_per_day SMALLINT UNSIGNED NOT NULL,
  session_value_idr BIGINT UNSIGNED NOT NULL,
  offshore_buffer_sessions SMALLINT UNSIGNED NOT NULL,
  opportunity_cost_idr BIGINT UNSIGNED NOT NULL,
  offshore_buffer_idr BIGINT UNSIGNED NOT NULL,
  estimated_blocking_fee_idr BIGINT UNSIGNED NOT NULL,
  currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'IDR',
  travel_costs_borne_by_inviter TINYINT(1) NOT NULL DEFAULT 1,
  operations_review_required TINYINT(1) NOT NULL DEFAULT 0,
  commercial_status VARCHAR(32) NOT NULL DEFAULT 'quotation_required',
  request_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  idempotency_key VARCHAR(200) NOT NULL,
  supersedes_estimate_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (estimate_id),
  UNIQUE KEY uniq_commercial_estimate_idempotency (actor_scope, idempotency_key),
  KEY idx_commercial_estimate_opportunity (opportunity_id, created_at),
  KEY idx_commercial_estimate_policy (policy_id, created_at),
  KEY idx_commercial_estimate_owner (owner_user_id, created_at),
  KEY idx_commercial_estimate_supersedes (supersedes_estimate_id),
  CONSTRAINT fk_commercial_estimate_owner FOREIGN KEY (owner_user_id)
    REFERENCES auth_users (id) ON DELETE SET NULL,
  CONSTRAINT fk_commercial_estimate_policy FOREIGN KEY (policy_id)
    REFERENCES commercial_policy_versions (id),
  CONSTRAINT fk_commercial_estimate_opportunity FOREIGN KEY (opportunity_id)
    REFERENCES crm_opportunities (opportunity_id) ON DELETE SET NULL,
  CONSTRAINT fk_commercial_estimate_supersedes FOREIGN KEY (supersedes_estimate_id)
    REFERENCES commercial_estimates (estimate_id) ON DELETE SET NULL,
  CONSTRAINT chk_commercial_estimate_delivery CHECK (delivery_mode = 'onsite'),
  CONSTRAINT chk_commercial_estimate_non_id CHECK (destination_country <> 'ID'),
  CONSTRAINT chk_commercial_estimate_status CHECK (commercial_status IN (
    'quotation_required', 'recalculation_required', 'offer_issued',
    'offer_accepted', 'offer_expired', 'offer_cancelled'
  )),
  CONSTRAINT chk_commercial_estimate_math CHECK (
    opportunity_cost_idr = total_blocked_travel_days * sessions_per_day * session_value_idr
    AND offshore_buffer_idr = offshore_buffer_sessions * session_value_idr
    AND estimated_blocking_fee_idr = opportunity_cost_idr + offshore_buffer_idr
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS commercial_offers (
  offer_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  opportunity_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  estimate_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  offer_version INT UNSIGNED NOT NULL,
  commercial_status VARCHAR(32) NOT NULL DEFAULT 'offer_issued',
  currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  subtotal_amount BIGINT UNSIGNED NOT NULL,
  tax_amount BIGINT UNSIGNED NOT NULL DEFAULT 0,
  total_amount BIGINT UNSIGNED NOT NULL,
  issued_by VARCHAR(120) NOT NULL,
  issued_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  accepted_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (offer_id),
  UNIQUE KEY uniq_commercial_offer_version (opportunity_id, offer_version),
  KEY idx_commercial_offer_estimate (estimate_id),
  KEY idx_commercial_offer_status (commercial_status, expires_at),
  CONSTRAINT fk_commercial_offer_opportunity FOREIGN KEY (opportunity_id)
    REFERENCES crm_opportunities (opportunity_id),
  CONSTRAINT fk_commercial_offer_estimate FOREIGN KEY (estimate_id)
    REFERENCES commercial_estimates (estimate_id),
  CONSTRAINT chk_commercial_offer_status CHECK (commercial_status IN (
    'offer_issued', 'offer_accepted', 'offer_expired', 'offer_cancelled'
  )),
  CONSTRAINT chk_commercial_offer_amount CHECK (total_amount = subtotal_amount + tax_amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS commercial_offer_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  offer_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  line_type VARCHAR(40) NOT NULL,
  description VARCHAR(255) NOT NULL,
  quantity DECIMAL(12,2) NOT NULL DEFAULT 1.00,
  unit_amount BIGINT UNSIGNED NOT NULL,
  line_amount BIGINT UNSIGNED NOT NULL,
  sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_commercial_offer_lines_offer (offer_id, sort_order),
  CONSTRAINT fk_commercial_offer_line_offer FOREIGN KEY (offer_id)
    REFERENCES commercial_offers (offer_id) ON DELETE CASCADE,
  CONSTRAINT chk_commercial_offer_line_type CHECK (line_type IN (
    'blocking_time_professional_fee', 'travel_reimbursement',
    'accommodation_reimbursement', 'local_transport_reimbursement',
    'meals_reimbursement', 'visa_insurance_reimbursement', 'tax', 'other_approved'
  )),
  CONSTRAINT chk_commercial_offer_line_math CHECK (line_amount = ROUND(quantity * unit_amount, 0))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS commercial_event_outbox (
  event_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  aggregate_type VARCHAR(40) NOT NULL,
  aggregate_id VARCHAR(64) NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  payload JSON NOT NULL,
  idempotency_key VARCHAR(200) NOT NULL,
  attempt_count INT UNSIGNED NOT NULL DEFAULT 0,
  available_at DATETIME NOT NULL,
  processed_at DATETIME NULL,
  last_error VARCHAR(500) NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (event_id),
  UNIQUE KEY uniq_commercial_outbox_idempotency (idempotency_key),
  KEY idx_commercial_outbox_ready (processed_at, available_at, attempt_count)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS commercial_request_limits (
  bucket_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  actor_scope VARCHAR(190) NOT NULL,
  ip_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  window_start DATETIME NOT NULL,
  request_count INT UNSIGNED NOT NULL DEFAULT 1,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (bucket_key),
  KEY idx_commercial_request_limit_cleanup (window_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO commercial_policy_versions (
  policy_code, version, status, sessions_per_day, session_value_idr,
  offshore_buffer_sessions, delegation_default, travel_days_min,
  travel_days_max, operations_review_after_days,
  travel_costs_borne_by_inviter, effective_at, created_by, created_at
) VALUES (
  'international-onsite-blocking-time', '2026-07-18', 'active',
  2, 50000000, 2, 3, 1, 365, 30, 1,
  '2026-07-18 00:00:00', 'FRED_COACH_POLICY', UTC_TIMESTAMP()
) ON DUPLICATE KEY UPDATE version = VALUES(version);

INSERT INTO migrations (migration, batch)
SELECT '003_workshop_commercial', COALESCE(MAX(batch), 0) + 1
FROM migrations
WHERE NOT EXISTS (
  SELECT 1 FROM migrations WHERE migration = '003_workshop_commercial'
);