USE TESVISCOVENTURES;

-- Payment transactions (records of money in/out tied to orders)
CREATE TABLE IF NOT EXISTS payments (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT UNSIGNED NOT NULL,
  order_number VARCHAR(32) NOT NULL DEFAULT '',
  gateway VARCHAR(40) NOT NULL DEFAULT '',
  gateway_event_id VARCHAR(255) DEFAULT NULL,
  gateway_reference VARCHAR(255) DEFAULT NULL,
  amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'GBP',
  status VARCHAR(40) NOT NULL DEFAULT 'succeeded',
  event_type VARCHAR(80) NOT NULL DEFAULT '',
  raw_payload MEDIUMTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_pay_order (order_id),
  INDEX idx_pay_gateway (gateway, created_at),
  UNIQUE KEY uq_pay_gateway_event (gateway, gateway_event_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Webhook events received from payment gateways
CREATE TABLE IF NOT EXISTS payment_webhook_events (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  gateway VARCHAR(40) NOT NULL,
  event_type VARCHAR(100) NOT NULL DEFAULT '',
  order_reference VARCHAR(120) NOT NULL DEFAULT '',
  status VARCHAR(40) NOT NULL DEFAULT 'received',
  response_sent VARCHAR(255) NOT NULL DEFAULT '',
  raw_payload MEDIUMTEXT NULL,
  received_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_wh_gateway (gateway, received_at),
  INDEX idx_wh_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Customer notes (internal staff notes per customer)
CREATE TABLE IF NOT EXISTS customer_notes (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT UNSIGNED NOT NULL,
  admin_user_id BIGINT UNSIGNED NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_cn_user (user_id, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CSV export queue
CREATE TABLE IF NOT EXISTS export_jobs (
  id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  export_type VARCHAR(60) NOT NULL,
  params_json MEDIUMTEXT NULL,
  status ENUM('queued','processing','completed','failed') NOT NULL DEFAULT 'queued',
  file_path VARCHAR(255) NULL,
  row_count INT UNSIGNED NOT NULL DEFAULT 0,
  error_message TEXT NULL,
  admin_user_id BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  completed_at DATETIME NULL,
  INDEX idx_ex_status (status, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;