-- =====================================================
-- Database Setup Script for Axel Guest Registration System
-- Host: localhost
-- Database: thez9859_guests
-- =====================================================

-- Drop existing tables (if you want to reset)
-- DROP TABLE IF EXISTS attendance;
-- DROP TABLE IF EXISTS invitations;

-- Create invitations table (untuk data undangan tamu)
CREATE TABLE IF NOT EXISTS invitations (
    id VARCHAR(50) PRIMARY KEY,
    guest_name VARCHAR(255) NOT NULL,
    guest_email VARCHAR(255),
    guest_phone VARCHAR(50),
    total_guests INT NOT NULL DEFAULT 1,
    location VARCHAR(255) DEFAULT 'Axel Office',
    event_date DATETIME NOT NULL,
    status ENUM('active', 'used', 'cancelled') DEFAULT 'active',
    qr_code_data TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_qr_data (qr_code_data),
    INDEX idx_status (status),
    INDEX idx_event_date (event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create attendance table (untuk data kehadiran check-in)
CREATE TABLE IF NOT EXISTS attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invitation_id VARCHAR(50) NOT NULL,
    guest_name VARCHAR(255) NOT NULL,
    total_guests INT NOT NULL,
    check_in_time DATETIME NOT NULL,
    location VARCHAR(255) DEFAULT 'Axel Office',
    notes TEXT,
    scanned_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (invitation_id) REFERENCES invitations(id) ON DELETE CASCADE,
    INDEX idx_invitation (invitation_id),
    INDEX idx_checkin (check_in_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample invitations (contoh data)
INSERT INTO invitations (id, guest_name, guest_email, guest_phone, total_guests, event_date, qr_code_data) VALUES
('RR-001', 'John Doe', 'john@email.com', '6281234567890', 2, '2025-01-20 10:00:00', 'RR-001|JOHN DOE|2'),
('RR-002', 'Jane Smith', 'jane@email.com', '6280987654321', 1, '2025-01-20 10:00:00', 'RR-002|JANE SMITH|1'),
('RR-003', 'Robert Brown', 'robert@email.com', '6285555555555', 4, '2025-01-20 10:00:00', 'RR-003|ROBERT BROWN|4');