CREATE TABLE IF NOT EXISTS app_settings (
  setting_key VARCHAR(120) PRIMARY KEY,
  setting_value TEXT NULL,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS message_templates (
  event_key VARCHAR(80) PRIMARY KEY,
  name VARCHAR(190) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  enabled BOOLEAN NOT NULL DEFAULT TRUE,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS email_deliveries (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NULL,
  event_key VARCHAR(80) NOT NULL,
  recipient VARCHAR(190) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  body TEXT NOT NULL,
  status ENUM('sent', 'failed', 'not_configured') NOT NULL,
  error_message TEXT NULL,
  sent_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_email_delivery_order
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_email_delivery_order (order_id, created_at),
  INDEX idx_email_delivery_status (status, created_at)
);

SET @add_occurrence_date = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'orders'
        AND column_name = 'recurring_occurrence_date'
    ),
    'SELECT 1',
    'ALTER TABLE orders ADD COLUMN recurring_occurrence_date DATE NULL AFTER recurring_template_id'
  )
);
PREPARE add_occurrence_date_stmt FROM @add_occurrence_date;
EXECUTE add_occurrence_date_stmt;
DEALLOCATE PREPARE add_occurrence_date_stmt;

UPDATE orders
SET recurring_occurrence_date = required_date
WHERE source = 'recurring'
  AND recurring_template_id IS NOT NULL
  AND recurring_occurrence_date IS NULL;

SET @add_occurrence_unique = (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE()
        AND table_name = 'orders'
        AND index_name = 'uq_recurring_occurrence'
    ),
    'SELECT 1',
    'ALTER TABLE orders ADD UNIQUE KEY uq_recurring_occurrence (recurring_template_id, recurring_occurrence_date)'
  )
);
PREPARE add_occurrence_unique_stmt FROM @add_occurrence_unique;
EXECUTE add_occurrence_unique_stmt;
DEALLOCATE PREPARE add_occurrence_unique_stmt;

INSERT IGNORE INTO message_templates (event_key, name, subject, body) VALUES
  (
    'order_confirmed',
    'Order confirmed',
    'Order {{order_number}} confirmed',
    'Hi {{customer_name}},\n\nYour coffee order {{order_number}} has been confirmed. We will let you know when production is complete.\n\nThanks,\n{{company_name}}'
  ),
  (
    'production_finished',
    'Production finished',
    'Your coffee order {{order_number}} is ready',
    'Hi {{customer_name}},\n\nProduction for order {{order_number}} is finished and your coffee is ready for dispatch or collection.\n\nThanks,\n{{company_name}}'
  ),
  (
    'order_shipped',
    'Order shipped',
    'Order {{order_number}} has shipped',
    'Hi {{customer_name}},\n\nYour coffee order {{order_number}} has been shipped.\n\nThanks,\n{{company_name}}'
  ),
  (
    'invoice_issued',
    'Invoice issued',
    'Invoice {{invoice_number}} from {{company_name}}',
    'Hi {{customer_name}},\n\nInvoice {{invoice_number}} for order {{order_number}} has been issued and is due on {{due_date}}.\n\nThanks,\n{{company_name}}'
  );