USE TESVISCOVENTURES;

CREATE TABLE IF NOT EXISTS email_templates (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(60) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  subject VARCHAR(200) NOT NULL DEFAULT '',
  body_html LONGTEXT NOT NULL,
  variables TEXT NULL,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO email_templates (code, name, subject, body_html, variables) VALUES
('welcome', 'Welcome email', 'Welcome to Elroi Foods, {first_name}!', '<p>Hi {first_name},</p><p>Welcome to Elroi Foods. Thanks for joining us!</p>', 'first_name,email'),
('order_confirmation', 'Order confirmation', 'Your order #{order_number} is confirmed', '<p>Hi {first_name},</p><p>Thanks for your order #{order_number}. Total: {grand_total}</p>', 'first_name,order_number,grand_total'),
('order_shipped', 'Order shipped', 'Your order #{order_number} is on the way', '<p>Hi {first_name},</p><p>Your order #{order_number} has been dispatched.</p>', 'first_name,order_number'),
('password_reset', 'Password reset', 'Reset your Elroi Foods password', '<p>Hi {first_name},</p><p>Click <a href="{reset_url}">here</a> to reset your password.</p>', 'first_name,reset_url'),
('cod_collected', 'COD collected', 'Payment received for order #{order_number}', '<p>Hi {first_name},</p><p>We have received payment for order #{order_number}. Thank you!</p>', 'first_name,order_number'),
('refund_issued', 'Refund issued', 'Refund processed for order #{order_number}', '<p>Hi {first_name},</p><p>Your refund of {refund_amount} for order #{order_number} has been processed.</p>', 'first_name,order_number,refund_amount');