-- MJ Marketplace MySQL/MariaDB compatible safe schema helpers.
-- Required because some MySQL versions do not support ALTER TABLE ... ADD COLUMN.
DROP PROCEDURE IF EXISTS mj_safe_add_column;
DROP PROCEDURE IF EXISTS mj_safe_add_index;
DELIMITER $$
CREATE PROCEDURE mj_safe_add_column(IN p_table VARCHAR(128), IN p_col VARCHAR(128), IN p_def TEXT)
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table LIMIT 1)
     AND NOT EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND COLUMN_NAME = p_col LIMIT 1) THEN
    SET @mj_sql = CONCAT('ALTER TABLE `', REPLACE(p_table,'`','``'), '` ADD COLUMN ', p_def);
    PREPARE mj_stmt FROM @mj_sql;
    EXECUTE mj_stmt;
    DEALLOCATE PREPARE mj_stmt;
  END IF;
END$$
CREATE PROCEDURE mj_safe_add_index(IN p_table VARCHAR(128), IN p_index VARCHAR(128), IN p_def TEXT)
BEGIN
  IF EXISTS (SELECT 1 FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table LIMIT 1)
     AND NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = p_table AND INDEX_NAME = p_index LIMIT 1) THEN
    SET @mj_sql = CONCAT('ALTER TABLE `', REPLACE(p_table,'`','``'), '` ', p_def);
    PREPARE mj_stmt FROM @mj_sql;
    EXECUTE mj_stmt;
    DEALLOCATE PREPARE mj_stmt;
  END IF;
END$$
DELIMITER ;

-- MJ Marketplace v3.0.4.2.9 Lead Popup + Notification Dedupe + Speed Optimization
-- Safe update SQL for existing websites.

CREATE TABLE IF NOT EXISTS mj_schema_migrations (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  version VARCHAR(60) NOT NULL,
  migration_file VARCHAR(255) NULL,
  status VARCHAR(40) NOT NULL DEFAULT 'completed',
  started_at DATETIME NULL,
  completed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  error_message TEXT NULL,
  UNIQUE KEY uniq_version (version)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS mj_notifications (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT NULL,
  admin_id BIGINT NULL,
  user_type VARCHAR(20) NULL,
  title VARCHAR(255) NOT NULL,
  message TEXT NULL,
  body TEXT NULL,
  type VARCHAR(80) NULL,
  priority VARCHAR(40) NOT NULL DEFAULT 'normal',
  link VARCHAR(500) NULL,
  action_url VARCHAR(500) NULL,
  url VARCHAR(500) NULL,
  notification_key VARCHAR(191) NULL,
  dedupe_hash CHAR(40) NULL,
  source_type VARCHAR(80) NULL,
  source_id BIGINT NULL,
  lead_id BIGINT NULL,
  is_read TINYINT(1) NOT NULL DEFAULT 0,
  is_popup_shown TINYINT(1) NOT NULL DEFAULT 0,
  popup_shown_at DATETIME NULL,
  read_at DATETIME NULL,
  is_archived TINYINT(1) NOT NULL DEFAULT 0,
  archived_at DATETIME NULL,
  meta_json LONGTEXT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  KEY idx_user (user_id), KEY idx_admin (admin_id), KEY idx_read (is_read), KEY idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CALL mj_safe_add_column('mj_notifications', 'notification_key', 'notification_key VARCHAR(191) NULL');
CALL mj_safe_add_column('mj_notifications', 'dedupe_hash', 'dedupe_hash CHAR(40) NULL');
CALL mj_safe_add_column('mj_notifications', 'source_type', 'source_type VARCHAR(80) NULL');
CALL mj_safe_add_column('mj_notifications', 'source_id', 'source_id BIGINT NULL');
CALL mj_safe_add_column('mj_notifications', 'lead_id', 'lead_id BIGINT NULL');
CALL mj_safe_add_column('mj_notifications', 'is_archived', 'is_archived TINYINT(1) NOT NULL DEFAULT 0');
CALL mj_safe_add_column('mj_notifications', 'archived_at', 'archived_at DATETIME NULL');
CALL mj_safe_add_column('mj_notifications', 'is_popup_shown', 'is_popup_shown TINYINT(1) NOT NULL DEFAULT 0');
CALL mj_safe_add_column('mj_notifications', 'popup_shown_at', 'popup_shown_at DATETIME NULL');
CALL mj_safe_add_column('mj_notifications', 'read_at', 'read_at DATETIME NULL');

CALL mj_safe_add_column('seller_leads', 'updated_at', 'updated_at DATETIME NULL');
CALL mj_safe_add_column('seller_leads', 'last_activity_at', 'last_activity_at DATETIME NULL');
CALL mj_safe_add_column('seller_leads', 'followup_at', 'followup_at DATETIME NULL');
CALL mj_safe_add_column('seller_leads', 'is_read', 'is_read TINYINT(1) NOT NULL DEFAULT 0');

CALL mj_safe_add_index('mj_notifications', 'idx_mj429_notify_key', 'ADD INDEX idx_mj429_notify_key (notification_key)');
CALL mj_safe_add_index('mj_notifications', 'idx_mj429_notify_hash', 'ADD INDEX idx_mj429_notify_hash (dedupe_hash)');
CALL mj_safe_add_index('mj_notifications', 'idx_mj429_notify_recipient', 'ADD INDEX idx_mj429_notify_recipient (user_type,user_id,admin_id,is_read,created_at)');
CALL mj_safe_add_index('mj_notifications', 'idx_mj429_notify_source', 'ADD INDEX idx_mj429_notify_source (source_type,source_id,lead_id)');
CALL mj_safe_add_index('seller_leads', 'idx_mj429_leads_seller_status_date', 'ADD INDEX idx_mj429_leads_seller_status_date (seller_user_id,status,created_at)');
CALL mj_safe_add_index('seller_leads', 'idx_mj429_leads_source', 'ADD INDEX idx_mj429_leads_source (source_type,source_id)');
CALL mj_safe_add_index('seller_lead_activities', 'idx_mj429_lead_activities', 'ADD INDEX idx_mj429_lead_activities (lead_id,created_at)');

-- Generate keys for older notifications so frontend can de-duplicate them.
UPDATE mj_notifications
SET notification_key = CONCAT(COALESCE(user_type, IF(admin_id IS NOT NULL,'admin','user')), ':', COALESCE(user_id, admin_id, 0), ':', COALESCE(type,'info'), ':', SHA1(CONCAT(COALESCE(title,''),'|',COALESCE(message,body,''),'|',COALESCE(action_url,link,url,''))))
WHERE notification_key IS NULL OR notification_key='';
UPDATE mj_notifications SET dedupe_hash = SHA1(notification_key) WHERE dedupe_hash IS NULL OR dedupe_hash='';

-- Archive older duplicate notifications. Keep latest row visible.
UPDATE mj_notifications n
INNER JOIN mj_notifications k ON k.notification_key=n.notification_key AND k.id>n.id
SET n.is_archived=1,n.is_read=1,n.archived_at=NOW()
WHERE n.notification_key IS NOT NULL AND n.notification_key<>'' AND n.is_archived=0;

INSERT INTO mj_schema_migrations(version,migration_file,status,started_at,completed_at)
VALUES('3.0.4.2.9','update_to_v3.0.4.2.9.sql','completed',NOW(),NOW())
ON DUPLICATE KEY UPDATE status='completed',completed_at=NOW(),error_message=NULL;
