-- MyIMM / MemberSync production schema reconciliation
-- Source audited: immadmin_membersync.sql (2026-08-31)
-- Target: MariaDB 10.5+ / MySQL 8.0+
-- Take a full database backup before running this file.

SET NAMES utf8mb4;
SET @migration_name = '20260831_production_schema_reconciliation';

CREATE TABLE IF NOT EXISTS schema_migrations (
  migration_name VARCHAR(190) NOT NULL PRIMARY KEY,
  applied_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  notes VARCHAR(500) NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Re-runnable helpers for columns and indexes.
DELIMITER $$
DROP PROCEDURE IF EXISTS ms_add_column$$
CREATE PROCEDURE ms_add_column(IN p_table VARCHAR(64), IN p_column VARCHAR(64), IN p_definition TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema = DATABASE() AND table_name = p_table AND column_name = p_column
  ) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD COLUMN `', p_column, '` ', p_definition);
    PREPARE statement FROM @ddl; EXECUTE statement; DEALLOCATE PREPARE statement;
  END IF;
END$$

DROP PROCEDURE IF EXISTS ms_add_index$$
CREATE PROCEDURE ms_add_index(IN p_table VARCHAR(64), IN p_index VARCHAR(64), IN p_columns TEXT, IN p_unique TINYINT)
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.statistics
    WHERE table_schema = DATABASE() AND table_name = p_table AND index_name = p_index
  ) THEN
    SET @ddl = CONCAT('ALTER TABLE `', p_table, '` ADD ', IF(p_unique = 1, 'UNIQUE ', ''),
                      'INDEX `', p_index, '` (', p_columns, ')');
    PREPARE statement FROM @ddl; EXECUTE statement; DEALLOCATE PREPARE statement;
  END IF;
END$$
DELIMITER ;

