db_init.sql

-- OpenDonate - Inicjalizacja bazy danych / Database initialization
--
-- INSTRUKCJA / INSTRUCTIONS:
-- 1. Utwórz bazę danych (np. "opendonate") w phpMyAdmin lub przez SQL:
--    CREATE DATABASE opendonate CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 2. Wybierz bazę (USE opendonate; lub w phpMyAdmin)
-- 3. Wykonaj ten plik
-- 4. Domyślne dane logowania: admin / password (ZMIEŃ PO PIERWSZYM LOGOWANIU!)

-- UWAGA: Odkomentuj linię niżej jeśli wykonujesz przez CLI / Uncomment if running via CLI:
-- USE opendonate;

DROP TABLE IF EXISTS voice_messages;
DROP TABLE IF EXISTS donation_control;
DROP TABLE IF EXISTS message_templates;
DROP TABLE IF EXISTS amount_templates;
DROP TABLE IF EXISTS donations;
DROP TABLE IF EXISTS goals;
DROP TABLE IF EXISTS settings;
DROP TABLE IF EXISTS users;

-- ============================================================
-- UŻYTKOWNICY / USERS
-- ============================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Domyślny admin: admin / password
-- ⚠️ ZMIEŃ HASŁO PO PIERWSZYM LOGOWANIU! / CHANGE PASSWORD AFTER FIRST LOGIN!
INSERT INTO users (username, password, email)
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@example.com');

