CREATE TABLE IF NOT EXISTS membership_cycles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  starts_on DATE NOT NULL,
  ends_on DATE NOT NULL,
  status ENUM('draft','active','closed') NOT NULL DEFAULT 'draft',
  publish_digital_ids TINYINT(1) NOT NULL DEFAULT 0,
  publish_certificates TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_membership_cycle_name (name),
  KEY idx_membership_cycle_dates (starts_on, ends_on)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS member_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  membership_subscription_id BIGINT UNSIGNED NULL,
  membership_cycle_id BIGINT UNSIGNED NULL,
  document_type ENUM('membership_id','membership_certificate') NOT NULL,
  file_url VARCHAR(1000) NOT NULL,
  published_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_member_document (user_id, membership_cycle_id, document_type),
  KEY idx_member_documents_visible (user_id, published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS event_attendee_documents (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NOT NULL,
  document_type ENUM('participation_certificate','event_nametag') NOT NULL,
  file_url VARCHAR(1000) NOT NULL,
  published_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_event_attendee_document (event_id, user_id, document_type),
  KEY idx_event_documents_visible (user_id, event_id, published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS payment_transactions_v3 (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  charge_id VARCHAR(100) NOT NULL,
  payment_type ENUM('membership','event_ticket','marketplace') NOT NULL,
  reference_id BIGINT UNSIGNED NOT NULL,
  amount DECIMAL(14,2) NOT NULL,
  currency CHAR(3) NOT NULL DEFAULT 'MWK',
  mobile VARCHAR(40) NOT NULL,
  operator_ref VARCHAR(100) NOT NULL,
  status ENUM('pending','successful','failed','cancelled') NOT NULL DEFAULT 'pending',
  provider_reference VARCHAR(255) NULL,
  provider_payload JSON NULL,
  verified_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_paychangu_charge (charge_id),
  KEY idx_paychangu_user (user_id, created_at),
  KEY idx_paychangu_reference (payment_type, reference_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;