-- 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','scheduled') 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,
  attachment_url  VARCHAR(255) NULL,
  attachment_name VARCHAR(255) NULL,
  attachment_mime VARCHAR(100) NULL,
  attachment_size INT          NULL,
  call_event VARCHAR(20) NULL,
  call_video TINYINT(1) 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', 'DRAFTING NOTE — REMOVE BEFORE PUBLISHING: This document was drafted to match how SheListens actually works today, but it has not been reviewed by a lawyer. Before this goes live, have it checked by someone qualified in Nigerian law — in particular the payment/refund terms, the liability and disclaimer sections, and anything touching mental health, given how closely this service sits to that space without being one.

1. ABOUT SHELISTENS
SheListens isn''t your regular therapy session. It''s about your need to be self-aware — awareness that becomes self-actualization and helps you reach your full potential. This is about the need to be seen. SheListens connects you with one dedicated listener for confidential text, voice, and video conversations. It is a paid listening and support service — it is not therapy, counseling, psychiatry, or any other form of licensed medical or mental health treatment, and your listener is not necessarily a licensed clinician. If you are in crisis or need medical or psychiatric care, please contact a licensed professional or emergency services — SheListens is not a substitute for either.

2. ELIGIBILITY
You must be at least 18 years old to create an account and use SheListens. By registering, you confirm that you meet this requirement and that the information you provide is accurate.

3. YOUR ACCOUNT
You''re responsible for keeping your login details secure and for everything that happens under your account. Tell us right away if you think someone else has accessed it. We may suspend or close an account that we reasonably believe is being misused, shared, or used to harm another person.

4. PLANS, BILLING & PAYMENT
SheListens is offered on weekly, monthly, and yearly plans, each priced and described on the Pricing page and inside the app. Payments are processed by Flutterwave; we never see or store your full card details. Switching plans while you''re already on one doesn''t cut your current plan short — the new plan is charged for at checkout and takes over once your current period ends. Cancelling a plan stops future renewal but doesn''t refund the time already paid for; you keep access until the end of the period you already paid for. We don''t offer partial refunds for unused time within a period unless required by law.

5. BOOKING, RESCHEDULING & MISSED SESSIONS
Sessions are 45 minutes by default (the exact length is shown at booking) and you may book up to three sessions per week. If you don''t join a session you''ve booked, it''s marked as missed and you''ll need to reschedule it — missed sessions don''t carry over or extend your plan. Your listener may end a session early if it runs into a safety concern, a technical problem, or a clear policy violation.

6. VIDEO SESSIONS & FEES
Text and voice calling are included with every session. Video is available if your plan includes it, or for an additional fee shown at booking. If a session with video is cancelled or rescheduled before it happens, no fee is charged for the cancelled slot; a separately paid video fee doesn''t automatically transfer to a new booking unless the app carries it over for you as part of a reschedule.

7. ACCEPTABLE USE
Treat your listener and the platform with basic respect. You agree not to: threaten, harass, or abuse your listener or anyone else; attempt to record, screenshot, or redistribute a session without the other person''s consent; try to access another user''s account or data; use the service for anything illegal; or attempt to disrupt, reverse-engineer, or interfere with how the platform works. We may suspend or terminate access for violating this section.

8. INTELLECTUAL PROPERTY
The SheListens name, app, design, and underlying software are owned by us and may not be copied, resold, or used to build a competing service. You keep ownership of anything you personally write or share (journal entries, messages, uploaded files) — we don''t claim ownership of it, though our Privacy Policy explains how it''s stored and who can access it.

9. TERMINATION
You may stop using SheListens and cancel your plan at any time from Settings. We may suspend or terminate an account for violating these Terms, for safety reasons, or for non-payment. Where reasonably possible, we''ll let you know why.

10. DISCLAIMERS
SheListens is provided "as is." We work to keep the service reliable and available, but we don''t guarantee it will be uninterrupted, error-free, or available at all times — technical issues, network problems, or maintenance can affect access. We don''t guarantee any particular outcome from using the service.

11. LIMITATION OF LIABILITY
To the fullest extent permitted by law, SheListens and its owners are not liable for indirect, incidental, or consequential damages arising from your use of the service. Nothing in these Terms limits liability that cannot legally be limited, including in cases of gross negligence or willful misconduct.

12. GOVERNING LAW
These Terms are governed by the laws of the Federal Republic of Nigeria. Any dispute arising from these Terms will be handled in the courts of Nigeria, unless otherwise required by applicable law.

13. CHANGES TO THESE TERMS
We may update these Terms from time to time. If we make a material change, we''ll let you know before it takes effect — through the app, by email, or both. Continuing to use SheListens after a change takes effect means you accept the updated Terms.