-- ============================================================
-- CELE DONACJI / DONATION GOALS (wsparcie wielu celów + domyślny)
-- ============================================================
CREATE TABLE goals (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    target_amount DECIMAL(10, 2) NOT NULL,
    current_amount DECIMAL(10, 2) DEFAULT 0.00,
    is_active TINYINT(1) DEFAULT 1,
    is_default TINYINT(1) DEFAULT 0,
    show_in_overlay TINYINT(1) DEFAULT 1,
    show_on_donation_page TINYINT(1) DEFAULT 1,
    bar_style ENUM('normal', 'striped', 'animated', 'gradient') DEFAULT 'animated',
    display_order INT DEFAULT 0,
    start_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_date TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_active (is_active),
    INDEX idx_default (is_default)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Przykładowe cele / Example goals
INSERT INTO goals (title, description, target_amount, is_active, is_default, show_in_overlay, show_on_donation_page, bar_style, display_order) VALUES
('Nowy sprzęt', 'Zbieramy na nowy sprzęt do streamowania!', 5000.00, 1, 1, 1, 1, 'animated', 1),
('Wakacje ekipy', 'Wyjazd integracyjny dla najlepszych widzów!', 10000.00, 1, 0, 0, 1, 'gradient', 2);

-- ============================================================
-- DONACJE / DONATIONS (z polem goal_id dla wyboru celu przez widza)
-- ============================================================
CREATE TABLE donations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    donor_name VARCHAR(100) NOT NULL,
    email VARCHAR(100),
    amount DECIMAL(10, 2) NOT NULL,
    message TEXT,
    voice_message_path VARCHAR(255),
    voice_message_duration INT,
    status ENUM('pending', 'paid', 'failed') DEFAULT 'pending',
    is_seen TINYINT(1) DEFAULT 0,
    is_processing TINYINT(1) DEFAULT 0,
    is_muted TINYINT(1) DEFAULT 0,
    is_paused TINYINT(1) DEFAULT 0,
    is_skipped TINYINT(1) DEFAULT 0,
    can_repeat TINYINT(1) DEFAULT 1,
    repeat_count INT DEFAULT 0,
    transaction_id VARCHAR(100),
    control_id VARCHAR(100) UNIQUE,
    template_id INT,
    goal_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_status_seen (status, is_seen),
    INDEX idx_control (control_id),
    INDEX idx_donations_queue (status, is_seen, is_processing, created_at, id),
    INDEX idx_donations_processing (is_processing, is_seen, updated_at),
    INDEX idx_goal (goal_id),
    FOREIGN KEY (goal_id) REFERENCES goals(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- USTAWIENIA / SETTINGS
-- ============================================================
CREATE TABLE settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) NOT NULL UNIQUE,
    setting_value TEXT,
    setting_type ENUM('text','number','boolean','json') DEFAULT 'text',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Domyślne ustawienia / Default settings
INSERT INTO settings (setting_key, setting_value, setting_type) VALUES

-- === BRANDING (do dostosowania pod siebie w panelu admina) ===
-- === BRANDING (customize via admin panel) ===
('site_title', 'OpenDonate', 'text'),
('brand_name', 'OpenDonate', 'text'),
('brand_logo_url', '', 'text'),
('brand_url', '#', 'text'),
('brand_favicon_url', '', 'text'),
('contact_email', 'contact@example.com', 'text'),
('operator_name', 'Operator', 'text'),
('streamer_name', 'Streamer', 'text'),

-- === METODY PŁATNOŚCI / PAYMENT METHODS ===
-- Stripe
('stripe_secret_key', '', 'text'),
('stripe_publishable_key', '', 'text'),
('stripe_webhook_secret', '', 'text'),
('stripe_fee_enabled', '0', 'boolean'),
('stripe_fee_percent', '1.6', 'number'),
('stripe_fee_fixed', '1.00', 'number'),
-- PayPal
('paypal_email', '', 'text'),
('paypal_fee_enabled', '0', 'boolean'),
('paypal_fee_percent', '2.90', 'number'),
('paypal_fee_fixed', '1.45', 'number'),

-- === CZCIONKI - NICK / FONTS - NICKNAME ===
('alert_font_family_name', 'Rubik', 'text'),
('alert_font_size_name', '36', 'number'),
('alert_font_weight_name', '700', 'number'),
('alert_font_stroke_name', '2', 'number'),
('alert_font_stroke_color_name', '#000000', 'text'),
('alert_font_shadow_name', '2px 2px 4px rgba(0,0,0,0.8)', 'text'),

-- === CZCIONKI - WIADOMOŚĆ / FONTS - MESSAGE ===
('alert_font_family_message', 'Arial', 'text'),
('alert_font_size_message', '24', 'number'),
('alert_font_weight_message', '400', 'number'),
('alert_font_stroke_message', '0', 'number'),
('alert_font_stroke_color_message', '#000000', 'text'),
('alert_font_shadow_message', '1px 1px 2px rgba(0,0,0,0.6)', 'text'),

-- === KOLORY / COLORS ===
('alert_bg_color', '#121212', 'text'),
('alert_text_color', '#FFFFFF', 'text'),
('alert_accent_color', '#FF0000', 'text'),
('alert_bg_opacity', '0.95', 'number'),

-- === DŹWIĘK I TTS / SOUND AND TTS ===
('alert_intro_sound', '', 'text'),
('alert_intro_volume', '70', 'number'),
('tts_enabled', '1', 'boolean'),
('tts_voice', 'pl-PL', 'text'),
('tts_voice_name', 'Microsoft Paulina Online (Natural) - Polish (Poland)', 'text'),
('tts_volume', '100', 'number'),
('tts_speed', '1.0', 'number'),

-- === FILTROWANIE / FILTERING ===
('alert_max_chars', '200', 'number'),
('alert_min_donation', '1.00', 'number'),
('alert_filter_emoji', '1', 'boolean'),
('alert_filter_special_chars', '1', 'boolean'),
('alert_filter_urls', '1', 'boolean'),
('alert_filter_word_repeat', '1', 'boolean'),
('voice_message_max_duration', '15', 'number'),
('voice_message_auto_delete_days', '60', 'number'),

-- === ANIMACJE / ANIMATIONS ===
('alert_animation_in', 'slideInUp', 'text'),
('alert_animation_out', 'fadeOut', 'text'),
('alert_duration', '30000', 'number'),
('alert_auto_duration', '1', 'number'),
('alert_min_duration', '5000', 'number'),

-- === CEL DONACJI / DONATION GOAL OVERLAY ===
('goal_overlay_enabled', '1', 'boolean'),
('goal_update_realtime', '1', 'boolean'),
('goal_bg_color', 'rgba(18, 18, 18, 0.9)', 'text'),
('goal_text_color', '#FFFFFF', 'text'),
('goal_bar_color', '#757aff', 'text'),
('goal_bar_bg_color', '#333333', 'text'),
('goal_bar_height', '30', 'number'),
('goal_bar_animation', 'striped', 'text'),
('goal_font_family', 'Poppins', 'text'),
('goal_font_size', '24', 'number'),
('goal_opacity', '0.9', 'number'),
('goal_overlay_rotate_seconds', '8', 'number'),

-- === TOP DONATE OVERLAY ===
('top_donate_enabled', '1', 'boolean'),
('top_donate_count', '5', 'number'),
('top_donate_period', 'all', 'text'),
('top_donate_custom_from', '', 'text'),
('top_donate_align', 'left', 'text'),
('top_donate_autoscroll', '0', 'boolean'),
('top_donate_style', 'vertical', 'text'),
('top_donate_scroll_speed', '50', 'number'),
('top_donate_bg_color', 'transparent', 'text'),
('top_donate_title_color', '#FFFFFF', 'text'),
('top_donate_position_color', '#00FF00', 'text'),
('top_donate_nick_color', '#FFFFFF', 'text'),
('top_donate_amount_color', '#00FF00', 'text'),
('top_donate_font_family', 'Poppins', 'text'),
('top_donate_font_size', '32', 'number'),

-- === LAST DONATE OVERLAY ===
('last_donate_enabled', '1', 'boolean'),
('last_donate_count', '5', 'number'),
('last_donate_period', 'all', 'text'),
('last_donate_custom_from', '', 'text'),
('last_donate_align', 'left', 'text'),
('last_donate_autoscroll', '0', 'boolean'),
('last_donate_style', 'vertical', 'text'),
('last_donate_scroll_speed', '50', 'number'),
('last_donate_bg_color', 'transparent', 'text'),
('last_donate_position_color', '#FFFFFF', 'text'),
('last_donate_nick_color', '#FFFFFF', 'text'),
('last_donate_amount_color', '#00FF00', 'text'),
('last_donate_font_family', 'Poppins', 'text'),
('last_donate_font_size', '32', 'number'),

-- === ODLICZNIK DO KOŃCA TRANSMISJI / STREAM COUNTDOWN ===
('countdown_enabled', '0', 'boolean'),
('countdown_running', '0', 'boolean'),
('countdown_end_timestamp', '0', 'number'),
('countdown_remaining_seconds', '0', 'number'),
('countdown_pln_per_unit', '1.00', 'number'),
('countdown_seconds_per_unit', '60', 'number'),
('countdown_min_amount', '0.00', 'number'),
('countdown_label', 'Do końca transmisji', 'text'),
('countdown_font_family', 'Orbitron', 'text'),
('countdown_font_size', '64', 'number'),
('countdown_label_font_size', '18', 'number'),
('countdown_text_color', '#FFFFFF', 'text'),
('countdown_label_color', '#FFD700', 'text'),
('countdown_bg_color', 'rgba(18, 18, 18, 0.85)', 'text'),
('countdown_hide_when_stopped', '0', 'boolean'),

-- === OGÓLNE / GENERAL ===
('theme', 'dark', 'text'),
('min_donation_amount', '1.00', 'number'),
('max_donation_amount', '10000.00', 'number'),
('show_goals_on_donation_page', '1', 'boolean'),

-- === PANEL STEROWANIA / OVERLAY CONTROL ===
('overlay_paused', '0', 'boolean'),
('overlay_muted', '0', 'boolean');

-- ============================================================
-- SZABLONY KWOTOWE / AMOUNT TEMPLATES
-- ============================================================
CREATE TABLE amount_templates (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    min_amount DECIMAL(10, 2) NOT NULL,
    is_active TINYINT(1) DEFAULT 1,
    display_order INT DEFAULT 0,

    -- Dźwięk
    intro_sound VARCHAR(255),
    intro_sound_file VARCHAR(255),
    intro_sound_enabled TINYINT(1) DEFAULT 1,

    -- TTS
    tts_enabled TINYINT(1) DEFAULT 1,
    tts_voice VARCHAR(100) DEFAULT 'pl-PL',
    tts_volume INT DEFAULT 100,
    tts_template TEXT,

    -- Filtrowanie
    filter_urls TINYINT(1) DEFAULT 1,
    filter_special_chars TINYINT(1) DEFAULT 1,
    filter_word_repeat TINYINT(1) DEFAULT 1,

    -- Wygląd
    nick_color VARCHAR(7) DEFAULT '#FFFFFF',
    message_color VARCHAR(7) DEFAULT '#FFAA00',
    background_color VARCHAR(50) DEFAULT '#121212',
    background_pattern VARCHAR(50),
    animation_style VARCHAR(50) DEFAULT 'slideInLeft',

    -- Czcionki
    font_family_name VARCHAR(100) DEFAULT 'Rubik',
    font_size_name INT DEFAULT 36,
    font_family_message VARCHAR(100) DEFAULT 'Arial',
    font_size_message INT DEFAULT 24,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_amount (min_amount)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Domyślne szablony kwotowe / Default amount templates
INSERT INTO amount_templates (
    name, min_amount, intro_sound_enabled, tts_enabled, tts_template,
    filter_urls, filter_special_chars, filter_word_repeat,
    nick_color, message_color, background_color, display_order
) VALUES
('Standardowy', 0.00, 1, 1, '{NICK} przesyła {KWOTA} złotych. {WIADOMOŚĆ}', 1, 1, 1, '#FFFFFF', '#CCCCCC', '#121212', 1),
('Srebrny', 10.00, 1, 1, '{NICK} wpłaca {KWOTA} PLN! {WIADOMOŚĆ}', 1, 1, 1, '#C0C0C0', '#E0E0E0', '#1a1a1a', 2),
('Złoty', 50.00, 1, 1, 'Mega donate od {NICK} na kwotę {KWOTA} złotych! {WIADOMOŚĆ}', 1, 1, 1, '#FFD700', '#FFA500', '#1a1a1a', 3),
('Platynowy', 100.00, 1, 1, 'Ogromny donate! {NICK} przekazuje {KWOTA} PLN! {WIADOMOŚĆ}', 1, 1, 1, '#E5E4E2', '#B9F2FF', '#0a0a0a', 4),
('Diamentowy', 500.00, 1, 1, 'MEGA DONATE! {NICK} wpłaca aż {KWOTA} złotych! {WIADOMOŚĆ}', 1, 1, 1, '#00FFFF', '#00CED1', '#000000', 5);

-- ============================================================
-- WIADOMOŚCI GŁOSOWE / VOICE MESSAGES
-- ============================================================
CREATE TABLE voice_messages (
    id INT AUTO_INCREMENT PRIMARY KEY,
    donation_id INT,
    file_path VARCHAR(255) NOT NULL,
    duration FLOAT NOT NULL,
    file_size INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP,
    is_deleted TINYINT(1) DEFAULT 0,
    FOREIGN KEY (donation_id) REFERENCES donations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- KONTROLA DONACJI / DONATION CONTROL (real-time overlay control)
-- ============================================================
CREATE TABLE donation_control (
    id INT AUTO_INCREMENT PRIMARY KEY,
    action ENUM('mute', 'pause', 'skip', 'resume', 'repeat') NOT NULL,
    target_donation_id INT,
    admin_id INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (target_donation_id) REFERENCES donations(id) ON DELETE CASCADE,
    FOREIGN KEY (admin_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- ✅ GOTOWE! / DONE!
-- Teraz przejdź do panelu admina: /admin.php
-- Login: admin / Password: password
-- ⚠️ ZMIEŃ HASŁO PO PIERWSZYM LOGOWANIU!
-- ============================================================