CREATE DATABASE IF NOT EXISTS webt7708_convitesonline_db;
USE webt7708_convitesonline_db;

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    whatsapp VARCHAR(20),
    password VARCHAR(255) NOT NULL,
    plan_id INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (plan_id) REFERENCES plans(id)
);

CREATE TABLE IF NOT EXISTS events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    type ENUM('Aniversário', 'Casamento', 'Chá de Bebê', 'Formatura', 'Corporativo', 'Outro') NOT NULL,
    title VARCHAR(255) NOT NULL,
    hosts VARCHAR(255),
    event_date DATE,
    event_time TIME,
    location VARCHAR(255),
    address TEXT,
    maps_link TEXT,
    message TEXT,
    banner VARCHAR(255),
    background_image VARCHAR(255),
    theme VARCHAR(50) DEFAULT 'default',
    primary_color VARCHAR(7) DEFAULT '#ff6600',
    font_style VARCHAR(50) DEFAULT 'Inter',
    animation_style VARCHAR(50) DEFAULT 'fade-in',
    decorative_frame VARCHAR(50) DEFAULT 'none',
    bg_music_url VARCHAR(255),
    opening_video_url VARCHAR(255),
    fullscreen_banner BOOLEAN DEFAULT FALSE,
    has_gallery BOOLEAN DEFAULT FALSE,
    start_date DATE,
    expiry_date DATE,
    rsvp_deadline DATETIME,
    status ENUM('Ativo', 'Expirado', 'Pausado') DEFAULT 'Ativo',
    views_count INT DEFAULT 0,
    last_access TIMESTAMP NULL,
    password VARCHAR(255),
    is_private BOOLEAN DEFAULT FALSE,
    allow_guests BOOLEAN DEFAULT TRUE,
    guest_limit INT DEFAULT 0,
    whatsapp VARCHAR(20),
    slug VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS event_gallery (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    image_path VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS rsvps (
    id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    whatsapp VARCHAR(20),
    status ENUM('Sim', 'Não', 'Talvez') NOT NULL,
    guest_count INT DEFAULT 0,
    guest_names TEXT,
    note TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS plans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    event_limit INT,
    confirmation_limit INT,
    validity_days INT, -- days the invitation stays active
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    site_name VARCHAR(100) DEFAULT 'webTHI Convites Online',
    logo VARCHAR(255) DEFAULT '/img/logo.png',
    favicon VARCHAR(255) DEFAULT '/favicon.ico',
    support_whatsapp VARCHAR(20),
    primary_color VARCHAR(7) DEFAULT '#ff6a00',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

INSERT IGNORE INTO settings (id, site_name, logo, favicon) VALUES (1, 'webTHI Convites Online', '/img/logo.png', '/favicon.ico');

-- Insert default plans
INSERT IGNORE INTO plans (id, name, price, event_limit, confirmation_limit, validity_days) VALUES 
(1, 'Básico', 49.90, 1, 50, 15),
(2, 'Premium', 89.90, 3, 300, 60),
(3, 'Profissional', 149.90, -1, -1, 3650);

-- Insert initial Admin user (Password: #webTHI-1979#)
-- Hashed with bcrypt: $2a$10$N.i9n8vM7Q.0y8F3Z2W6uO6N8i9n8vM7Q.0y8F3Z2W6uO6N8i9n8v (Sample hash, user should register for real hash)
INSERT IGNORE INTO users (id, name, email, whatsapp, password, plan_id) VALUES 
(1, 'Admin webTHI', 'webthi@webthi.com.br', '(00) 00000-0000', '$2a$10$7Z8K.x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8x8', 3);