-- =============================================
-- ONVITE DIGITAL - Super Admin Schema
-- =============================================

CREATE DATABASE IF NOT EXISTS onvite_superadmin DEFAULT CHARACTER SET utf8mb4;
USE onvite_superadmin;

-- Super admin users
CREATE TABLE IF NOT EXISTS super_admins (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  username    VARCHAR(100) NOT NULL UNIQUE,
  password    VARCHAR(255) NOT NULL,  -- bcrypt hash
  created_at  DATETIME DEFAULT NOW()
);

-- One row per wedding project
CREATE TABLE IF NOT EXISTS projects (
  id                  INT AUTO_INCREMENT PRIMARY KEY,
  directory_name      VARCHAR(100) NOT NULL UNIQUE,   -- e.g. GroomBride
  groom_name          VARCHAR(100) NOT NULL,
  bride_name          VARCHAR(100) NOT NULL,
  wedding_datetime    DATETIME NOT NULL,              -- for countdown
  quote               TEXT,
  quote_reference     VARCHAR(255),
  groom_family        VARCHAR(255),
  bride_family        VARCHAR(255),
  church_name         VARCHAR(255),
  church_location_url TEXT,
  venue_name          VARCHAR(255),
  venue_location_url  TEXT,
  gift_method         ENUM('whish','omt') DEFAULT 'whish',
  gift_number         VARCHAR(100),
  rsvp_deadline       DATE,
  groom_phone         VARCHAR(50),
  bride_phone         VARCHAR(50),
  music_file          VARCHAR(255),
  last_image_file     VARCHAR(255),
  thumbnail_file      VARCHAR(255),
  og_url              TEXT,
  admin_password      VARCHAR(255) NOT NULL,          -- bcrypt for per-project admin
  created_at          DATETIME DEFAULT NOW()
);

-- Background photos per project (up to 10)
CREATE TABLE IF NOT EXISTS project_photos (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  project_id  INT NOT NULL,
  filename    VARCHAR(255) NOT NULL,
  sort_order  INT DEFAULT 0,
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Invitations (token groups) per project
CREATE TABLE IF NOT EXISTS invitations (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  project_id  INT NOT NULL,
  token       VARCHAR(100) NOT NULL,
  created_at  DATETIME DEFAULT NOW(),
  UNIQUE KEY  (project_id, token),
  FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- Individual invitees
CREATE TABLE IF NOT EXISTS invitees (
  id              INT AUTO_INCREMENT PRIMARY KEY,
  invitation_id   INT NOT NULL,
  name            VARCHAR(255) NOT NULL,
  response_status ENUM('Accept','Decline') DEFAULT NULL,
  responded_at    DATETIME DEFAULT NULL,
  FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE CASCADE
);

-- Default super admin  (password: SuperAdmin123!)
INSERT IGNORE INTO super_admins (username, password)
VALUES ('superadmin', '$2y$12$9Q8F2zL1kM3nP4rT5vW6uO7YhXqJ0sA1bC2dE3fG4hI5jK6lM7nN8');
-- IMPORTANT: Run this PHP to generate your own hash:
-- echo password_hash('YourPassword', PASSWORD_BCRYPT);