-- Event activities and paid-attendee participation.
CREATE TABLE IF NOT EXISTS event_activities (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT NOT NULL,
  name VARCHAR(255) NOT NULL,
  summary VARCHAR(500) NULL,
  details_html MEDIUMTEXT NULL,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  location VARCHAR(255) NULL,
  has_capacity_limit TINYINT(1) NOT NULL DEFAULT 0,
  capacity INT UNSIGNED NULL,
  registration_opens_at DATETIME NULL,
  registration_closes_at DATETIME NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'published',
  created_by BIGINT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_event_activities_event_status (event_id, status, starts_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_activity_images (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  activity_id BIGINT NOT NULL,
  image_url VARCHAR(500) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_event_activity_images_sort (activity_id, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_activity_registrations (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  activity_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'registered',
  registered_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  cancelled_at DATETIME NULL,
  UNIQUE KEY uq_activity_user (activity_id, user_id),
  KEY idx_activity_registrations_status (activity_id, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Resource library.
CREATE TABLE IF NOT EXISTS resource_types (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(120) NOT NULL,
  description VARCHAR(500) NULL,
  icon VARCHAR(50) NOT NULL DEFAULT 'folder',
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_resource_types_slug (slug),
  KEY idx_resource_types_active_sort (is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS resources (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  resource_type_id BIGINT NOT NULL,
  title VARCHAR(255) NOT NULL,
  description TEXT NULL,
  thumbnail_url VARCHAR(500) NULL,
  file_url VARCHAR(500) NOT NULL,
  media_type VARCHAR(20) NOT NULL,
  mime_type VARCHAR(120) NOT NULL,
  file_size BIGINT UNSIGNED NOT NULL DEFAULT 0,
  duration_seconds INT UNSIGNED NULL,
  view_count INT UNSIGNED NOT NULL DEFAULT 0,
  download_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_published TINYINT(1) NOT NULL DEFAULT 1,
  published_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_resources_type_published (resource_type_id, is_published, published_at),
  KEY idx_resources_media_type (media_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO resource_types (name, slug, description, icon, sort_order)
VALUES ('Magazines', 'magazines', 'IMM magazines and publications', 'menu_book', 10)
ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description);

INSERT INTO resources (resource_type_id,title,description,thumbnail_url,file_url,media_type,mime_type,view_count,download_count,published_at,created_at)
SELECT rt.id,m.title,m.description,m.cover_image_url,m.file_url,'pdf','application/pdf',m.view_count,m.download_count,m.created_at,m.created_at
FROM magazines m JOIN resource_types rt ON rt.slug='magazines'
WHERE m.file_url IS NOT NULL AND NOT EXISTS (SELECT 1 FROM resources r WHERE r.file_url=m.file_url);

-- Documents and templates.
CREATE TABLE IF NOT EXISTS document_templates (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  document_type ENUM('ticket','receipt','invoice','quote','membership_id','membership_certificate','event_certificate','event_nametag') NOT NULL,
  name VARCHAR(160) NOT NULL,
  page_format VARCHAR(30) NOT NULL DEFAULT 'A4',
  orientation ENUM('P','L') NOT NULL DEFAULT 'P',
  header_image_url VARCHAR(1000) NULL,
  footer_image_url VARCHAR(1000) NULL,
  template_html MEDIUMTEXT NOT NULL,
  custom_css MEDIUMTEXT NULL,
  is_default TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_document_template_type (document_type,is_active,is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS document_template_assignments (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  template_id BIGINT NOT NULL,
  scope_type ENUM('global','event','membership_cycle') NOT NULL DEFAULT 'global',
  scope_id BIGINT NULL,
  document_type VARCHAR(60) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_document_assignment (scope_type,scope_id,document_type),
  KEY idx_document_assignment_template (template_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS membership_cycles (
  id BIGINT NOT NULL 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 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS member_documents (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  membership_subscription_id BIGINT NULL,
  membership_cycle_id BIGINT 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 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_attendee_documents (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT NOT NULL,
  user_id BIGINT 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 COLLATE=utf8mb4_unicode_ci;

-- Payments, streams, notifications and community.
CREATE TABLE IF NOT EXISTS payment_transactions_v3 (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  charge_id VARCHAR(100) NOT NULL,
  payment_type ENUM('membership','event_ticket','marketplace') NOT NULL,
  reference_id BIGINT 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,
  fulfilled_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),
  KEY idx_payment_fulfilled (fulfilled_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_streams_v3 (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT NOT NULL,
  provider ENUM('youtube','facebook','vimeo','hls','web') NOT NULL,
  title VARCHAR(180) NOT NULL DEFAULT 'Live stream',
  playback_url VARCHAR(1500) NOT NULL,
  embed_url VARCHAR(1500) NULL,
  is_live TINYINT(1) NOT NULL DEFAULT 0,
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_event_stream_live (event_id,is_live)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS user_devices (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NOT NULL,
  push_token VARCHAR(512) NOT NULL,
  platform ENUM('ios','android','web') NOT NULL,
  app_version VARCHAR(40) NULL,
  last_seen_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_push_token (push_token),
  KEY idx_user_devices_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_notifications (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  content_type VARCHAR(64) NOT NULL,
  content_id BIGINT NULL,
  title VARCHAR(180) NOT NULL,
  body VARCHAR(400) NOT NULL,
  deep_link VARCHAR(1000) NULL,
  audience ENUM('all','members','event_attendees','individual') NOT NULL DEFAULT 'all',
  audience_id BIGINT NULL,
  status ENUM('draft','queued','sent','failed') NOT NULL DEFAULT 'draft',
  sent_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_app_notifications_status (status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS app_notification_reads (
  notification_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  read_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(notification_id,user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS community_channels (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(400) NULL,
  visibility ENUM('members','event','private') NOT NULL DEFAULT 'members',
  event_id BIGINT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS community_messages (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  channel_id BIGINT NOT NULL,
  user_id BIGINT NULL,
  parent_message_id BIGINT NULL,
  message_text TEXT NULL,
  attachment_url VARCHAR(1000) NULL,
  attachment_type ENUM('image','video','audio','document','link') NULL,
  is_edited TINYINT(1) NOT NULL DEFAULT 0,
  deleted_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_community_messages_feed (channel_id,created_at),
  KEY idx_community_messages_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS community_reactions (
  message_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  reaction VARCHAR(32) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY(message_id,user_id,reaction)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO community_channels(name,description,visibility)
SELECT 'Member Lounge','A welcoming space for members to exchange updates and ideas.','members'
WHERE NOT EXISTS (SELECT 1 FROM community_channels WHERE name='Member Lounge');

-- Voting queue and public result publication state.
CREATE TABLE IF NOT EXISTS vote_submission_queue (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  submission_id CHAR(36) NOT NULL,
  schedule_id BIGINT NOT NULL,
  seat_id BIGINT NOT NULL,
  candidate_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  status ENUM('queued','processing','completed','rejected','failed') NOT NULL DEFAULT 'queued',
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  rejection_reason VARCHAR(255) NULL,
  queued_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed_at DATETIME NULL,
  UNIQUE KEY uq_vote_submission (submission_id),
  UNIQUE KEY uq_queued_user_seat (user_id,seat_id),
  KEY idx_vote_queue_worker (status,id),
  KEY idx_vote_queue_schedule (schedule_id,status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS result_publications (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  schedule_id BIGINT NOT NULL,
  feature_type ENUM('election','nomination','award') NOT NULL,
  is_published TINYINT(1) NOT NULL DEFAULT 0,
  published_by BIGINT NULL,
  published_at DATETIME NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_result_publication (schedule_id,feature_type),
  KEY idx_public_results (is_published,feature_type,schedule_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Daily/session check-ins and distributions.
CREATE TABLE IF NOT EXISTS event_checkin_settings (
  event_id BIGINT NOT NULL PRIMARY KEY,
  max_checkins_per_day INT UNSIGNED NOT NULL DEFAULT 1,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS event_checkin_logs (
  id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  event_id BIGINT NOT NULL,
  ticket_id BIGINT NOT NULL,
  schedule_id BIGINT NULL,
  checked_in_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  created_by BIGINT NULL,
  KEY idx_checkin_event_day (event_id,checked_in_at),
  KEY idx_checkin_ticket_day (ticket_id,checked_in_at),
  KEY idx_checkin_schedule (schedule_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Existing-table alterations, each safe to re-run.
CALL ms_add_column('users','profile_image_url','VARCHAR(1000) NULL AFTER `position`');
CALL ms_add_column('election_candidates','image_url','VARCHAR(500) NULL AFTER `description`');
CALL ms_add_column('payment_transactions_v3','fulfilled_at','DATETIME NULL AFTER `verified_at`');
CALL ms_add_column('merchandise_distribution','schedule_id','BIGINT NULL AFTER `ticket_id`');
CALL ms_add_column('merchandise_distribution','checkin_log_id','BIGINT NULL AFTER `schedule_id`');
CALL ms_add_index('merchandise_distribution','idx_merch_distribution_schedule','`schedule_id`',0);
CALL ms_add_index('merchandise_distribution','idx_merch_distribution_checkin','`checkin_log_id`',0);
CALL ms_add_index('payment_transactions_v3','idx_payment_fulfilled','`fulfilled_at`',0);

ALTER TABLE app_notifications MODIFY content_type VARCHAR(64) NOT NULL;
ALTER TABLE community_messages MODIFY user_id BIGINT NULL;
ALTER TABLE election_candidates MODIFY image_url VARCHAR(500) NULL;

UPDATE event_tickets SET status='verified' WHERE status='bought';
ALTER TABLE event_tickets MODIFY status ENUM('pending','verified','denied') NOT NULL DEFAULT 'pending';

-- Preserve legacy duplicate votes before enforcing one vote per user and seat.
CREATE TABLE IF NOT EXISTS votes_duplicate_archive (
  id BIGINT NOT NULL PRIMARY KEY,
  candidate_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,
  seat_id BIGINT NOT NULL,
  voted_at TIMESTAMP NULL,
  archived_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_archived_vote_user_seat (user_id,seat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT IGNORE INTO votes_duplicate_archive
SELECT v.*,CURRENT_TIMESTAMP FROM votes v
JOIN (SELECT user_id,seat_id,MIN(id) keep_id FROM votes GROUP BY user_id,seat_id HAVING COUNT(*)>1) d
  ON d.user_id=v.user_id AND d.seat_id=v.seat_id AND v.id<>d.keep_id;
DELETE v FROM votes v
JOIN (SELECT user_id,seat_id,MIN(id) keep_id FROM votes GROUP BY user_id,seat_id HAVING COUNT(*)>1) d
  ON d.user_id=v.user_id AND d.seat_id=v.seat_id AND v.id<>d.keep_id;
CALL ms_add_index('votes','user_id_seat_id_unique','`user_id`,`seat_id`',1);
CALL ms_add_index('votes','idx_votes_candidate','`candidate_id`',0);
CALL ms_add_index('votes','idx_votes_seat_cast','`seat_id`,`voted_at`',0);

INSERT INTO payment_gateways(name,config)
SELECT 'PAYCHANGU','{"public_key":"","secret_key":"","webhook_url":"/api/v3/payments/paychangu/webhook.php","webhook_secret":"","api_url":"https://api.paychangu.com","enabled":false}'
WHERE NOT EXISTS (SELECT 1 FROM payment_gateways WHERE UPPER(name)='PAYCHANGU');

-- Retire only confirmed-empty, unreferenced legacy tables. If any gained data
-- after the supplied export, they are kept and listed in the final report.
DELIMITER $$
DROP PROCEDURE IF EXISTS ms_drop_if_empty$$
CREATE PROCEDURE ms_drop_if_empty(IN p_table VARCHAR(64))
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=p_table) THEN
    SET @sql_count=CONCAT('SELECT COUNT(*) INTO @retired_rows FROM `',p_table,'`');
    PREPARE statement FROM @sql_count; EXECUTE statement; DEALLOCATE PREPARE statement;
    IF @retired_rows=0 THEN
      SET @sql_drop=CONCAT('DROP TABLE `',p_table,'`');
      PREPARE statement FROM @sql_drop; EXECUTE statement; DEALLOCATE PREPARE statement;
    END IF;
  END IF;
END$$
DELIMITER ;

CALL ms_drop_if_empty('company_logs');
CALL ms_drop_if_empty('user_logs');
CALL ms_drop_if_empty('event_certificate_designs');
CALL ms_drop_if_empty('id_card_designs');

INSERT INTO schema_migrations(migration_name,notes)
VALUES(@migration_name,'Production schema reconciled; legacy tables dropped only when empty')
ON DUPLICATE KEY UPDATE applied_at=CURRENT_TIMESTAMP,notes=VALUES(notes);

DROP PROCEDURE IF EXISTS ms_drop_if_empty;
DROP PROCEDURE IF EXISTS ms_add_index;
DROP PROCEDURE IF EXISTS ms_add_column;

SELECT migration_name,applied_at,notes FROM schema_migrations
WHERE migration_name=CONVERT(@migration_name USING utf8mb4) COLLATE utf8mb4_unicode_ci;
SELECT table_name,table_rows FROM information_schema.tables
WHERE table_schema=DATABASE() AND table_name IN ('company_logs','user_logs','event_certificate_designs','id_card_designs');