-- ==========================================
-- Undangan QR Web - MySQL Schema
-- ==========================================
CREATE DATABASE IF NOT EXISTS undangan_qr
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE undangan_qr;

-- Events (optional, tapi sudah disiapkan)
CREATE TABLE IF NOT EXISTS events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(100) NOT NULL UNIQUE,
  name VARCHAR(200) NOT NULL,
  event_date DATETIME NULL,
  location_name VARCHAR(255) NULL,
  maps_url TEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Guests
CREATE TABLE IF NOT EXISTS guests (
  id INT AUTO_INCREMENT PRIMARY KEY,
  event_id INT NOT NULL,
  name VARCHAR(120) NOT NULL,
  phone VARCHAR(30) NULL,
  code VARCHAR(32) NOT NULL UNIQUE,
  invited_qty INT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_guests_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  INDEX idx_guests_event (event_id),
  INDEX idx_guests_name (name)
) ENGINE=InnoDB;

-- Guest status (1 row per guest)
CREATE TABLE IF NOT EXISTS guest_status (
  guest_id INT PRIMARY KEY,
  checked_in_at DATETIME NULL,
  checked_in_by INT NULL,
  souvenir_at DATETIME NULL,
  souvenir_by INT NULL,
  CONSTRAINT fk_status_guest FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE,
  CONSTRAINT fk_status_checked_by FOREIGN KEY (checked_in_by) REFERENCES users(id) ON DELETE SET NULL,
  CONSTRAINT fk_status_souvenir_by FOREIGN KEY (souvenir_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Scan logs (audit)
CREATE TABLE IF NOT EXISTS scan_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  event_id INT NOT NULL,
  guest_id INT NOT NULL,
  scan_type ENUM('CHECKIN','SOUVENIR') NOT NULL,
  scan_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  operator_id INT NOT NULL,
  device_info VARCHAR(255) NULL,
  CONSTRAINT fk_logs_event FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
  CONSTRAINT fk_logs_guest FOREIGN KEY (guest_id) REFERENCES guests(id) ON DELETE CASCADE,
  CONSTRAINT fk_logs_operator FOREIGN KEY (operator_id) REFERENCES users(id) ON DELETE RESTRICT,
  UNIQUE KEY uniq_guest_scantype (guest_id, scan_type),
  INDEX idx_logs_event_time (event_id, scan_time)
) ENGINE=InnoDB;

-- ==========================================
-- Seed data
-- ==========================================
INSERT INTO events (slug, name, event_date, location_name, maps_url)
VALUES ('demo-event', 'DEMO EVENT', NOW(), 'Jakarta', 'https://maps.google.com')
ON DUPLICATE KEY UPDATE name=VALUES(name);

-- Demo guests
INSERT INTO guests (event_id, name, phone, code, invited_qty)
SELECT (SELECT id FROM events WHERE slug='demo-event' LIMIT 1), 'Budi Santoso', '08123456789', 'DEMO123456', 1
WHERE NOT EXISTS (SELECT 1 FROM guests WHERE code='DEMO123456');

INSERT INTO guest_status (guest_id)
SELECT g.id FROM guests g
WHERE g.code='DEMO123456'
  AND NOT EXISTS (SELECT 1 FROM guest_status gs WHERE gs.guest_id=g.id);

-- Notes:
-- - uniq_guest_scantype memastikan 1x CHECKIN dan 1x SOUVENIR per guest.
-- - Atomic update dilakukan di aplikasi untuk anti double-scan.
