-- Migration 014: Email, Calendar, Payments, Messaging scaffolds

-- ============================================================
-- EMAIL CONFIG (Gmail OAuth, Protonmail Bridge, SMTP)
-- ============================================================
CREATE TABLE IF NOT EXISTS email_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider ENUM('gmail','protonmail','smtp') NOT NULL DEFAULT 'smtp',
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  smtp_host VARCHAR(255) DEFAULT NULL,
  smtp_port INT DEFAULT 587,
  smtp_user VARCHAR(255) DEFAULT NULL,
  smtp_pass VARCHAR(255) DEFAULT NULL,
  smtp_encryption ENUM('tls','ssl','none') DEFAULT 'tls',
  imap_host VARCHAR(255) DEFAULT NULL,
  imap_port INT DEFAULT 993,
  gmail_client_id VARCHAR(255) DEFAULT NULL,
  gmail_client_secret VARCHAR(255) DEFAULT NULL,
  gmail_access_token TEXT DEFAULT NULL,
  gmail_refresh_token TEXT DEFAULT NULL,
  gmail_token_expiry DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- CALENDAR INTEGRATION (Google Calendar, Apple iCal)
-- ============================================================
CREATE TABLE IF NOT EXISTS calendar_config (
  id INT AUTO_INCREMENT PRIMARY KEY,
  provider ENUM('google','ical','both') NOT NULL DEFAULT 'ical',
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  google_client_id VARCHAR(255) DEFAULT NULL,
  google_client_secret VARCHAR(255) DEFAULT NULL,
  google_access_token TEXT DEFAULT NULL,
  google_refresh_token TEXT DEFAULT NULL,
  google_calendar_id VARCHAR(255) DEFAULT 'primary',
  ical_secret VARCHAR(64) DEFAULT NULL,
  sync_direction ENUM('mvp_to_odoo','odoo_to_mvp','bidirectional') DEFAULT 'mvp_to_odoo',
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- PAYMENT PROCESSING (QBO + Stripe)
-- ============================================================
CREATE TABLE IF NOT EXISTS payment_processors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  processor ENUM('qbo','stripe') NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 0,
  -- QBO fields
  qbo_access_token TEXT DEFAULT NULL,
  qbo_refresh_token TEXT DEFAULT NULL,
  qbo_token_expiry DATETIME DEFAULT NULL,
  -- Stripe fields
  stripe_publishable_key VARCHAR(255) DEFAULT NULL,
  stripe_secret_key VARCHAR(255) DEFAULT NULL,
  stripe_webhook_secret VARCHAR(255) DEFAULT NULL,
  stripe_account_id VARCHAR(255) DEFAULT NULL,
  -- Common
  test_mode TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uk_processor (processor)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- MESSAGING (Customer App + Email)
-- ============================================================
CREATE TABLE IF NOT EXISTS messages (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  appointment_id INT DEFAULT NULL,
  direction ENUM('inbound','outbound') NOT NULL,
  channel ENUM('app','email','sms') NOT NULL DEFAULT 'app',
  subject VARCHAR(255) DEFAULT NULL,
  body TEXT NOT NULL,
  status ENUM('sent','delivered','read','failed') NOT NULL DEFAULT 'sent',
  sent_by INT DEFAULT NULL COMMENT 'user_id of sender (null = customer)',
  read_at DATETIME DEFAULT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_customer (customer_id),
  KEY idx_appointment (appointment_id),
  KEY idx_created (created_at),
  CONSTRAINT fk_msg_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;