-- ============================================================
-- MedAI Database Schema — Supabase (PostgreSQL)
-- Run this in your Supabase SQL Editor
-- ============================================================

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- ── USERS ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS users (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  email           TEXT UNIQUE NOT NULL,
  password_hash   TEXT NOT NULL,
  first_name      TEXT NOT NULL,
  last_name       TEXT NOT NULL,
  phone           TEXT,
  is_premium      BOOLEAN DEFAULT FALSE,
  plan_id         TEXT,
  plan_expires_at TIMESTAMPTZ,
  paystack_customer_code TEXT,
  daily_count     INTEGER DEFAULT 0,
  daily_reset_at  DATE DEFAULT CURRENT_DATE,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── MEDICAL PROFILES ──────────────────────────────────────────
CREATE TABLE IF NOT EXISTS medical_profiles (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  card_id         TEXT UNIQUE NOT NULL,
  date_of_birth   DATE NOT NULL,
  sex             TEXT NOT NULL CHECK (sex IN ('Male','Female','Other')),
  blood_group     TEXT NOT NULL,
  height_cm       NUMERIC(5,1),
  weight_kg       NUMERIC(5,1),
  allergies       TEXT[] DEFAULT '{}',
  conditions      TEXT[] DEFAULT '{}',
  medications     TEXT[] DEFAULT '{}',
  emergency_contact_name  TEXT,
  emergency_contact_phone TEXT,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── DIAGNOSIS SESSIONS ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS diagnosis_sessions (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID REFERENCES users(id) ON DELETE SET NULL,
  session_token   TEXT UNIQUE NOT NULL,  -- for anonymous users
  specialty       TEXT DEFAULT 'General Medicine',
  messages        JSONB DEFAULT '[]',    -- full conversation history
  diagnosis_summary TEXT,
  severity        TEXT CHECK (severity IN ('low','moderate','high','emergency')),
  conditions_suspected TEXT[],
  medications_recommended TEXT[],
  referred_hospital TEXT,
  image_urls      TEXT[] DEFAULT '{}',
  vitals_snapshot JSONB,
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── VITALS RECORDS ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS vitals_records (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id         UUID REFERENCES users(id) ON DELETE CASCADE,
  heart_rate      INTEGER,
  spo2            NUMERIC(4,1),
  hrv_ms          INTEGER,
  respiratory_rate INTEGER,
  temperature_c   NUMERIC(4,1),
  bp_systolic     INTEGER,
  bp_diastolic    INTEGER,
  measurement_method TEXT DEFAULT 'ppg_camera',
  notes           TEXT,
  recorded_at     TIMESTAMPTZ DEFAULT NOW()
);

-- ── SUBSCRIPTIONS ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS subscriptions (
  id                  UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id             UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  plan_id             TEXT NOT NULL,
  billing_cycle       TEXT NOT NULL CHECK (billing_cycle IN ('monthly','annual')),
  amount_kobo         INTEGER NOT NULL,
  currency            TEXT DEFAULT 'NGN',
  paystack_reference  TEXT UNIQUE NOT NULL,
  paystack_plan_code  TEXT,
  status              TEXT DEFAULT 'active' CHECK (status IN ('active','cancelled','expired','pending')),
  started_at          TIMESTAMPTZ DEFAULT NOW(),
  expires_at          TIMESTAMPTZ,
  cancelled_at        TIMESTAMPTZ,
  created_at          TIMESTAMPTZ DEFAULT NOW()
);

-- ── HOSPITALS CACHE ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS hospitals (
  id              UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  name            TEXT NOT NULL,
  address         TEXT,
  city            TEXT,
  state           TEXT,
  country         TEXT DEFAULT 'Nigeria',
  latitude        NUMERIC(10,7),
  longitude       NUMERIC(10,7),
  type            TEXT CHECK (type IN ('teaching','govt','private','clinic','pharmacy')),
  phone           TEXT,
  email           TEXT,
  whatsapp        TEXT,
  website         TEXT,
  emergency_24hr  BOOLEAN DEFAULT FALSE,
  specialties     TEXT[] DEFAULT '{}',
  verified        BOOLEAN DEFAULT FALSE,
  source          TEXT DEFAULT 'manual',
  created_at      TIMESTAMPTZ DEFAULT NOW(),
  updated_at      TIMESTAMPTZ DEFAULT NOW()
);

-- ── USAGE LOGS ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS usage_logs (
  id          UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id     UUID REFERENCES users(id) ON DELETE SET NULL,
  ip_address  INET,
  action      TEXT NOT NULL,
  meta        JSONB,
  created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- ── INDEXES ──────────────────────────────────────────────────
CREATE INDEX IF NOT EXISTS idx_medical_profiles_user_id ON medical_profiles(user_id);
CREATE INDEX IF NOT EXISTS idx_diagnosis_sessions_user_id ON diagnosis_sessions(user_id);
CREATE INDEX IF NOT EXISTS idx_diagnosis_sessions_created ON diagnosis_sessions(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_vitals_user_id ON vitals_records(user_id);
CREATE INDEX IF NOT EXISTS idx_vitals_recorded_at ON vitals_records(recorded_at DESC);
CREATE INDEX IF NOT EXISTS idx_subscriptions_user_id ON subscriptions(user_id);
CREATE INDEX IF NOT EXISTS idx_hospitals_state ON hospitals(state);
CREATE INDEX IF NOT EXISTS idx_hospitals_coords ON hospitals(latitude, longitude);
CREATE INDEX IF NOT EXISTS idx_usage_logs_user_id ON usage_logs(user_id);
CREATE INDEX IF NOT EXISTS idx_usage_logs_created ON usage_logs(created_at DESC);

-- ── UPDATED_AT TRIGGER ────────────────────────────────────────
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN NEW.updated_at = NOW(); RETURN NEW; END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
  BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER trg_medical_profiles_updated_at
  BEFORE UPDATE ON medical_profiles FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER trg_diagnosis_sessions_updated_at
  BEFORE UPDATE ON diagnosis_sessions FOR EACH ROW EXECUTE FUNCTION update_updated_at();
CREATE TRIGGER trg_hospitals_updated_at
  BEFORE UPDATE ON hospitals FOR EACH ROW EXECUTE FUNCTION update_updated_at();

-- ── ROW LEVEL SECURITY ────────────────────────────────────────
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE medical_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE diagnosis_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE vitals_records ENABLE ROW LEVEL SECURITY;
ALTER TABLE subscriptions ENABLE ROW LEVEL SECURITY;

-- Users can only access their own data
CREATE POLICY "users_own_data" ON users
  FOR ALL USING (auth.uid()::TEXT = id::TEXT);

CREATE POLICY "profiles_own_data" ON medical_profiles
  FOR ALL USING (auth.uid()::TEXT = user_id::TEXT);

CREATE POLICY "sessions_own_data" ON diagnosis_sessions
  FOR ALL USING (auth.uid()::TEXT = user_id::TEXT);

CREATE POLICY "vitals_own_data" ON vitals_records
  FOR ALL USING (auth.uid()::TEXT = user_id::TEXT);

-- Hospitals are public read
CREATE POLICY "hospitals_public_read" ON hospitals
  FOR SELECT USING (true);