-- SheListens — migration 006: voice notes + live call signaling
-- Run via phpMyAdmin or: mysql -u USER -p DBNAME < migrations/006_voice_and_calls.sql
-- Safe to run on an existing database.

ALTER TABLE session_messages
  ADD COLUMN IF NOT EXISTS voice_note_duration_sec INT NULL AFTER voice_note_url;

CREATE TABLE IF NOT EXISTS call_signals (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  booking_id    CHAR(36) NOT NULL,
  sender_role   ENUM('client','listener') NOT NULL,
  kind          ENUM('offer','answer','ice','hangup','busy') NOT NULL,
  payload       TEXT NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- MySQL doesn't support "CREATE INDEX IF NOT EXISTS" before 8.0.29 in all
-- distros, so guard it the same way the payments FK migration does.
SET @idx_exists = (
  SELECT COUNT(*) FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'call_signals'
    AND INDEX_NAME = 'idx_call_signals_booking'
);
SET @sql = IF(@idx_exists = 0,
  'CREATE INDEX idx_call_signals_booking ON call_signals(booking_id, id)',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;