-- SheListens — schema.sql
-- Import via phpMyAdmin or: mysql -u USER -p DBNAME < schema.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ============================================================
-- USERS & AUTH
-- ============================================================
CREATE TABLE users (
  id            CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  role          ENUM('client','owner') NOT NULL DEFAULT 'client',
  name          VARCHAR(120) NOT NULL,
  alias         VARCHAR(60)  NULL,
  email         VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  admin_password_hash VARCHAR(255) NULL,
  risk_flag     ENUM('low','watch','high') NOT NULL DEFAULT 'low',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login_at DATETIME NULL,
  suspended_at  DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sessions_auth (
  id            CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id       CHAR(36) NOT NULL,
  token_hash    CHAR(64) NOT NULL,
  mode          ENUM('listener','admin') NOT NULL DEFAULT 'listener',
  admin_granted_at DATETIME NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  expires_at    DATETIME NOT NULL,
  ip            VARCHAR(45) NULL,
  device        VARCHAR(160) NULL,
  INDEX idx_token (token_hash),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE consents (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id       CHAR(36) NOT NULL,
  policy        ENUM('terms','privacy','confidentiality') NOT NULL,
  accepted_at   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  ip            VARCHAR(45) NULL,
  device        VARCHAR(160) NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- SUBSCRIPTIONS & PAYMENTS
-- ============================================================
CREATE TABLE plans (
  id            VARCHAR(20) PRIMARY KEY,
  name          VARCHAR(40) NOT NULL,
  price         INT NOT NULL,
  period_label  VARCHAR(10) NOT NULL,
  video_included TINYINT(1) NOT NULL DEFAULT 0,
  interval_count INT NOT NULL DEFAULT 1 COMMENT 'drives actual renewal dates, e.g. 3 + month = quarterly',
  interval_unit ENUM('day','week','month','year') NOT NULL DEFAULT 'month',
  features      TEXT NULL COMMENT 'JSON array of short feature strings, admin-editable'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO plans (id, name, price, period_label, video_included, interval_count, interval_unit, features) VALUES
  ('weekly', 'Weekly', 50000, '/wk', 0, 1, 'week',
   '["45-minute sessions with your dedicated listener","Text and voice sessions included","Video sessions billed separately"]'),
  ('monthly', 'Monthly', 179000, '/mo', 0, 1, 'month',
   '["Everything in Weekly","Priority scheduling","Video sessions billed separately"]'),
  ('yearly', 'Yearly', 500000, '/yr', 1, 1, 'year',
   '["Everything in Monthly","Video sessions included at no extra fee","Two months free vs. paying monthly"]');

CREATE TABLE subscriptions (
  id            CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id       CHAR(36) NOT NULL,
  plan_id       VARCHAR(20) NOT NULL,
  status        ENUM('active','cancelled','expired') NOT NULL DEFAULT 'active',
  started_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  renews_at     DATETIME NOT NULL,
  cancelled_at  DATETIME NULL,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (plan_id) REFERENCES plans(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE payments (
  id            CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  user_id       CHAR(36) NOT NULL,
  subscription_id CHAR(36) NULL,
  booking_id    CHAR(36) NULL,
  amount        INT NOT NULL,
  gateway       ENUM('paystack','flutterwave','kora','manual') NOT NULL DEFAULT 'manual',
  gateway_ref   VARCHAR(120) NULL,
  status        ENUM('paid','failed','refunded') NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  FOREIGN KEY (subscription_id) REFERENCES subscriptions(id) ON DELETE SET NULL
  -- booking_id's FK is added after the bookings table below (it doesn't exist yet here).
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- AVAILABILITY & BOOKINGS
-- ============================================================
CREATE TABLE availability_rules (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  weekday       TINYINT NOT NULL,
  start_time    TIME NOT NULL,
  end_time      TIME NOT NULL,
  active        TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE availability_blocks (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  start_date    DATE NOT NULL,
  end_date      DATE NOT NULL,
  reason        VARCHAR(120) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE bookings (
  id            CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  client_id     CHAR(36) NOT NULL,
  scheduled_at  DATETIME NOT NULL,
  duration_min  INT NOT NULL DEFAULT 45,
  type          ENUM('text','voice','video') NOT NULL,
  status        ENUM('pending','confirmed','completed','cancelled','ended_early') NOT NULL DEFAULT 'confirmed',
  video_fee     INT NOT NULL DEFAULT 0,
  extended      TINYINT(1) NOT NULL DEFAULT 0,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- payments.booking_id references this table, which didn't exist yet when
-- payments was created above.
ALTER TABLE payments
  ADD CONSTRAINT fk_payments_booking FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE SET NULL;

-- ============================================================
-- LIVE SESSION TRANSCRIPTS (apply your own retention policy)
-- Messaging is session-only: this is the one and only chat surface,
-- tied to a booking. There is no separate always-open async inbox.
-- ============================================================
CREATE TABLE session_messages (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  booking_id    CHAR(36) NOT NULL,
  sender_role   ENUM('client','listener') NOT NULL,
  body          TEXT NULL,
  voice_note_url VARCHAR(255) NULL,
  voice_note_duration_sec INT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (booking_id) REFERENCES bookings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- WebRTC signaling for live voice/video calls within a session. Rows are
-- short-lived — each side polls for the other's offer/answer/ICE
-- candidates and old rows are pruned automatically (see
-- session/call-signal.php). There's no TURN server configured, so calls
-- rely on STUN + direct/host candidates; some restrictive networks
-- (symmetric NAT, strict corporate firewalls) may fail to connect.
CREATE TABLE 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;
CREATE INDEX idx_call_signals_booking ON call_signals(booking_id, id);

-- ============================================================
-- CLIENT-PRIVATE CONTENT
-- ============================================================
CREATE TABLE journal_entries (
  id            CHAR(36) PRIMARY KEY DEFAULT (UUID()),
  client_id     CHAR(36) NOT NULL,
  title         VARCHAR(120) NULL,
  mood          VARCHAR(30) NULL,
  body          TEXT NOT NULL,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE mood_checkins (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id     CHAR(36) NOT NULL,
  value         TINYINT NOT NULL,
  checked_at    DATE NOT NULL,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY one_per_day (client_id, checked_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- LISTENER-PRIVATE CONTENT
-- ============================================================
CREATE TABLE client_notes (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  client_id     CHAR(36) NOT NULL,
  body          TEXT NOT NULL,
  risk_flag     ENUM('low','watch','high') NOT NULL DEFAULT 'low',
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (client_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- STATIC / ADMIN-EDITABLE CONTENT
-- ============================================================
CREATE TABLE resources (
  id            VARCHAR(40) PRIMARY KEY,
  title         VARCHAR(160) NOT NULL,
  kind          VARCHAR(30) NOT NULL,
  minutes       INT NOT NULL,
  body          TEXT NOT NULL,
  published     TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO resources (id, title, kind, minutes, body) VALUES
  ('grounding', 'Grounding when your chest gets tight', 'Practice', 4,
   'Name five things you can see, four you can touch, three you can hear, two you can smell, one you can taste. Repeat until the room comes back.'),
  ('sleep', 'Getting to sleep with a loud mind', 'Guide', 6,
   'A racing mind at midnight is usually unfinished thinking. Give it somewhere to go before your head hits the pillow.'),
  ('asking', 'How to ask for help without shrinking', 'Essay', 5,
   'Asking is not a confession of weakness. It''s an invitation for someone to be useful to you.');

CREATE TABLE testimonials (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  quote         VARCHAR(280) NOT NULL,
  name          VARCHAR(60) NOT NULL,
  published     TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO testimonials (quote, name) VALUES
  ('First time in years I said the thing out loud.', 'D., Lagos'),
  ('Honest, but never harsh. That''s rare.', 'K., Abuja'),
  ('It''s the one hour a week that''s actually mine.', 'S., Port Harcourt');

CREATE TABLE faqs (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  question      VARCHAR(200) NOT NULL,
  answer        TEXT NOT NULL,
  sort_order    INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO faqs (question, answer, sort_order) VALUES
  ('Is this actual therapy?', 'No. SheListens is confidential listening support, not clinical therapy or medical advice. If you need clinical care, we will help you find it.', 1),
  ('How private are my conversations?', 'Sessions are private between you and your listener. We never log message content, and nobody else on the platform can read your conversations.', 2),
  ('Can I cancel anytime?', 'Yes. Cancel from your subscription page and you keep access until the end of the current period.', 3),
  ('What happens in a crisis?', 'Your listener can surface crisis-line information right inside the session, calmly and without ending the conversation.', 4),
  ('Who am I talking to?', 'One dedicated listener — the same person every session. No marketplace, no rotating cast, no AI.', 5);

-- Terms of Service / Privacy Policy / Confidentiality Policy — editable by
-- admin from Admin -> Legal rather than being hardcoded in the frontend.
CREATE TABLE legal_pages (
  page_key      VARCHAR(30) PRIMARY KEY,
  title         VARCHAR(120) NOT NULL,
  body          LONGTEXT NOT NULL,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO legal_pages (page_key, title, body) VALUES
  ('terms', 'Terms of Service', 'Placeholder text. Final wording for this policy is pending legal review.'),
  ('privacy', 'Privacy Policy', 'Placeholder text. Final wording for this policy is pending legal review.'),
  ('confidentiality', 'Confidentiality Policy', 'Placeholder text. Final wording for this policy is pending legal review.');

-- ============================================================
-- AUDIT LOG (admin-only, append-only)
-- ============================================================
CREATE TABLE audit_log (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  user_id       CHAR(36) NULL,
  action        VARCHAR(80) NOT NULL,
  kind          ENUM('auth','booking','payment','consent','admin','security','system') NOT NULL,
  result        VARCHAR(20) NOT NULL,
  ip            VARCHAR(45) NULL,
  device        VARCHAR(160) NULL,
  meta          JSON NULL,
  INDEX idx_kind (kind),
  INDEX idx_at (at),
  INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Support: a way to reach the listener outside a booked/open session,
-- since normal messaging only exists inside a live session window.
CREATE TABLE 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 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;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- PLATFORM SETTINGS (admin-editable, e.g. payment gateway keys)
-- See migrations/002_platform_settings.sql for adding this to an
-- already-imported database without re-running the whole file.
-- ============================================================
CREATE TABLE IF NOT EXISTS platform_settings (
  setting_key   VARCHAR(60) PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;