-- My Diaspora Connect workflow schema
-- Designed for trusted, iterative, consultant-mediated matching.

CREATE TABLE IF NOT EXISTS connect_members (
  id VARCHAR(80) PRIMARY KEY,
  full_name VARCHAR(180) NOT NULL,
  email VARCHAR(180) NOT NULL UNIQUE,
  location VARCHAR(160),
  profession VARCHAR(160),
  industry VARCHAR(160),
  status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
  interests JSON,
  joined_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS connect_requests (
  id VARCHAR(90) PRIMARY KEY,
  member_id VARCHAR(80) NOT NULL,
  request_type ENUM('professional','personal','career') NOT NULL,
  title VARCHAR(220) NOT NULL,
  description TEXT NOT NULL,
  goals JSON,
  preferences JSON,
  privacy_level ENUM('consultant_only','anonymous_board','private') NOT NULL DEFAULT 'consultant_only',
  status ENUM('draft','submitted','under_review','needs_clarification','active_matching','match_in_progress','reopened','paused','successful_match','closed','escalated') NOT NULL DEFAULT 'submitted',
  assigned_consultant_id VARCHAR(80),
  assigned_consultant_name VARCHAR(180),
  review_notes TEXT,
  current_iteration INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_connect_requests_member (member_id),
  INDEX idx_connect_requests_status (status),
  INDEX idx_connect_requests_type (request_type)
);

CREATE TABLE IF NOT EXISTS connect_request_reviews (
  id VARCHAR(90) PRIMARY KEY,
  request_id VARCHAR(90) NOT NULL,
  reviewer_id VARCHAR(80) NOT NULL,
  decision ENUM('approved','rejected','needs_clarification','under_review','active_matching') NOT NULL,
  notes TEXT,
  reviewed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_reviews_request (request_id)
);

CREATE TABLE IF NOT EXISTS connect_public_opportunities (
  id VARCHAR(90) PRIMARY KEY,
  request_id VARCHAR(90) NOT NULL,
  category ENUM('professional','personal','career') NOT NULL,
  anonymous_title VARCHAR(220) NOT NULL,
  anonymous_description TEXT NOT NULL,
  visibility_status ENUM('draft','published','expired','withdrawn') NOT NULL DEFAULT 'draft',
  interest_count INT NOT NULL DEFAULT 0,
  published_at DATETIME,
  expires_at DATETIME,
  INDEX idx_connect_opportunities_request (request_id),
  INDEX idx_connect_opportunities_visibility (visibility_status)
);

CREATE TABLE IF NOT EXISTS connect_expressions_of_interest (
  id VARCHAR(90) PRIMARY KEY,
  opportunity_id VARCHAR(90) NOT NULL,
  interested_member_id VARCHAR(80) NOT NULL,
  message TEXT NOT NULL,
  relevant_background TEXT,
  status ENUM('submitted','shortlisted','declined','converted_to_match') NOT NULL DEFAULT 'submitted',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_interest_opportunity (opportunity_id),
  INDEX idx_connect_interest_member (interested_member_id)
);

CREATE TABLE IF NOT EXISTS connect_match_attempts (
  id VARCHAR(90) PRIMARY KEY,
  request_id VARCHAR(90) NOT NULL,
  attempt_number INT NOT NULL,
  member_a_id VARCHAR(80) NOT NULL,
  member_b_id VARCHAR(80) NOT NULL,
  proposed_by VARCHAR(80) NOT NULL,
  match_reason TEXT NOT NULL,
  status ENUM('proposed','awaiting_member_acceptance','accepted_by_both','meeting_scheduled','meeting_completed','successful','not_a_fit','no_show','rescheduled','cancelled','escalated') NOT NULL DEFAULT 'proposed',
  outcome ENUM('good_fit','not_fit','no_show','safety_concern','pending') DEFAULT 'pending',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  closed_at DATETIME,
  INDEX idx_connect_match_request (request_id),
  INDEX idx_connect_match_status (status)
);

CREATE TABLE IF NOT EXISTS connect_meetings (
  id VARCHAR(90) PRIMARY KEY,
  match_attempt_id VARCHAR(90),
  request_id VARCHAR(90),
  meeting_type ENUM('member_match','career_consultation') NOT NULL,
  title VARCHAR(220) NOT NULL,
  zoom_meeting_id VARCHAR(120),
  zoom_provider VARCHAR(80),
  zoom_payload JSON,
  join_url TEXT NOT NULL,
  scheduled_start DATETIME NOT NULL,
  duration_minutes INT NOT NULL DEFAULT 100,
  status ENUM('scheduled','completed','missed','cancelled','rescheduled','feedback_pending','closed') NOT NULL DEFAULT 'scheduled',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_meetings_match (match_attempt_id),
  INDEX idx_connect_meetings_request (request_id),
  INDEX idx_connect_meetings_status (status)
);

CREATE TABLE IF NOT EXISTS connect_meeting_participants (
  id VARCHAR(90) PRIMARY KEY,
  meeting_id VARCHAR(90) NOT NULL,
  participant_id VARCHAR(80) NOT NULL,
  participant_role ENUM('member','consultant','admin') NOT NULL DEFAULT 'member',
  join_url TEXT,
  attendance_status ENUM('pending','joined','missed') NOT NULL DEFAULT 'pending',
  joined_at DATETIME,
  left_at DATETIME,
  INDEX idx_connect_participants_meeting (meeting_id),
  INDEX idx_connect_participants_user (participant_id)
);

CREATE TABLE IF NOT EXISTS connect_feedback (
  id VARCHAR(90) PRIMARY KEY,
  meeting_id VARCHAR(90) NOT NULL,
  member_id VARCHAR(80) NOT NULL,
  attended TINYINT(1) NOT NULL DEFAULT 0,
  wants_to_continue TINYINT(1),
  match_score INT,
  reason_code VARCHAR(120),
  private_notes TEXT,
  safety_concern TINYINT(1) NOT NULL DEFAULT 0,
  submitted_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_feedback_meeting (meeting_id),
  INDEX idx_connect_feedback_member (member_id)
);

CREATE TABLE IF NOT EXISTS connect_career_cases (
  id VARCHAR(90) PRIMARY KEY,
  request_id VARCHAR(90) NOT NULL,
  member_id VARCHAR(80) NOT NULL,
  assigned_consultant_id VARCHAR(80),
  assigned_consultant_name VARCHAR(180),
  status ENUM('submitted','assigned','support_plan_pending','support_plan_active','session_scheduled','in_progress','waiting_on_member','completed','closed') NOT NULL DEFAULT 'support_plan_pending',
  goal TEXT,
  support_plan JSON,
  next_session_at DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_connect_career_member (member_id),
  INDEX idx_connect_career_request (request_id),
  INDEX idx_connect_career_status (status)
);

CREATE TABLE IF NOT EXISTS connect_career_sessions (
  id VARCHAR(90) PRIMARY KEY,
  case_id VARCHAR(90) NOT NULL,
  meeting_id VARCHAR(90),
  session_type VARCHAR(120) NOT NULL,
  status ENUM('scheduled','completed','missed','cancelled') NOT NULL DEFAULT 'scheduled',
  deliverables JSON,
  scheduled_at DATETIME,
  completed_at DATETIME,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_career_sessions_case (case_id)
);

CREATE TABLE IF NOT EXISTS connect_request_revisions (
  id VARCHAR(90) PRIMARY KEY,
  request_id VARCHAR(90) NOT NULL,
  revision_reason TEXT NOT NULL,
  old_preferences JSON,
  new_preferences JSON,
  created_by VARCHAR(80) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_revisions_request (request_id)
);

CREATE TABLE IF NOT EXISTS connect_admin_notes (
  id VARCHAR(90) PRIMARY KEY,
  entity_type ENUM('connect_request','connect_match','connect_meeting','connect_member','connect_opportunity','connect_career_case','connect_safety_flag') NOT NULL,
  entity_id VARCHAR(90) NOT NULL,
  note TEXT NOT NULL,
  visibility ENUM('internal','member_visible') NOT NULL DEFAULT 'internal',
  created_by VARCHAR(80) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_notes_entity (entity_type, entity_id)
);

CREATE TABLE IF NOT EXISTS connect_safety_flags (
  id VARCHAR(90) PRIMARY KEY,
  request_id VARCHAR(90),
  match_attempt_id VARCHAR(90),
  meeting_id VARCHAR(90),
  reported_by VARCHAR(80) NOT NULL,
  severity ENUM('low','medium','high','critical','member_reported') NOT NULL DEFAULT 'low',
  issue_type VARCHAR(120) NOT NULL,
  description TEXT NOT NULL,
  status ENUM('open','reviewing','resolved','dismissed') NOT NULL DEFAULT 'open',
  reviewed_by VARCHAR(80),
  resolution_notes TEXT,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  resolved_at DATETIME,
  INDEX idx_connect_safety_request (request_id),
  INDEX idx_connect_safety_status (status)
);

CREATE TABLE IF NOT EXISTS connect_audit_logs (
  id VARCHAR(90) PRIMARY KEY,
  actor_id VARCHAR(80) NOT NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(120) NOT NULL,
  entity_id VARCHAR(90) NOT NULL,
  metadata JSON,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_audit_entity (entity_type, entity_id),
  INDEX idx_connect_audit_actor (actor_id),
  INDEX idx_connect_audit_action (action)
);

CREATE TABLE IF NOT EXISTS connect_workflow_actions (
  id VARCHAR(90) PRIMARY KEY,
  actor_id VARCHAR(80) NOT NULL,
  action VARCHAR(120) NOT NULL,
  payload JSON,
  result JSON,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_connect_actions_actor (actor_id),
  INDEX idx_connect_actions_action (action)
);