-- Database Schema for SambAlegria Website
-- Run this SQL to create the necessary tables

CREATE DATABASE IF NOT EXISTS sambalegria CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sambalegria;

-- Events table with enhanced location fields
CREATE TABLE IF NOT EXISTS events (
                                      id INT AUTO_INCREMENT PRIMARY KEY,
                                      title VARCHAR(255) NOT NULL,
    description TEXT,
    event_date DATETIME NOT NULL,
    location_name VARCHAR(255),
    location_address TEXT,
    location_city VARCHAR(100),
    location_postal_code VARCHAR(20),
    location_coordinates VARCHAR(100),
    status ENUM('upcoming', 'completed', 'cancelled') DEFAULT 'upcoming',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gallery categories table
CREATE TABLE IF NOT EXISTS gallery_categories (
                                                  id INT AUTO_INCREMENT PRIMARY KEY,
                                                  name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Gallery items table
CREATE TABLE IF NOT EXISTS gallery_items (
                                             id INT AUTO_INCREMENT PRIMARY KEY,
                                             category_id INT,
                                             title VARCHAR(255) NOT NULL,
    description TEXT,
    file_path VARCHAR(500) NOT NULL,
    file_type ENUM('image', 'video') NOT NULL,
    mime_type VARCHAR(100),
    file_size INT,
    sort_order INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES gallery_categories(id) ON DELETE SET NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Contact messages table
CREATE TABLE IF NOT EXISTS contact_messages (
                                                id INT AUTO_INCREMENT PRIMARY KEY,
                                                name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    event_type VARCHAR(100),
    event_date DATE,
    message TEXT NOT NULL,
    status ENUM('new', 'read', 'replied') DEFAULT 'new',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Security tables for admin authentication
CREATE TABLE IF NOT EXISTS login_attempts (
                                              id INT AUTO_INCREMENT PRIMARY KEY,
                                              ip_address VARCHAR(45) NOT NULL,
    username VARCHAR(50),
    attempt_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    success BOOLEAN DEFAULT FALSE,
    user_agent TEXT,
    INDEX idx_ip_time (ip_address, attempt_time)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS admin_sessions (
                                              session_id VARCHAR(128) PRIMARY KEY,
    user_id VARCHAR(50) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_activity TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    expires_at TIMESTAMP NOT NULL,
    is_active BOOLEAN DEFAULT TRUE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Site settings table
CREATE TABLE IF NOT EXISTS site_settings (
                                             setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data with enhanced location information
INSERT IGNORE INTO events (title, description, event_date, location_name, location_address, location_city, location_postal_code) VALUES
('Stader Altstadtfest', 'Traditionelle Samba-Rhythmen im Herzen der Altstadt', '2025-10-15 19:00:00', 'Altstadt Stade', 'Am Fischmarkt 1', 'Stade', '21682'),
('Vörder Seefest', 'Rhythmus am Wasser - Samba meets Norddeutschland', '2025-11-22 20:00:00', 'Bremervörder See', 'Seestraße 10', 'Bremervörde', '27432'),
('Weihnachtsmarkt Bremervörde', 'Warme Samba-Klänge für die kalte Jahreszeit', '2025-12-05 18:00:00', 'Marktplatz Bremervörde', 'Lange Straße 2', 'Bremervörde', '27432');

-- Insert sample gallery categories
INSERT IGNORE INTO gallery_categories (name, description) VALUES
('Auftritte 2025', 'Bilder und Videos von unseren Auftritten im Jahr 2025'),
('Proben & Training', 'Einblicke in unsere Probenarbeit'),
('Instrumente', 'Unsere vielfältigen Samba-Instrumente'),
('Bloco do Norte', 'Gemeinsame Auftritte mit anderen Samba-Gruppen');

-- Insert default site settings
INSERT IGNORE INTO site_settings (setting_key, setting_value) VALUES
('site_title', 'SambAlegria Bremervörde'),
('contact_email', 'info@sambalegria-bremervoerde.de'),
('contact_phone', '+49 4761 12345'),
('google_maps_api_key', 'YOUR_GOOGLE_MAPS_API_KEY'),
('about_text', 'Wir sind SambAlegria Bremervörde, eine lebendige Sambatrommelgruppe aus Bremervörde, Gnarrenburg und Umgebung. Seit 2008 bringen wir brasilianische Rhythmen und pure Lebensfreude zu verschiedensten Anlässen.'),
('facebook_url', 'https://facebook.com/sambalegria'),
('instagram_url', 'https://instagram.com/sambalegria'),
('youtube_url', 'https://youtube.com/sambalegria'),
('Vörder Seefest', 'Rhythmus am Wasser - Samba meets Norddeutschland', '2025-11-22 20:00:00', 'Bremervörde Seeufer'),
('Weihnachtsmarkt Bremervörde', 'Warme Samba-Klänge für die kalte Jahreszeit', '2025-12-05 18:00:00', 'Weihnachtsmarkt Bremervörde');

-- Insert sample gallery categories
INSERT INTO gallery_categories (name, description) VALUES
                                                       ('Auftritte 2025', 'Bilder und Videos von unseren Auftritten im Jahr 2025'),
                                                       ('Proben & Training', 'Einblicke in unsere Probenarbeit'),
                                                       ('Instrumente', 'Unsere vielfältigen Samba-Instrumente'),
                                                       ('Bloco do Norte', 'Gemeinsame Auftritte mit anderen Samba-Gruppen');

-- Insert default site settings
INSERT INTO site_settings (setting_key, setting_value) VALUES
                                                           ('site_title', 'SambAlegria Bremervörde'),
                                                           ('contact_email', 'info@sambalegria-bremervoerde.de'),
                                                           ('contact_phone', '+49 4761 12345'),
                                                           ('about_text', 'Wir sind SambAlegria Bremervörde, eine lebendige Sambatrommelgruppe aus Bremervörde, Gnarrenburg und Umgebung. Seit 2008 bringen wir brasilianische Rhythmen und pure Lebensfreude zu verschiedensten Anlässen.'),
                                                           ('facebook_url', 'https://facebook.com/sambalegria'),
                                                           ('instagram_url', 'https://instagram.com/sambalegria'),
                                                           ('youtube_url', 'https://youtube.com/sambalegria');