CREATE DATABASE IF NOT EXISTS wedding CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE wedding;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  phone VARCHAR(40) NOT NULL,
  email VARCHAR(255),
  pricing ENUM('single','double') NOT NULL DEFAULT 'single',
  token CHAR(40) NOT NULL UNIQUE,
  paid TINYINT(1) NOT NULL DEFAULT 0,
  payment_ref VARCHAR(100) DEFAULT NULL,
  ticket_qr TEXT DEFAULT NULL,
  template VARCHAR(50) DEFAULT 'card_minimal',
  used TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE sends (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  channel ENUM('sms','whatsapp') NOT NULL,
  payload TEXT,
  status ENUM('pending','sent','failed') DEFAULT 'pending',
  response TEXT,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE admins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(100) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE scan_logs (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  scanned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  action ENUM('verified','already_used','invalid') NOT NULL,
  note TEXT
);

INSERT INTO admins (username, password_hash) VALUES ('admin', '');
