-- ============================================================
-- Asterixx Email Notification System — Server Migration SQL
-- Run this on the LIVE database: mediaiqn_ASTERIXXTOYS
-- ============================================================

-- 1. Create email_templates table
CREATE TABLE IF NOT EXISTS email_templates (
  id INT AUTO_INCREMENT PRIMARY KEY,
  	emplate_key VARCHAR(60) NOT NULL UNIQUE,
  label VARCHAR(100) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  sender_name VARCHAR(100) NOT NULL DEFAULT 'Asterixx Books & Toys',
  sender_email VARCHAR(150) NOT NULL DEFAULT 'hello@asterixxbooksandtoys.com',
  html_body LONGTEXT NOT NULL,
  is_enabled TINYINT(1) NOT NULL DEFAULT 1,
  
ecipient_type ENUM('customer','admin') NOT NULL DEFAULT 'customer',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 2. Create email_logs table
CREATE TABLE IF NOT EXISTS email_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  	emplate_key VARCHAR(60),
  
ecipient_email VARCHAR(255),
  subject VARCHAR(255),
  status ENUM('sent','failed') NOT NULL,
  
esend_id VARCHAR(100),
  error_message TEXT,
  sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 3. Add new settings keys
INSERT INTO settings (setting_key, setting_value) VALUES ('resend_api_key', '') ON DUPLICATE KEY UPDATE setting_key=setting_key;
INSERT INTO settings (setting_key, setting_value) VALUES ('admin_email', 'admin@asterixxbooksandtoys.com') ON DUPLICATE KEY UPDATE setting_key=setting_key;
INSERT INTO settings (setting_key, setting_value) VALUES ('admin_notification_email', 'admin@asterixxbooksandtoys.com') ON DUPLICATE KEY UPDATE setting_key=setting_key;
INSERT INTO settings (setting_key, setting_value) VALUES ('site_url', 'https://asterixxbooksandtoys.com') ON DUPLICATE KEY UPDATE setting_key=setting_key;
INSERT INTO settings (setting_key, setting_value) VALUES ('store_email', 'hello@asterixxbooksandtoys.com') ON DUPLICATE KEY UPDATE setting_key=setting_key;

-- ============================================================
-- IMPORTANT: After uploading files to the server,
-- run the template seeder via browser or CLI:
--   https://asterixxbooksandtoys.com/config/seed_email_templates.php
-- Then DELETE that file from the server immediately after.
-- ============================================================