-- My Diaspora Nexus Membership Onboarding Schema
-- Version: 1.0
-- Purpose: Production tables for website membership application, ISP waiver validation,
-- USD 20 application fee, admin pre-approval, Calendly advisory booking, Zoom meeting,
-- post-meeting vetting, USD 100 annual membership payment, activation, email delivery,
-- event listening, notes, and audit history.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS membership_applications (
  id VARCHAR(64) PRIMARY KEY,
  reference_number VARCHAR(64) NOT NULL UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  phone_number VARCHAR(64) NOT NULL,
  country_of_residence VARCHAR(128) NOT NULL,
  city_region VARCHAR(128) NULL,
  professional_title VARCHAR(255) NOT NULL,
  employer_or_business VARCHAR(255) NULL,
  industry VARCHAR(128) NOT NULL,
  professional_summary TEXT NOT NULL,
  international_exposure_categories JSON NOT NULL,
  international_exposure_description TEXT NOT NULL,
  consent_accepted TINYINT(1) NOT NULL DEFAULT 0,
  isp_membership_number VARCHAR(100) NULL,
  status ENUM(
    'draft',
    'submitted',
    'application_fee_pending',
    'application_fee_waived',
    'application_fee_paid',
    'application_fee_failed',
    'pending_admin_preapproval',
    'requires_clarification',
    'advisory_booking_requested',
    'advisory_scheduled',
    'advisory_cancelled',
    'advisory_completed',
    'vetting_pending',
    'vetting_approved',
    'vetting_rejected',
    'vetting_deferred',
    'annual_membership_payment_pending',
    'annual_membership_payment_failed',
    'annual_membership_paid',
    'active_member',
    'renewal_due',
    'expired',
    'suspended',
    'rejected',
    'archived'
  ) NOT NULL DEFAULT 'submitted',
  application_fee_amount DECIMAL(10,2) NOT NULL DEFAULT 20.00,
  application_fee_currency CHAR(3) NOT NULL DEFAULT 'USD',
  application_fee_status ENUM('not_required','pending','waived','paid','failed','cancelled','exception') NOT NULL DEFAULT 'pending',
  annual_fee_amount DECIMAL(10,2) NOT NULL DEFAULT 100.00,
  annual_fee_currency CHAR(3) NOT NULL DEFAULT 'USD',
  annual_fee_status ENUM('not_due','pending','paid','failed','cancelled','exception') NOT NULL DEFAULT 'not_due',
  member_id VARCHAR(64) NULL,
  submitted_at DATETIME NULL,
  activated_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_membership_applications_email (email),
  INDEX idx_membership_applications_status (status),
  INDEX idx_membership_applications_reference (reference_number),
  INDEX idx_membership_applications_member (member_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_isp_waiver_validations (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NOT NULL,
  isp_membership_number VARCHAR(100) NOT NULL,
  client_name VARCHAR(255) NOT NULL,
  validation_provider VARCHAR(100) NOT NULL DEFAULT 'isp_registry',
  validation_status ENUM('valid','invalid','pending','error') NOT NULL DEFAULT 'pending',
  waiver_applied TINYINT(1) NOT NULL DEFAULT 0,
  validation_reason TEXT NULL,
  provider_reference VARCHAR(255) NULL,
  provider_payload JSON NULL,
  validated_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_waiver_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
  INDEX idx_waiver_application (application_id),
  INDEX idx_waiver_number (isp_membership_number),
  INDEX idx_waiver_status (validation_status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_documents (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NOT NULL,
  document_type VARCHAR(100) NOT NULL,
  file_name VARCHAR(255) NOT NULL,
  mime_type VARCHAR(150) NOT NULL,
  size_bytes BIGINT NOT NULL DEFAULT 0,
  storage_provider VARCHAR(100) NOT NULL DEFAULT 'local_or_object_storage',
  storage_key VARCHAR(500) NOT NULL,
  checksum_sha256 VARCHAR(128) NULL,
  status ENUM('uploaded','under_review','accepted','rejected','archived','deleted') NOT NULL DEFAULT 'uploaded',
  review_status ENUM('pending_review','accepted','rejected','not_required') NOT NULL DEFAULT 'pending_review',
  review_note TEXT NULL,
  reviewed_by VARCHAR(64) NULL,
  reviewed_at DATETIME NULL,
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_membership_documents_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
  INDEX idx_documents_application (application_id),
  INDEX idx_documents_status (status),
  INDEX idx_documents_type (document_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_payment_requests (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NOT NULL,
  stage ENUM('application_fee','annual_membership_fee','renewal_fee') NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  status ENUM('pending','sent','paid','failed','cancelled','expired','exception') NOT NULL DEFAULT 'pending',
  provider VARCHAR(64) NULL,
  legacy_payment_endpoint VARCHAR(255) NULL,
  legacy_status_endpoint VARCHAR(255) NULL,
  legacy_callback_endpoint VARCHAR(255) NULL,
  legacy_adapter_payload JSON NULL,
  payment_url VARCHAR(1000) NULL,
  gateway_reference VARCHAR(255) NULL,
  receipt_number VARCHAR(255) NULL,
  requested_by VARCHAR(64) NULL,
  requested_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  paid_at DATETIME NULL,
  expires_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_requests_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
  INDEX idx_payment_requests_application (application_id),
  INDEX idx_payment_requests_stage (stage),
  INDEX idx_payment_requests_status (status),
  INDEX idx_payment_requests_gateway (gateway_reference)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_payment_events (
  id VARCHAR(64) PRIMARY KEY,
  payment_request_id VARCHAR(64) NULL,
  application_id VARCHAR(64) NOT NULL,
  stage ENUM('application_fee','annual_membership_fee','renewal_fee') NOT NULL,
  provider VARCHAR(64) NOT NULL,
  event_type VARCHAR(100) NOT NULL,
  payment_status ENUM('successful','failed','cancelled','refunded','exception','pending') NOT NULL,
  gateway_reference VARCHAR(255) NULL,
  receipt_number VARCHAR(255) NULL,
  amount DECIMAL(10,2) NULL,
  currency CHAR(3) NULL,
  raw_payload JSON NULL,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  processed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_payment_events_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
  CONSTRAINT fk_payment_events_request FOREIGN KEY (payment_request_id) REFERENCES membership_payment_requests(id) ON DELETE SET NULL,
  INDEX idx_payment_events_application (application_id),
  INDEX idx_payment_events_gateway (gateway_reference),
  INDEX idx_payment_events_status (payment_status),
  INDEX idx_payment_events_processed (processed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_advisory_meetings (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NOT NULL,
  advisor_id VARCHAR(64) NULL,
  calendly_event_id VARCHAR(128) NULL,
  calendly_event_url VARCHAR(1000) NULL,
  calendly_invitee_uri VARCHAR(1000) NULL,
  calendly_event_uri VARCHAR(1000) NULL,
  scheduling_url VARCHAR(1000) NULL,
  utm_source VARCHAR(100) NULL,
  utm_medium VARCHAR(128) NULL,
  utm_campaign VARCHAR(128) NULL,
  utm_content VARCHAR(128) NULL,
  provider VARCHAR(64) NOT NULL DEFAULT 'calendly',
  status ENUM('booking_requested','scheduled','completed','missed','cancelled','reschedule_requested','closed') NOT NULL DEFAULT 'booking_requested',
  scheduled_at DATETIME NULL,
  timezone VARCHAR(100) NULL,
  zoom_provider VARCHAR(64) NOT NULL DEFAULT 'zoom',
  zoom_meeting_id VARCHAR(255) NULL,
  zoom_join_url VARCHAR(1000) NULL,
  zoom_start_url VARCHAR(1000) NULL,
  meeting_notes TEXT NULL,
  missed_reason TEXT NULL,
  cancellation_reason TEXT NULL,
  completed_at DATETIME NULL,
  created_by VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_advisory_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
  INDEX idx_advisory_application (application_id),
  INDEX idx_advisory_advisor (advisor_id),
  INDEX idx_advisory_status (status),
  INDEX idx_advisory_scheduled (scheduled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_calendly_events (
  id VARCHAR(64) PRIMARY KEY,
  advisor_id VARCHAR(64) NOT NULL,
  event_name VARCHAR(255) NOT NULL,
  event_url VARCHAR(1000) NOT NULL,
  event_code VARCHAR(128) NOT NULL,
  provider VARCHAR(64) NOT NULL DEFAULT 'calendly',
  active TINYINT(1) NOT NULL DEFAULT 1,
  metadata JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_calendly_events_advisor (advisor_id),
  INDEX idx_calendly_events_active (active),
  INDEX idx_calendly_events_code (event_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_calendly_webhook_events (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NULL,
  advisory_meeting_id VARCHAR(64) NULL,
  event_type VARCHAR(100) NOT NULL,
  event_uri VARCHAR(1000) NULL,
  invitee_uri VARCHAR(1000) NULL,
  raw_payload JSON NOT NULL,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  processed_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_calendly_webhook_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE SET NULL,
  CONSTRAINT fk_calendly_webhook_meeting FOREIGN KEY (advisory_meeting_id) REFERENCES membership_advisory_meetings(id) ON DELETE SET NULL,
  INDEX idx_calendly_webhook_application (application_id),
  INDEX idx_calendly_webhook_event_type (event_type),
  INDEX idx_calendly_webhook_processed (processed)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_vetting_decisions (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NOT NULL,
  advisory_meeting_id VARCHAR(64) NULL,
  decision ENUM('approved','rejected','deferred') NOT NULL,
  note TEXT NOT NULL,
  conditions JSON NULL,
  decided_by VARCHAR(64) NOT NULL,
  decided_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_vetting_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
  CONSTRAINT fk_vetting_advisory FOREIGN KEY (advisory_meeting_id) REFERENCES membership_advisory_meetings(id) ON DELETE SET NULL,
  INDEX idx_vetting_application (application_id),
  INDEX idx_vetting_decision (decision),
  INDEX idx_vetting_decided_by (decided_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS members (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NULL UNIQUE,
  application_reference VARCHAR(64) NULL,
  member_number VARCHAR(64) NOT NULL UNIQUE,
  full_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  phone_number VARCHAR(64) NULL,
  account_status ENUM('activation_pending','active','limited','suspended','archived') NOT NULL DEFAULT 'activation_pending',
  membership_status ENUM('active','renewal_due','grace_period','expired','access_restricted','suspended','archived','re_verification_required') NOT NULL DEFAULT 'active',
  membership_start_date DATE NOT NULL,
  membership_expiry_date DATE NOT NULL,
  activation_token_hash VARCHAR(255) NULL,
  activated_at DATETIME NULL,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_members_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE SET NULL,
  INDEX idx_members_status (membership_status),
  INDEX idx_members_email (email),
  INDEX idx_members_expiry (membership_expiry_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_email_deliveries (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NULL,
  member_id VARCHAR(64) NULL,
  recipient_email VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  template VARCHAR(128) NOT NULL,
  provider VARCHAR(100) NOT NULL DEFAULT 'legacy_sendMail',
  status ENUM('queued','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
  payload JSON NULL,
  provider_response JSON NULL,
  error_message TEXT NULL,
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_email_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE SET NULL,
  CONSTRAINT fk_email_member FOREIGN KEY (member_id) REFERENCES members(id) ON DELETE SET NULL,
  INDEX idx_email_application (application_id),
  INDEX idx_email_member (member_id),
  INDEX idx_email_status (status),
  INDEX idx_email_template (template)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_notes (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NOT NULL,
  note_type ENUM('admin','clarification','advisory','vetting','payment','system') NOT NULL DEFAULT 'admin',
  note TEXT NOT NULL,
  visibility ENUM('internal','applicant_visible') NOT NULL DEFAULT 'internal',
  created_by VARCHAR(64) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_membership_notes_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
  INDEX idx_notes_application (application_id),
  INDEX idx_notes_type (note_type),
  INDEX idx_notes_created_by (created_by)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_audit_logs (
  id VARCHAR(64) PRIMARY KEY,
  actor_id VARCHAR(64) NOT NULL,
  actor_role VARCHAR(100) NULL,
  action VARCHAR(150) NOT NULL,
  entity_type VARCHAR(100) NOT NULL,
  entity_id VARCHAR(64) NOT NULL,
  previous_value JSON NULL,
  new_value JSON NULL,
  metadata JSON NULL,
  request_id VARCHAR(100) NULL,
  ip_address VARCHAR(64) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_audit_entity (entity_type, entity_id),
  INDEX idx_audit_actor (actor_id),
  INDEX idx_audit_action (action),
  INDEX idx_audit_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_status_history (
  id VARCHAR(64) PRIMARY KEY,
  application_id VARCHAR(64) NOT NULL,
  from_status VARCHAR(100) NULL,
  to_status VARCHAR(100) NOT NULL,
  reason TEXT NULL,
  changed_by VARCHAR(64) NOT NULL,
  changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_status_history_application FOREIGN KEY (application_id) REFERENCES membership_applications(id) ON DELETE CASCADE,
  INDEX idx_status_history_application (application_id),
  INDEX idx_status_history_to_status (to_status),
  INDEX idx_status_history_changed_at (changed_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_configuration (
  id VARCHAR(64) PRIMARY KEY,
  config_key VARCHAR(150) NOT NULL UNIQUE,
  config_value JSON NOT NULL,
  description TEXT NULL,
  updated_by VARCHAR(64) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_config_key (config_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO membership_configuration (id, config_key, config_value, description)
VALUES
  ('cfg_application_fee', 'application_fee', JSON_OBJECT('amount', 20, 'currency', 'USD', 'waivedForValidatedIspMembers', true), 'USD 20 application fee and ISP waiver rule'),
  ('cfg_annual_membership_fee', 'annual_membership_fee', JSON_OBJECT('amount', 100, 'currency', 'USD', 'renewalFrequency', 'annually'), 'USD 100 annual renewable membership fee'),
  ('cfg_advisory_meeting', 'advisory_meeting', JSON_OBJECT('provider', 'calendly', 'meetingProvider', 'zoom', 'defaultEventCode', 'MEMBERSHIP_ADVISORY'), 'Calendly advisory booking with Zoom meeting')
ON DUPLICATE KEY UPDATE config_value = VALUES(config_value), updated_at = CURRENT_TIMESTAMP;

SET FOREIGN_KEY_CHECKS = 1;