CREATE TABLE IF NOT EXISTS seminar_orders (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id VARCHAR(64) NOT NULL,
  package_code VARCHAR(32) NOT NULL,
  package_label VARCHAR(80) NOT NULL,
  seat_quantity INT UNSIGNED NOT NULL,
  gross_amount INT UNSIGNED NOT NULL,
  buyer_name VARCHAR(160) NOT NULL,
  buyer_email VARCHAR(190) NOT NULL,
  buyer_phone VARCHAR(80) NOT NULL,
  company VARCHAR(190) NOT NULL,
  role_title VARCHAR(160) NOT NULL,
  industry VARCHAR(160) NULL,
  company_size VARCHAR(80) NULL,
  main_problem TEXT NULL,
  wants_invoice TINYINT(1) NOT NULL DEFAULT 0,
  invoice_legal_name VARCHAR(190) NULL,
  invoice_tax_id VARCHAR(80) NULL,
  invoice_address TEXT NULL,
  source_channel VARCHAR(120) NULL,
  status VARCHAR(64) NOT NULL DEFAULT 'registration_submitted',
  payment_status VARCHAR(64) NOT NULL DEFAULT 'not_created',
  midtrans_token VARCHAR(255) NULL,
  midtrans_redirect_url TEXT NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  paid_at DATETIME NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_seminar_orders_order_id (order_id),
  KEY idx_seminar_orders_status (status),
  KEY idx_seminar_orders_payment_status (payment_status),
  KEY idx_seminar_orders_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS seminar_attendees (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id VARCHAR(64) NOT NULL,
  seat_no INT UNSIGNED NOT NULL,
  attendee_name VARCHAR(160) NULL,
  attendee_email VARCHAR(190) NULL,
  attendee_phone VARCHAR(80) NULL,
  role_title VARCHAR(160) NULL,
  company VARCHAR(190) NULL,
  status VARCHAR(64) NOT NULL DEFAULT 'incomplete',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_seminar_attendee_seat (order_id, seat_no),
  KEY idx_seminar_attendees_order_id (order_id),
  CONSTRAINT fk_seminar_attendees_order
    FOREIGN KEY (order_id) REFERENCES seminar_orders(order_id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS seminar_payments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id VARCHAR(64) NOT NULL,
  transaction_id VARCHAR(160) NULL,
  payment_type VARCHAR(80) NULL,
  transaction_status VARCHAR(80) NULL,
  fraud_status VARCHAR(80) NULL,
  status_code VARCHAR(20) NULL,
  gross_amount DECIMAL(18,2) NULL,
  currency VARCHAR(12) NULL,
  signature_valid TINYINT(1) NOT NULL DEFAULT 0,
  raw_payload JSON NULL,
  received_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_seminar_payments_order_id (order_id),
  KEY idx_seminar_payments_transaction_status (transaction_status),
  CONSTRAINT fk_seminar_payments_order
    FOREIGN KEY (order_id) REFERENCES seminar_orders(order_id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS midtrans_notifications (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  order_id VARCHAR(64) NULL,
  transaction_id VARCHAR(160) NULL,
  transaction_status VARCHAR(80) NULL,
  fraud_status VARCHAR(80) NULL,
  signature_valid TINYINT(1) NOT NULL DEFAULT 0,
  payload JSON NOT NULL,
  received_at DATETIME NOT NULL,
  PRIMARY KEY (id),
  KEY idx_midtrans_notifications_order_id (order_id),
  KEY idx_midtrans_notifications_received_at (received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;