-- migrate:up

ALTER TABLE bot_message_templates
  ADD COLUMN description VARCHAR(500) NULL AFTER locale,
  ADD COLUMN disable_web_page_preview TINYINT(1) NOT NULL DEFAULT 1 AFTER button_config,
  ADD CONSTRAINT chk_bot_templates_placeholders CHECK (
    JSON_TYPE(allowed_placeholders) = 'ARRAY'
  );

CREATE TABLE bot_message_template_versions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  template_id BIGINT UNSIGNED NOT NULL,
  version INT UNSIGNED NOT NULL,
  description VARCHAR(500) NULL,
  body_html TEXT NOT NULL,
  button_config JSON NULL,
  disable_web_page_preview TINYINT(1) NOT NULL,
  allowed_placeholders JSON NOT NULL,
  status ENUM('draft', 'active', 'archived') NOT NULL,
  changed_by_admin_id BIGINT UNSIGNED NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_bot_template_versions_version (template_id, version),
  KEY idx_bot_template_versions_time (template_id, created_at, id),
  CONSTRAINT fk_bot_template_versions_template
    FOREIGN KEY (template_id) REFERENCES bot_message_templates (id) ON DELETE RESTRICT,
  CONSTRAINT fk_bot_template_versions_admin
    FOREIGN KEY (changed_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  CONSTRAINT chk_bot_template_versions_version CHECK (version > 0),
  CONSTRAINT chk_bot_template_versions_placeholders CHECK (
    JSON_TYPE(allowed_placeholders) = 'ARRAY'
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO bot_message_template_versions (
  template_id, version, description, body_html, button_config,
  disable_web_page_preview, allowed_placeholders, status, changed_by_admin_id,
  created_at
)
SELECT id, version, description, body_html, button_config,
       disable_web_page_preview, allowed_placeholders, status,
       updated_by_admin_id, created_at
FROM bot_message_templates;

ALTER TABLE reminder_schedules
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD COLUMN last_run_at DATETIME(6) NULL AFTER next_run_at,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD CONSTRAINT fk_reminder_schedules_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_reminder_schedules_version CHECK (version > 0),
  ADD CONSTRAINT chk_reminder_schedules_days CHECK (JSON_TYPE(active_days) = 'ARRAY'),
  ADD CONSTRAINT chk_reminder_schedules_rules CHECK (JSON_TYPE(target_rules) = 'OBJECT'),
  ADD CONSTRAINT chk_reminder_schedules_next_run CHECK (
    status <> 'active' OR next_run_at IS NOT NULL
  );

ALTER TABLE broadcasts
  ADD COLUMN version BIGINT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
  ADD COLUMN audience_frozen_at DATETIME(6) NULL AFTER scheduled_at,
  ADD COLUMN started_at DATETIME(6) NULL AFTER audience_frozen_at,
  ADD COLUMN completed_at DATETIME(6) NULL AFTER started_at,
  ADD COLUMN recipient_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER completed_at,
  ADD COLUMN sent_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER recipient_count,
  ADD COLUMN failed_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER sent_count,
  ADD COLUMN skipped_count BIGINT UNSIGNED NOT NULL DEFAULT 0 AFTER failed_count,
  ADD COLUMN cancellation_reason VARCHAR(500) NULL AFTER skipped_count,
  ADD COLUMN updated_by_admin_id BIGINT UNSIGNED NULL AFTER created_by_admin_id,
  ADD CONSTRAINT fk_broadcasts_updater
    FOREIGN KEY (updated_by_admin_id) REFERENCES admins (id) ON DELETE SET NULL,
  ADD CONSTRAINT chk_broadcasts_version CHECK (version > 0),
  ADD CONSTRAINT chk_broadcasts_target_rules CHECK (JSON_TYPE(target_rules) = 'OBJECT'),
  ADD CONSTRAINT chk_broadcasts_counts CHECK (
    sent_count + failed_count + skipped_count <= recipient_count
  ),
  ADD CONSTRAINT chk_broadcasts_approval_state CHECK (
    (status = 'draft' AND approved_by_admin_id IS NULL AND approved_at IS NULL) OR
    (status IN ('approved', 'queued', 'sending', 'completed', 'failed') AND
      approved_by_admin_id IS NOT NULL AND approved_at IS NOT NULL) OR
    (status = 'cancelled' AND
      ((approved_by_admin_id IS NULL AND approved_at IS NULL) OR
       (approved_by_admin_id IS NOT NULL AND approved_at IS NOT NULL)))
  );

ALTER TABLE reminder_deliveries
  ADD COLUMN outbox_message_id BIGINT UNSIGNED NULL AFTER delivery_key,
  ADD COLUMN template_version INT UNSIGNED NOT NULL DEFAULT 1 AFTER outbox_message_id,
  ADD COLUMN payload_hash BINARY(32) NULL AFTER template_version,
  ADD UNIQUE KEY uq_reminder_deliveries_outbox (outbox_message_id),
  ADD CONSTRAINT fk_reminder_deliveries_outbox
    FOREIGN KEY (outbox_message_id) REFERENCES outbox_messages (id) ON DELETE RESTRICT,
  ADD CONSTRAINT chk_reminder_deliveries_template_version CHECK (template_version > 0);

ALTER TABLE broadcast_deliveries
  ADD COLUMN outbox_message_id BIGINT UNSIGNED NULL AFTER user_id,
  ADD COLUMN template_version INT UNSIGNED NOT NULL DEFAULT 1 AFTER outbox_message_id,
  ADD COLUMN payload_hash BINARY(32) NULL AFTER template_version,
  ADD UNIQUE KEY uq_broadcast_deliveries_outbox (outbox_message_id),
  ADD CONSTRAINT fk_broadcast_deliveries_outbox
    FOREIGN KEY (outbox_message_id) REFERENCES outbox_messages (id) ON DELETE RESTRICT,
  ADD CONSTRAINT chk_broadcast_deliveries_template_version CHECK (template_version > 0);

ALTER TABLE outbox_messages
  ADD COLUMN last_attempt_at DATETIME(6) NULL AFTER locked_until,
  ADD CONSTRAINT chk_outbox_messages_payload CHECK (JSON_TYPE(payload) = 'OBJECT'),
  ADD CONSTRAINT chk_outbox_messages_lease CHECK (
    (status = 'processing' AND locked_by IS NOT NULL AND locked_until IS NOT NULL) OR
    (status <> 'processing')
  );

CREATE TABLE message_delivery_attempts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  outbox_message_id BIGINT UNSIGNED NOT NULL,
  attempt_number SMALLINT UNSIGNED NOT NULL,
  provider VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  outcome ENUM('sent', 'retry', 'permanent_failure') NOT NULL,
  provider_status SMALLINT UNSIGNED NULL,
  retry_after_seconds INT UNSIGNED NULL,
  error_code VARCHAR(120) CHARACTER SET ascii COLLATE ascii_bin NULL,
  provider_message_id BIGINT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (id),
  UNIQUE KEY uq_message_attempts_number (outbox_message_id, attempt_number),
  KEY idx_message_attempts_outcome_time (outcome, created_at, id),
  CONSTRAINT fk_message_attempts_outbox
    FOREIGN KEY (outbox_message_id) REFERENCES outbox_messages (id) ON DELETE RESTRICT,
  CONSTRAINT chk_message_attempts_number CHECK (attempt_number > 0),
  CONSTRAINT chk_message_attempts_retry CHECK (
    retry_after_seconds IS NULL OR outcome = 'retry'
  )
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT IGNORE INTO bot_message_templates (
  template_key, locale, description, body_html, button_config,
  disable_web_page_preview, allowed_placeholders, status, version
) VALUES (
  'daily_reminder', 'bn', 'Scheduled daily task reminder.',
  '<b>{{first_name}}</b>, আজকের taskগুলো আপনার জন্য প্রস্তুত।',
  '{"type":"mini_app","label":"Task দেখুন","url_placeholder":"app_url"}',
  1, '["first_name","app_url","task_count"]', 'draft', 1
);

INSERT IGNORE INTO bot_message_template_versions (
  template_id, version, description, body_html, button_config,
  disable_web_page_preview, allowed_placeholders, status, changed_by_admin_id,
  created_at
)
SELECT id, version, description, body_html, button_config,
       disable_web_page_preview, allowed_placeholders, status,
       updated_by_admin_id, created_at
FROM bot_message_templates
WHERE template_key = 'daily_reminder' AND locale = 'bn';

INSERT IGNORE INTO permissions (code, permission_group, description) VALUES
  ('notifications.view', 'notifications', 'View notification status.'),
  ('notifications.send', 'notifications', 'Send approved notifications.'),
  ('reminders.manage', 'notifications', 'Manage reminder schedules.'),
  ('broadcasts.create', 'notifications', 'Create broadcast drafts.'),
  ('broadcasts.approve', 'notifications', 'Approve broadcasts created by another admin.'),
  ('bot_templates.manage', 'notifications', 'Manage bot templates.'),
  ('bot_templates.view', 'notifications', 'View bot templates and previews.'),
  ('reminders.view', 'notifications', 'View reminder schedules and delivery reports.'),
  ('broadcasts.view', 'notifications', 'View broadcast drafts and delivery reports.'),
  ('broadcasts.cancel', 'notifications', 'Cancel an approved or queued broadcast.'),
  ('system_jobs.view', 'system', 'View redacted delivery and job failure evidence.');

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT role.id, permission.id
FROM admin_roles AS role
CROSS JOIN permissions AS permission
WHERE role.code = 'super_admin';

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT role.id, permission.id
FROM admin_roles AS role
JOIN permissions AS permission ON permission.code IN (
  'bot_templates.view', 'reminders.view', 'broadcasts.view', 'broadcasts.cancel'
)
WHERE role.code = 'content_manager';

INSERT IGNORE INTO admin_role_permissions (role_id, permission_id)
SELECT role.id, permission.id
FROM admin_roles AS role
JOIN permissions AS permission ON permission.code IN (
  'bot_templates.view', 'reminders.view', 'broadcasts.view',
  'system_jobs.view'
)
WHERE role.code IN ('operations_manager', 'auditor');

CREATE TRIGGER trg_bot_template_versions_no_update
BEFORE UPDATE ON bot_message_template_versions
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Bot template version evidence is immutable';

CREATE TRIGGER trg_bot_template_versions_no_delete
BEFORE DELETE ON bot_message_template_versions
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Bot template version evidence cannot be deleted';

CREATE TRIGGER trg_message_attempts_no_update
BEFORE UPDATE ON message_delivery_attempts
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Message delivery attempt evidence is immutable';

CREATE TRIGGER trg_message_attempts_no_delete
BEFORE DELETE ON message_delivery_attempts
FOR EACH ROW
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Message delivery attempt evidence cannot be deleted';

-- migrate:down

DROP TRIGGER IF EXISTS trg_message_attempts_no_delete;
DROP TRIGGER IF EXISTS trg_message_attempts_no_update;
DROP TRIGGER IF EXISTS trg_bot_template_versions_no_delete;
DROP TRIGGER IF EXISTS trg_bot_template_versions_no_update;

DELETE FROM permissions WHERE code IN (
  'bot_templates.view', 'reminders.view', 'broadcasts.view',
  'broadcasts.cancel', 'system_jobs.view'
);

DROP TABLE IF EXISTS message_delivery_attempts;

ALTER TABLE outbox_messages
  DROP CHECK chk_outbox_messages_lease,
  DROP CHECK chk_outbox_messages_payload,
  DROP COLUMN last_attempt_at;

ALTER TABLE broadcast_deliveries
  DROP CHECK chk_broadcast_deliveries_template_version,
  DROP FOREIGN KEY fk_broadcast_deliveries_outbox,
  DROP INDEX uq_broadcast_deliveries_outbox,
  DROP COLUMN payload_hash,
  DROP COLUMN template_version,
  DROP COLUMN outbox_message_id;

ALTER TABLE reminder_deliveries
  DROP CHECK chk_reminder_deliveries_template_version,
  DROP FOREIGN KEY fk_reminder_deliveries_outbox,
  DROP INDEX uq_reminder_deliveries_outbox,
  DROP COLUMN payload_hash,
  DROP COLUMN template_version,
  DROP COLUMN outbox_message_id;

ALTER TABLE broadcasts
  DROP CHECK chk_broadcasts_approval_state,
  DROP CHECK chk_broadcasts_counts,
  DROP CHECK chk_broadcasts_target_rules,
  DROP CHECK chk_broadcasts_version,
  DROP FOREIGN KEY fk_broadcasts_updater,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN cancellation_reason,
  DROP COLUMN skipped_count,
  DROP COLUMN failed_count,
  DROP COLUMN sent_count,
  DROP COLUMN recipient_count,
  DROP COLUMN completed_at,
  DROP COLUMN started_at,
  DROP COLUMN audience_frozen_at,
  DROP COLUMN version;

ALTER TABLE reminder_schedules
  DROP CHECK chk_reminder_schedules_next_run,
  DROP CHECK chk_reminder_schedules_rules,
  DROP CHECK chk_reminder_schedules_days,
  DROP CHECK chk_reminder_schedules_version,
  DROP FOREIGN KEY fk_reminder_schedules_updater,
  DROP COLUMN updated_by_admin_id,
  DROP COLUMN last_run_at,
  DROP COLUMN version;

DROP TABLE IF EXISTS bot_message_template_versions;

ALTER TABLE bot_message_templates
  DROP CHECK chk_bot_templates_placeholders,
  DROP COLUMN disable_web_page_preview,
  DROP COLUMN description;