-- SheListens — migration 009: support tickets
-- Run via phpMyAdmin or: mysql -u USER -p DBNAME < migrations/009_support_tickets.sql
-- Safe to run on an existing database.
--
-- Normal messaging only exists inside a live/booked session window — this
-- gives clients a way to reach the listener (billing questions, "I need to
-- talk before my next session", general help) at any other time.

CREATE TABLE IF NOT EXISTS support_tickets (
  id            CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  client_id     CHAR(36) NOT NULL,
  subject       VARCHAR(150) NOT NULL,
  status        ENUM('open','closed') NOT NULL DEFAULT 'open',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS support_messages (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  ticket_id     CHAR(36) NOT NULL,
  sender_role   ENUM('client','listener') NOT NULL,
  body          TEXT NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (ticket_id) REFERENCES support_tickets(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;