-- =============================================================================
-- JobVumi Email Recommendations — Full DB Setup
-- Run against your job portal database (e.g. job_portal1234)
-- Safe to re-run: uses CREATE TABLE IF NOT EXISTS and INSERT ... ON DUPLICATE KEY
-- =============================================================================

-- -----------------------------------------------------------------------------
-- PART 1: NEW LOG TABLES (create if missing)
-- -----------------------------------------------------------------------------

CREATE TABLE IF NOT EXISTS seeker_email_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  seeker_id INT NOT NULL COMMENT 'users.id — seeker who received the email',
  job_ids JSON NOT NULL COMMENT 'JSON array of job_posts.id values sent in email',
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status ENUM('sent', 'failed', 'skipped') DEFAULT 'sent',
  INDEX idx_seeker_sent (seeker_id, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS recruiter_candidate_email_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  job_id INT NOT NULL COMMENT 'job_posts.id',
  recruiter_id INT NOT NULL COMMENT 'users.id — recruiter who received the email',
  candidate_ids JSON NOT NULL COMMENT 'JSON array of users.id (seeker) sent in email',
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  status ENUM('sent', 'failed', 'skipped') DEFAULT 'sent',
  INDEX idx_job_recruiter_sent (job_id, recruiter_id, sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- PART 2: CONFIG_SETTING ROWS (master controls)
-- Requires existing table: config_setting (setting_key UNIQUE)
-- Format: ON DUPLICATE KEY only updates description on re-run;
--         use the UPDATE block in Part 3 to change values.
-- -----------------------------------------------------------------------------

INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('seeker_email_dedup_days', '7', 'Days before a seeker receives the same job recommendation again. Use 0 to disable dedup.')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('recruiter_email_dedup_days', '7', 'Days before a recruiter receives the same candidate match again. Use 0 to disable dedup.')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('seeker_email_recommendations_enabled', '1', 'Master switch: 1=enabled, 0=disabled for seeker daily job emails')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('recruiter_email_recommendations_enabled', '1', 'Master switch: 1=enabled, 0=disabled for recruiter daily candidate emails')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('seeker_email_max_jobs', '5', 'Maximum jobs included in each seeker recommendation email')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('recruiter_email_max_jobs', '5', 'Maximum job posts included in each recruiter recommendation email')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('recruiter_email_max_candidates_per_job', '4', 'Maximum matched candidates shown per job in recruiter email')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- Cron times: 24-hour HH:MM in IST (Asia/Kolkata)
-- Default: seeker 10:30 AM, recruiter 11:00 AM
INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('seeker_email_cron_time', '10:30', 'Daily seeker email send time IST — 24-hour HH:MM e.g. 10:30 or 23:55 for 11:55 PM')
ON DUPLICATE KEY UPDATE description = VALUES(description);

INSERT INTO config_setting (setting_key, setting_value, description) VALUES
  ('recruiter_email_cron_time', '11:00', 'Daily recruiter email send time IST — 24-hour HH:MM e.g. 11:00 or 23:55 for 11:55 PM')
ON DUPLICATE KEY UPDATE description = VALUES(description);

-- -----------------------------------------------------------------------------
-- PART 3: EXAMPLE — Set send time to 11:55 PM IST (both engines)
-- 11:55 PM = 23:55 in 24-hour format (NOT 11:55 — that is 11:55 AM)
-- -----------------------------------------------------------------------------

-- UPDATE config_setting SET setting_value = '23:55' WHERE setting_key = 'seeker_email_cron_time';
-- UPDATE config_setting SET setting_value = '23:55' WHERE setting_key = 'recruiter_email_cron_time';

-- -----------------------------------------------------------------------------
-- PART 4: VERIFY
-- -----------------------------------------------------------------------------

SELECT setting_key, setting_value, description
FROM config_setting
WHERE setting_key IN (
  'seeker_email_dedup_days',
  'recruiter_email_dedup_days',
  'seeker_email_recommendations_enabled',
  'recruiter_email_recommendations_enabled',
  'seeker_email_max_jobs',
  'recruiter_email_max_jobs',
  'recruiter_email_max_candidates_per_job',
  'seeker_email_cron_time',
  'recruiter_email_cron_time'
)
ORDER BY setting_key;

SHOW TABLES LIKE '%email_logs%';