CREATE DATABASE IF NOT EXISTS halo_demo_db;
USE halo_demo_db;

CREATE TABLE IF NOT EXISTS demo_requests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  church_name VARCHAR(255) NOT NULL,
  pastor_name VARCHAR(255) NOT NULL,
  phone VARCHAR(50) NOT NULL,
  email VARCHAR(255) DEFAULT NULL,
  congregation_size VARCHAR(100) DEFAULT '100 – 500',
  branch_setup VARCHAR(50) DEFAULT 'single',
  status VARCHAR(50) DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_status (status),
  INDEX idx_created_at (created_at)
);

CREATE TABLE IF NOT EXISTS admin_users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS email_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  demo_request_id INT,
  recipient VARCHAR(255) NOT NULL,
  subject VARCHAR(255) NOT NULL,
  message TEXT,
  status VARCHAR(50) DEFAULT 'queued',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (demo_request_id) REFERENCES demo_requests(id) ON DELETE SET NULL
);

INSERT INTO admin_users (username, password_hash, full_name)
VALUES ('admin', '$2a$10$5F3PuNzhG8OgGBOkDQGUnewE4FjkIo/FhDngE4nCCHjgEPaOtfbt6', 'Halo Admin')
ON DUPLICATE KEY UPDATE username = VALUES(username);

SELECT 'Demo requests table ready.' AS status;