14. CONTACT
Questions about these Terms can be sent through Support inside the app, or to the contact details listed on our website.'),
  ('privacy', 'Privacy Policy', 'DRAFTING NOTE — REMOVE BEFORE PUBLISHING: This document reflects how SheListens actually handles data today, but it hasn''t been reviewed by a lawyer or a data-protection specialist. Before this goes live, have it checked against the Nigeria Data Protection Act 2023 (NDPA) — in particular the sections on your legal basis for processing, data-subject request handling, and cross-border transfer if your servers are hosted outside Nigeria.

1. OVERVIEW
This policy explains what information SheListens collects, why, and who can see it. We built SheListens around one idea: your conversations are private. This policy is where we explain exactly what that means in practice, not just as a promise.

2. INFORMATION WE COLLECT
Account information: your name, email, and (if you choose to set one) a private alias your listener sees instead of your real name. Session content: text messages, voice notes, file attachments, and call activity from your live sessions. Wellness data: mood check-ins and private journal entries you choose to add. Booking and payment information: session times, plan selection, and payment records (processed by Flutterwave — we do not store your full card number). Usage information: basic technical data like device type and login activity, used for security and to keep the app working properly.

3. HOW WE USE YOUR INFORMATION
We use your information to provide the service itself — booking sessions, connecting you with your listener, running live text/voice/video sessions, processing payments, and keeping your account secure. We also use it to enforce our Terms of Service, respond to support requests, and improve the app. We do not sell your personal information, and we do not use your session content to train any AI model or serve you advertising.

4. WHO CAN SEE WHAT
This is the part most people want spelled out plainly, so here it is directly. Your listener can see: the full transcript of your live sessions (text, voice notes, and attachments) — during the session and afterward, so they can review it and write private notes for their own reference; your mood check-ins, so they have context on how you''ve been doing. Your listener cannot see your private journal entries — those are visible only to you. You cannot see the transcript of a past session again once it''s over; only its date, type, and duration remain visible to you, for privacy reasons on both sides of the conversation. Our administrators can see account-level information (billing, session scheduling, support tickets, safety flags) to run the platform and respond to abuse or safety concerns, but do not routinely read session transcripts. We never share your session content with anyone outside SheListens except as described in the Confidentiality Policy''s safety exception, or where required by law.

5. PAYMENT INFORMATION
Payments are handled by Flutterwave, a licensed third-party payment processor. We receive confirmation that a payment succeeded and basic transaction details — we never receive or store your full card number, PIN, or banking password.

6. DATA RETENTION
We keep your account and session data for as long as your account is active, plus a reasonable period afterward for legal, safety, and dispute-resolution purposes. You can request deletion of your account and personal data at any time (see Section 8) — some information may be retained longer where we''re legally required to.

7. DATA SECURITY
We use standard security practices — encrypted connections, access controls limiting who can reach what data, and audit logging of sensitive actions — to protect your information. No system is perfectly secure, and we can''t guarantee absolute security, but we take it seriously and will notify affected users if we become aware of a breach that puts your data at risk.

8. YOUR RIGHTS
Under the Nigeria Data Protection Act, you have the right to know what data we hold about you, to correct inaccurate data, to request deletion of your data, and to withdraw consent for optional processing. You can exercise most of these directly from Settings, or by contacting Support.

9. CHILDREN''S PRIVACY
SheListens is for adults 18 and older. We don''t knowingly collect data from anyone under 18. If we learn an account belongs to a minor, we will close it.

10. COOKIES & TRACKING
We use the minimum technical storage needed to keep you logged in and the app working correctly (for example, remembering your session token). We don''t use third-party advertising trackers.

11. CHANGES TO THIS POLICY
If we make a material change to how we handle your data, we''ll notify you through the app, by email, or both, before the change takes effect.

12. CONTACT
Questions about this policy, or requests about your data, can be sent through Support inside the app, or to the contact details listed on our website.'),
  ('confidentiality', 'Confidentiality Policy', 'DRAFTING NOTE — REMOVE BEFORE PUBLISHING: This document hasn''t been reviewed by a lawyer. The "risk of serious harm" exception in Section 4 is the most legally sensitive part of this whole policy — have that wording specifically checked against Nigerian law before publishing, since what a service is actually permitted or obligated to do in a genuine emergency is a real legal question, not just a product decision.

1. OUR COMMITMENT
SheListens isn''t your regular therapy session. It''s about your need to be self-aware — awareness that becomes self-actualization and helps you reach your full potential. That only works if this is a place you can actually be honest, so confidentiality isn''t a footnote here — it''s the foundation everything else is built on.

2. WHAT STAYS CONFIDENTIAL
Everything you say in a session — text messages, voice notes, files you share, and anything said on a call — stays between you and your listener. We don''t sell it, we don''t use it to train AI, and we don''t show it to other clients or the public.

3. WHO CAN ACCESS YOUR CONVERSATIONS
Your listener can review your session transcripts after the fact, and can write private notes for their own reference in working with you — this is what lets them remember your story between sessions instead of starting fresh each time. Once a session ends, you yourself won''t be able to reopen its transcript — that''s intentional, and applies equally to your listener''s private notes, which are never shown to you either. Our administrators can see account and scheduling information to run the platform, but don''t routinely read session content. Nobody else — no other client, no outside company, no advertiser — ever has access to what you share in a session.

4. THE ONE EXCEPTION: RISK OF SERIOUS HARM
Your conversations stay private, with one exception: if there is a real risk of serious harm — to you or to someone else — your listener may share crisis resources with you, and may need to contact emergency services or another appropriate authority. This isn''t used lightly, and it isn''t used to monitor or judge what you share; it exists because your safety comes before confidentiality in a genuine emergency. We may also be required to disclose information in response to a valid legal order (such as a court order) — this is rare, and we push back on requests that aren''t legally required.

5. CRISIS RESOURCES
If a conversation touches on serious risk, your listener may surface crisis resources during the session. We log that this happened, as an event, so the moment isn''t lost — we never log what was actually said.

6. ANONYMITY
You can set a private alias for your listener to use instead of your real name, if that helps you feel more comfortable being open. Your listener still knows this is a real, consistent relationship with the same person each time — the alias is about comfort, not anonymity from the service itself.

7. WHAT SHELISTENS ISN''T
Being confidential doesn''t mean SheListens is therapy, counseling, or a licensed clinical relationship — see our Terms of Service for what SheListens is and isn''t. Your listener is there to listen and support you honestly, within the limits described in this policy.

8. CHANGES TO THIS POLICY
If we materially change what "confidential" means on SheListens, we will tell you before the change takes effect — this is not a policy we''ll quietly loosen.

9. CONTACT
Questions about this policy can be sent through Support inside the app, or to the contact details listed on our website.');

-- ============================================================
-- 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;