CREATE TABLE IF NOT EXISTS workshop_packages (
  package_code VARCHAR(40) NOT NULL,
  label_i18n JSON NOT NULL,
  participant_limit SMALLINT UNSIGNED NOT NULL,
  base_price_idr BIGINT UNSIGNED NULL,
  duration_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 120,
  buffer_before_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 60,
  buffer_after_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 60,
  confirmation_mode VARCHAR(24) NOT NULL DEFAULT 'manual',
  active TINYINT(1) NOT NULL DEFAULT 1,
  effective_from DATETIME NOT NULL,
  effective_until DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (package_code),
  KEY idx_workshop_packages_active (active, effective_from, effective_until),
  CONSTRAINT chk_workshop_package_confirmation CHECK (confirmation_mode IN ('manual', 'instant')),
  CONSTRAINT chk_workshop_package_capacity CHECK (participant_limit BETWEEN 1 AND 500)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_availability_rules (
  rule_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  resource_code VARCHAR(80) NOT NULL,
  canonical_timezone VARCHAR(80) NOT NULL DEFAULT 'Asia/Jakarta',
  weekday_iso TINYINT UNSIGNED NOT NULL,
  window_start TIME NOT NULL,
  window_end TIME NOT NULL,
  slot_start_times JSON NOT NULL,
  minimum_notice_business_days SMALLINT UNSIGNED NOT NULL DEFAULT 3,
  booking_horizon_days SMALLINT UNSIGNED NOT NULL DEFAULT 60,
  hold_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 10,
  daily_capacity SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  active TINYINT(1) NOT NULL DEFAULT 1,
  effective_from DATE NOT NULL,
  effective_until DATE NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (rule_id),
  UNIQUE KEY uniq_workshop_rule_day (resource_code, weekday_iso, effective_from),
  KEY idx_workshop_rules_active (active, effective_from, effective_until),
  CONSTRAINT chk_workshop_rule_weekday CHECK (weekday_iso BETWEEN 1 AND 7),
  CONSTRAINT chk_workshop_rule_window CHECK (window_end > window_start),
  CONSTRAINT chk_workshop_rule_capacity CHECK (daily_capacity BETWEEN 1 AND 20),
  CONSTRAINT chk_workshop_rule_hold CHECK (hold_minutes BETWEEN 1 AND 60)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_blackouts (
  blackout_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  resource_code VARCHAR(80) NOT NULL,
  starts_at_utc DATETIME NOT NULL,
  ends_at_utc DATETIME NOT NULL,
  reason VARCHAR(255) NULL,
  created_by VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (blackout_id),
  KEY idx_workshop_blackout_window (resource_code, starts_at_utc, ends_at_utc),
  CONSTRAINT chk_workshop_blackout_window CHECK (ends_at_utc > starts_at_utc)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_booking_contexts (
  context_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  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,
  token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  expires_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (context_id),
  UNIQUE KEY uniq_workshop_booking_context_token (token_hash),
  KEY idx_workshop_booking_context_opp (opportunity_id, expires_at),
  CONSTRAINT fk_workshop_booking_context_opportunity FOREIGN KEY (opportunity_id)
    REFERENCES crm_opportunities (opportunity_id) ON DELETE CASCADE,
  CONSTRAINT fk_workshop_booking_context_owner FOREIGN KEY (owner_user_id)
    REFERENCES auth_users (id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_slot_inventory (
  slot_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  resource_code VARCHAR(80) NOT NULL,
  service_date DATE NOT NULL,
  starts_at_utc DATETIME NOT NULL,
  ends_at_utc DATETIME NOT NULL,
  local_timezone VARCHAR(80) NOT NULL,
  local_start VARCHAR(5) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'available',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (slot_id),
  UNIQUE KEY uniq_workshop_slot_resource_start (resource_code, starts_at_utc, ends_at_utc),
  KEY idx_workshop_slots_availability (service_date, resource_code, status, starts_at_utc),
  CONSTRAINT chk_workshop_slot_window CHECK (ends_at_utc > starts_at_utc),
  CONSTRAINT chk_workshop_slot_status CHECK (status IN ('available', 'held', 'booked', 'closed'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_slot_holds (
  hold_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  slot_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  context_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  opportunity_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  package_code VARCHAR(40) NOT NULL,
  actor_scope VARCHAR(190) NOT NULL,
  idempotency_key VARCHAR(200) NOT NULL,
  request_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  expires_at DATETIME NOT NULL,
  converted_at DATETIME NULL,
  released_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (hold_id),
  UNIQUE KEY uniq_workshop_hold_idempotency (actor_scope, idempotency_key),
  KEY idx_workshop_holds_slot_status (slot_id, status, expires_at),
  KEY idx_workshop_holds_context (context_id, status, expires_at),
  CONSTRAINT fk_workshop_hold_slot FOREIGN KEY (slot_id)
    REFERENCES workshop_slot_inventory (slot_id),
  CONSTRAINT fk_workshop_hold_context FOREIGN KEY (context_id)
    REFERENCES workshop_booking_contexts (context_id),
  CONSTRAINT fk_workshop_hold_opportunity FOREIGN KEY (opportunity_id)
    REFERENCES crm_opportunities (opportunity_id),
  CONSTRAINT fk_workshop_hold_package FOREIGN KEY (package_code)
    REFERENCES workshop_packages (package_code),
  CONSTRAINT chk_workshop_hold_status CHECK (status IN ('active', 'converted', 'released', 'expired'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_bookings (
  booking_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  booking_reference VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  management_token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  context_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  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,
  brain_trace_id VARCHAR(120) NULL,
  package_code VARCHAR(40) NOT NULL,
  slot_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'pending_confirmation',
  visitor_timezone VARCHAR(80) NOT NULL,
  website_locale VARCHAR(16) CHARACTER SET ascii COLLATE ascii_bin 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,
  location_summary VARCHAR(500) NULL,
  participant_count SMALLINT UNSIGNED NOT NULL,
  pic_name VARCHAR(160) NOT NULL,
  company_name VARCHAR(190) NOT NULL,
  role_title VARCHAR(160) NOT NULL,
  work_email VARCHAR(190) NOT NULL,
  phone VARCHAR(80) NOT NULL,
  note TEXT NULL,
  public_context_summary TEXT NULL,
  privacy_policy_version VARCHAR(40) NOT NULL,
  contact_consent_at DATETIME NOT NULL,
  actor_scope VARCHAR(190) NOT NULL,
  idempotency_key VARCHAR(200) NOT NULL,
  request_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  confirmed_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  completed_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (booking_id),
  UNIQUE KEY uniq_workshop_booking_reference (booking_reference),
  UNIQUE KEY uniq_workshop_booking_management_token (management_token_hash),
  UNIQUE KEY uniq_workshop_booking_idempotency (actor_scope, idempotency_key),
  UNIQUE KEY uniq_workshop_booking_slot (slot_id),
  KEY idx_workshop_bookings_status (status, created_at),
  KEY idx_workshop_bookings_opportunity (opportunity_id, created_at),
  KEY idx_workshop_bookings_owner (owner_user_id, created_at),
  CONSTRAINT fk_workshop_booking_context FOREIGN KEY (context_id)
    REFERENCES workshop_booking_contexts (context_id),
  CONSTRAINT fk_workshop_booking_opportunity FOREIGN KEY (opportunity_id)
    REFERENCES crm_opportunities (opportunity_id),
  CONSTRAINT fk_workshop_booking_owner FOREIGN KEY (owner_user_id)
    REFERENCES auth_users (id) ON DELETE SET NULL,
  CONSTRAINT fk_workshop_booking_package FOREIGN KEY (package_code)
    REFERENCES workshop_packages (package_code),
  CONSTRAINT fk_workshop_booking_slot FOREIGN KEY (slot_id)
    REFERENCES workshop_slot_inventory (slot_id),
  CONSTRAINT chk_workshop_booking_status CHECK (status IN (
    'pending_confirmation', 'confirmed', 'payment_pending', 'paid',
    'scheduled', 'cancelled', 'completed'
  )),
  CONSTRAINT chk_workshop_booking_delivery CHECK (delivery_mode IN ('remote', 'onsite')),
  CONSTRAINT chk_workshop_booking_participants CHECK (participant_count BETWEEN 1 AND 500)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_booking_events (
  event_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  booking_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  actor_scope VARCHAR(190) NOT NULL,
  payload JSON NOT NULL,
  idempotency_key VARCHAR(200) NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (event_id),
  UNIQUE KEY uniq_workshop_booking_event_idempotency (idempotency_key),
  KEY idx_workshop_booking_events_booking (booking_id, created_at),
  CONSTRAINT fk_workshop_booking_event_booking FOREIGN KEY (booking_id)
    REFERENCES workshop_bookings (booking_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_notification_outbox (
  event_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  booking_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  channel VARCHAR(24) NOT NULL,
  template_code VARCHAR(80) NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  payload JSON 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),
  KEY idx_workshop_notification_ready (processed_at, available_at, attempt_count),
  KEY idx_workshop_notification_booking (booking_id, created_at),
  CONSTRAINT fk_workshop_notification_booking FOREIGN KEY (booking_id)
    REFERENCES workshop_bookings (booking_id) ON DELETE CASCADE,
  CONSTRAINT chk_workshop_notification_channel CHECK (channel IN ('email', 'ics', 'operations', 'whatsapp'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_offers (
  offer_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  booking_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 NULL,
  offer_version INT UNSIGNED NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'issued',
  invoice_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,
  terms_json JSON 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_workshop_offer_version (booking_id, offer_version),
  KEY idx_workshop_offer_status (status, expires_at),
  KEY idx_workshop_offer_estimate (estimate_id),
  CONSTRAINT fk_workshop_offer_booking FOREIGN KEY (booking_id)
    REFERENCES workshop_bookings (booking_id),
  CONSTRAINT fk_workshop_offer_opportunity FOREIGN KEY (opportunity_id)
    REFERENCES crm_opportunities (opportunity_id),
  CONSTRAINT fk_workshop_offer_estimate FOREIGN KEY (estimate_id)
    REFERENCES commercial_estimates (estimate_id) ON DELETE SET NULL,
  CONSTRAINT chk_workshop_offer_status CHECK (status IN ('issued', 'accepted', 'expired', 'cancelled')),
  CONSTRAINT chk_workshop_offer_amount CHECK (total_amount = subtotal_amount + tax_amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_offer_lines (
  line_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  offer_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  line_type VARCHAR(48) 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 (line_id),
  KEY idx_workshop_offer_lines_offer (offer_id, sort_order),
  CONSTRAINT fk_workshop_offer_line_offer FOREIGN KEY (offer_id)
    REFERENCES workshop_offers (offer_id) ON DELETE CASCADE,
  CONSTRAINT chk_workshop_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 workshop_invoices (
  invoice_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  invoice_number VARCHAR(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  booking_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  offer_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'issued',
  payment_rail VARCHAR(32) NOT NULL,
  invoice_currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  invoice_amount BIGINT UNSIGNED NOT NULL,
  charge_currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NULL,
  charge_amount BIGINT UNSIGNED NULL,
  fx_rate DECIMAL(24,10) NULL,
  fx_rate_source VARCHAR(120) NULL,
  fx_rate_at DATETIME NULL,
  finance_approved_at DATETIME NULL,
  issue_actor_scope VARCHAR(190) NOT NULL,
  issue_idempotency_key VARCHAR(200) NOT NULL,
  issue_request_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  checkout_token_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  checkout_expires_at DATETIME NOT NULL,
  due_at DATETIME NOT NULL,
  issued_at DATETIME NOT NULL,
  paid_at DATETIME NULL,
  cancelled_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (invoice_id),
  UNIQUE KEY uniq_workshop_invoice_number (invoice_number),
  UNIQUE KEY uniq_workshop_invoice_offer (offer_id),
  UNIQUE KEY uniq_workshop_invoice_issue_idempotency (issue_actor_scope, issue_idempotency_key),
  UNIQUE KEY uniq_workshop_invoice_checkout_token (checkout_token_hash),
  KEY idx_workshop_invoice_status (status, due_at),
  CONSTRAINT fk_workshop_invoice_booking FOREIGN KEY (booking_id)
    REFERENCES workshop_bookings (booking_id),
  CONSTRAINT fk_workshop_invoice_offer FOREIGN KEY (offer_id)
    REFERENCES workshop_offers (offer_id),
  CONSTRAINT chk_workshop_invoice_status CHECK (status IN (
    'issued', 'payment_pending', 'paid', 'expired', 'cancelled', 'refunded', 'partially_refunded'
  )),
  CONSTRAINT chk_workshop_invoice_rail CHECK (payment_rail IN ('midtrans_core', 'bank_transfer_invoice'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_payments (
  payment_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  invoice_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  booking_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  provider VARCHAR(32) NOT NULL,
  provider_order_id VARCHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  provider_transaction_id VARCHAR(120) NULL,
  payment_method VARCHAR(40) NOT NULL,
  status VARCHAR(32) NOT NULL DEFAULT 'initiating',
  currency CHAR(3) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  amount BIGINT UNSIGNED NOT NULL,
  actor_scope VARCHAR(190) NOT NULL,
  idempotency_key VARCHAR(200) NOT NULL,
  request_hash CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  safe_provider_response JSON NULL,
  paid_at DATETIME NULL,
  failed_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (payment_id),
  UNIQUE KEY uniq_workshop_payment_provider_order (provider, provider_order_id),
  UNIQUE KEY uniq_workshop_payment_idempotency (actor_scope, idempotency_key),
  KEY idx_workshop_payment_invoice (invoice_id, created_at),
  KEY idx_workshop_payment_status (status, updated_at),
  CONSTRAINT fk_workshop_payment_invoice FOREIGN KEY (invoice_id)
    REFERENCES workshop_invoices (invoice_id),
  CONSTRAINT fk_workshop_payment_booking FOREIGN KEY (booking_id)
    REFERENCES workshop_bookings (booking_id),
  CONSTRAINT chk_workshop_payment_status CHECK (status IN (
    'initiating', 'pending', 'challenge', 'authorized', 'paid',
    'denied', 'cancelled', 'expired', 'failed', 'refunded', 'partially_refunded'
  ))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS workshop_payment_events (
  event_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  payment_id CHAR(36) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  provider_event_key CHAR(64) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  event_type VARCHAR(80) NOT NULL,
  signature_trusted TINYINT(1) NOT NULL DEFAULT 0,
  amount_matched TINYINT(1) NOT NULL DEFAULT 0,
  safe_payload JSON NOT NULL,
  received_at DATETIME NOT NULL,
  PRIMARY KEY (event_id),
  UNIQUE KEY uniq_workshop_payment_provider_event (provider_event_key),
  KEY idx_workshop_payment_events_payment (payment_id, received_at),
  CONSTRAINT fk_workshop_payment_event_payment FOREIGN KEY (payment_id)
    REFERENCES workshop_payments (payment_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO workshop_packages (
  package_code, label_i18n, participant_limit, base_price_idr,
  duration_minutes, buffer_before_minutes, buffer_after_minutes,
  confirmation_mode, active, effective_from, created_at, updated_at
) VALUES
  (
    'executive',
    JSON_OBJECT(
      'id', 'Workshop AI Eksekutif', 'en', 'Executive AI Workshop',
      'zh-cn', '高管人工智能工作坊', 'de', 'Executive-AI-Workshop',
      'it', 'Workshop Executive AI', 'fr', 'Atelier IA pour dirigeants',
      'es', 'Taller ejecutivo de IA', 'ru', 'Воркшоп по ИИ для руководителей',
      'ja', '経営幹部向けAIワークショップ', 'ko', '경영진 AI 워크숍',
      'ar', 'ورشة الذكاء الاصطناعي للقيادات', 'pt', 'Workshop Executivo de IA',
      'hi', 'एग्जीक्यूटिव एआई कार्यशाला', 'vi', 'Hội thảo AI dành cho lãnh đạo',
      'th', 'เวิร์กช็อป AI สำหรับผู้บริหาร', 'fil', 'Executive AI Workshop'
    ),
    10, 25000000, 120, 60, 60, 'manual', 1,
    '2026-07-18 00:00:00', UTC_TIMESTAMP(), UTC_TIMESTAMP()
  ),
  (
    'company_ai_workforce',
    JSON_OBJECT(
      'id', 'Workshop Company AI Workforce', 'en', 'Company AI Workforce Workshop',
      'zh-cn', '企业人工智能团队工作坊', 'de', 'Company-AI-Workforce-Workshop',
      'it', 'Workshop Company AI Workforce', 'fr', 'Atelier Company AI Workforce',
      'es', 'Taller Company AI Workforce', 'ru', 'Воркшоп Company AI Workforce',
      'ja', '企業AIワークフォース・ワークショップ', 'ko', '기업 AI 워크포스 워크숍',
      'ar', 'ورشة قوة العمل بالذكاء الاصطناعي للشركات', 'pt', 'Workshop Company AI Workforce',
      'hi', 'कंपनी एआई वर्कफोर्स कार्यशाला', 'vi', 'Hội thảo Company AI Workforce',
      'th', 'เวิร์กช็อป Company AI Workforce', 'fil', 'Company AI Workforce Workshop'
    ),
    25, 50000000, 120, 60, 60, 'manual', 1,
    '2026-07-18 00:00:00', UTC_TIMESTAMP(), UTC_TIMESTAMP()
  ),
  (
    'undecided',
    JSON_OBJECT(
      'id', 'Belum yakin', 'en', 'Not sure yet', 'zh-cn', '尚未确定',
      'de', 'Noch nicht sicher', 'it', 'Non ancora sicuro', 'fr', 'Pas encore sûr',
      'es', 'Aún no estoy seguro', 'ru', 'Пока не уверен', 'ja', 'まだ未定',
      'ko', '아직 미정', 'ar', 'لم أقرر بعد', 'pt', 'Ainda não tenho certeza',
      'hi', 'अभी तय नहीं', 'vi', 'Chưa chắc chắn', 'th', 'ยังไม่แน่ใจ', 'fil', 'Hindi pa sigurado'
    ),
    25, NULL, 120, 60, 60, 'manual', 1,
    '2026-07-18 00:00:00', UTC_TIMESTAMP(), UTC_TIMESTAMP()
  )
ON DUPLICATE KEY UPDATE
  label_i18n = VALUES(label_i18n),
  participant_limit = VALUES(participant_limit),
  base_price_idr = VALUES(base_price_idr),
  duration_minutes = VALUES(duration_minutes),
  buffer_before_minutes = VALUES(buffer_before_minutes),
  buffer_after_minutes = VALUES(buffer_after_minutes),
  updated_at = VALUES(updated_at);

INSERT INTO workshop_availability_rules (
  resource_code, canonical_timezone, weekday_iso, window_start, window_end,
  slot_start_times, minimum_notice_business_days, booking_horizon_days,
  hold_minutes, daily_capacity, active, effective_from, created_at, updated_at
) VALUES
  ('coach-yoke', 'Asia/Jakarta', 1, '09:00:00', '17:00:00', JSON_ARRAY('09:00', '13:00'), 3, 60, 10, 1, 1, '2026-07-18', UTC_TIMESTAMP(), UTC_TIMESTAMP()),
  ('coach-yoke', 'Asia/Jakarta', 2, '09:00:00', '17:00:00', JSON_ARRAY('09:00', '13:00'), 3, 60, 10, 1, 1, '2026-07-18', UTC_TIMESTAMP(), UTC_TIMESTAMP()),
  ('coach-yoke', 'Asia/Jakarta', 3, '09:00:00', '17:00:00', JSON_ARRAY('09:00', '13:00'), 3, 60, 10, 1, 1, '2026-07-18', UTC_TIMESTAMP(), UTC_TIMESTAMP()),
  ('coach-yoke', 'Asia/Jakarta', 4, '09:00:00', '17:00:00', JSON_ARRAY('09:00', '13:00'), 3, 60, 10, 1, 1, '2026-07-18', UTC_TIMESTAMP(), UTC_TIMESTAMP()),
  ('coach-yoke', 'Asia/Jakarta', 5, '09:00:00', '17:00:00', JSON_ARRAY('09:00', '13:00'), 3, 60, 10, 1, 1, '2026-07-18', UTC_TIMESTAMP(), UTC_TIMESTAMP())
ON DUPLICATE KEY UPDATE
  canonical_timezone = VALUES(canonical_timezone),
  window_start = VALUES(window_start),
  window_end = VALUES(window_end),
  slot_start_times = VALUES(slot_start_times),
  minimum_notice_business_days = VALUES(minimum_notice_business_days),
  booking_horizon_days = VALUES(booking_horizon_days),
  hold_minutes = VALUES(hold_minutes),
  daily_capacity = VALUES(daily_capacity),
  updated_at = VALUES(updated_at);

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