-- ResellBD Step 22: marketing foundation. Apply only to the NEW ResellBD database after migrations 001-013.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS marketing_campaigns (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(160) NOT NULL,
 campaign_type ENUM('referral','email','cart_reminder','general') NOT NULL DEFAULT 'general',
 subject VARCHAR(190) NULL, message TEXT NOT NULL,
 status ENUM('draft','active','paused','ended') NOT NULL DEFAULT 'draft',
 starts_at DATETIME NULL, ends_at DATETIME NULL, created_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_campaign_status_dates(status,starts_at,ends_at),
 CONSTRAINT fk_marketing_campaign_creator FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS referral_codes (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL,
 code VARCHAR(32) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_referral_code(code), UNIQUE KEY uq_referral_user(user_id),
 CONSTRAINT fk_referral_code_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS referral_attributions (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, referral_code_id BIGINT UNSIGNED NOT NULL,
 referred_user_id BIGINT UNSIGNED NULL, referred_email VARCHAR(190) NULL, referred_phone VARCHAR(32) NULL,
 status ENUM('pending','qualified','rejected') NOT NULL DEFAULT 'pending', qualifying_order_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_referral_referred_user(referred_user_id), KEY idx_referral_status(status,created_at),
 CONSTRAINT fk_attribution_code FOREIGN KEY(referral_code_id) REFERENCES referral_codes(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_attribution_user FOREIGN KEY(referred_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT fk_attribution_order FOREIGN KEY(qualifying_order_id) REFERENCES orders(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS marketing_consents (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NULL, email VARCHAR(190) NULL, phone VARCHAR(32) NULL,
 channel ENUM('email','sms','whatsapp','in_app') NOT NULL, is_opted_in TINYINT(1) NOT NULL DEFAULT 0,
 consent_source VARCHAR(100) NOT NULL DEFAULT 'account_settings', consented_at DATETIME NULL, revoked_at DATETIME NULL,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_marketing_consent_user_channel(user_id,channel), KEY idx_marketing_consent_lookup(email,channel,is_opted_in),
 CONSTRAINT fk_marketing_consent_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE IF NOT EXISTS abandoned_cart_reminders (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, cart_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL,
 channel ENUM('email','sms','whatsapp','in_app') NOT NULL DEFAULT 'in_app',
 status ENUM('queued','skipped_no_consent','sent','failed','cancelled') NOT NULL DEFAULT 'queued',
 scheduled_at DATETIME NOT NULL, sent_at DATETIME NULL, provider_reference VARCHAR(190) NULL,
 failure_note VARCHAR(500) NULL, idempotency_key CHAR(64) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_cart_reminder_idempotency(idempotency_key), KEY idx_reminders_status_schedule(status,scheduled_at),
 CONSTRAINT fk_cart_reminder_cart FOREIGN KEY(cart_id) REFERENCES carts(id) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT fk_cart_reminder_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;