-- Prestige Barbershop Professional v4.33.0
-- FALLBACK database upgrade. Utamakan upgrade_v4_33_0.php.
-- Aman/idempotent dan tidak memilih/membuat database baru.

CREATE TABLE IF NOT EXISTS whatsapp_templates(
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 template_name VARCHAR(150) NOT NULL,
 category VARCHAR(50) NOT NULL DEFAULT 'promo',
 message_body TEXT NOT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_by INT NULL,
 updated_by INT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_wa_template_active(is_active,category)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS whatsapp_broadcasts(
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 template_id BIGINT NULL,
 source_type ENUM('member','pre_member','non_member') NOT NULL,
 campaign_name VARCHAR(180) NOT NULL,
 message_snapshot TEXT NOT NULL,
 total_recipients INT NOT NULL DEFAULT 0,
 opened_count INT NOT NULL DEFAULT 0,
 created_by INT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(template_id) REFERENCES whatsapp_templates(id) ON DELETE SET NULL,
 FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL,
 INDEX idx_wa_broadcast_source_created(source_type,created_at),
 INDEX idx_wa_broadcast_creator(created_by,created_at)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS whatsapp_broadcast_recipients(
 id BIGINT AUTO_INCREMENT PRIMARY KEY,
 broadcast_id BIGINT NOT NULL,
 source_type ENUM('member','pre_member','non_member') NOT NULL,
 source_id BIGINT NOT NULL,
 recipient_name VARCHAR(180) NOT NULL,
 phone VARCHAR(50) NOT NULL,
 normalized_phone VARCHAR(50) NOT NULL,
 message_text TEXT NOT NULL,
 status ENUM('queued','opened','skipped') NOT NULL DEFAULT 'queued',
 opened_at DATETIME NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(broadcast_id) REFERENCES whatsapp_broadcasts(id) ON DELETE CASCADE,
 UNIQUE KEY uq_wa_broadcast_source(broadcast_id,source_type,source_id),
 INDEX idx_wa_recipient_status(broadcast_id,status),
 INDEX idx_wa_recipient_phone(normalized_phone)
) ENGINE=InnoDB;

INSERT INTO whatsapp_templates(template_name,category,message_body,is_active,created_by)
SELECT 'Promo Umum','promo','Halo {nama}, ada promo spesial dari {brand}. Dapatkan penawaran terbaik untuk layanan pilihan Anda. Silakan balas pesan ini untuk informasi dan reservasi.',1,NULL
WHERE NOT EXISTS (SELECT 1 FROM whatsapp_templates WHERE template_name='Promo Umum');

INSERT INTO whatsapp_templates(template_name,category,message_body,is_active,created_by)
SELECT 'Promo Member','promo','Halo {nama}, khusus Member {level} {brand}: nikmati promo spesial kami. Poin Anda saat ini {poin}. Tunjukkan kartu member saat berkunjung.',1,NULL
WHERE NOT EXISTS (SELECT 1 FROM whatsapp_templates WHERE template_name='Promo Member');

INSERT INTO settings(setting_key,setting_value) VALUES('member_card_download_engine','canvas_safe_assets_v3')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);
INSERT INTO settings(setting_key,setting_value) VALUES('schema_release','4.33.0')
ON DUPLICATE KEY UPDATE setting_value=VALUES(setting_value);