

CREATE TABLE IF NOT EXISTS streams (
    id INT AUTO_INCREMENT PRIMARY KEY,
    stream_type ENUM('live', 'movie', 'series') NOT NULL,
    title VARCHAR(255) NOT NULL,
    stream_url VARCHAR(500) NOT NULL,
    stream_icon VARCHAR(500) DEFAULT NULL,
    category_id INT DEFAULT NULL,
    status ENUM('online', 'offline') DEFAULT 'online',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL,
    category_type ENUM('live', 'movie', 'series') NOT NULL
);

CREATE TABLE IF NOT EXISTS events (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL, -- Esempio: "Inter vs Milan"
    league_name VARCHAR(100) DEFAULT NULL, -- Esempio: "Serie A"
    event_time VARCHAR(10) DEFAULT NULL, -- Esempio: "20:45"
    stream_id INT DEFAULT NULL, -- Il canale IPTV associato
    status ENUM('active', 'hidden') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS settings (
    setting_key VARCHAR(50) PRIMARY KEY,
    setting_value TEXT
);

-- Inserimento impostazioni base
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('maintenance_mode', '0');
INSERT IGNORE INTO settings (setting_key, setting_value) VALUES ('global_announcement', 'Benvenuti nel pannello Addicted By The Server!');

CREATE TABLE IF NOT EXISTS favorites (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    stream_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE CASCADE,
    UNIQUE KEY (user_id, stream_id) -- Un utente non può aggiungere due volte lo stesso preferito
);

CREATE TABLE IF NOT EXISTS continue_watching (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    stream_id INT NOT NULL,
    position INT DEFAULT 0, -- Posizione in secondi
    duration INT DEFAULT 0, -- Durata totale (opzionale)
    last_watched TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (stream_id) REFERENCES streams(id) ON DELETE CASCADE,
    UNIQUE KEY (user_id, stream_id) -- Un record per utente/film
);
