-- ============================
-- Tabela użytkowników
-- ============================
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    role TEXT NOT NULL DEFAULT 'user', -- user/admin
    active INTEGER NOT NULL DEFAULT 1,
    max_connections INTEGER NOT NULL DEFAULT 1,
    expire_date INTEGER DEFAULT NULL,
    allowed_ips TEXT DEFAULT NULL,
    allowed_ua TEXT DEFAULT NULL,
    created_at INTEGER NOT NULL
);

-- Sesje użytkowników
CREATE TABLE IF NOT EXISTS user_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    ip TEXT NOT NULL,
    user_agent TEXT,
    login_time INTEGER NOT NULL,
    last_activity INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Logi użytkowników
CREATE TABLE IF NOT EXISTS user_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    action TEXT NOT NULL,
    details TEXT,
    ip TEXT,
    created_at INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Sesje adminów
CREATE TABLE IF NOT EXISTS admin_sessions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_id INTEGER NOT NULL,
    ip TEXT NOT NULL,
    user_agent TEXT,
    login_time INTEGER NOT NULL,
    last_activity INTEGER NOT NULL,
    FOREIGN KEY(admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Logi adminów
CREATE TABLE IF NOT EXISTS admin_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    admin_id INTEGER NOT NULL,
    action TEXT NOT NULL,
    details TEXT,
    ip TEXT,
    created_at INTEGER NOT NULL,
    FOREIGN KEY(admin_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Pakiety
CREATE TABLE IF NOT EXISTS packages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    type TEXT NOT NULL CHECK(type IN ('live','vod','series'))
);

-- Pakiety przypisane do użytkowników
CREATE TABLE IF NOT EXISTS user_packages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    package_id INTEGER NOT NULL,
    FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY(package_id) REFERENCES packages(id) ON DELETE CASCADE
);

-- Kategorie Live
CREATE TABLE IF NOT EXISTS live_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

-- Kanały Live
CREATE TABLE IF NOT EXISTS live_channels (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    url TEXT NOT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY(category_id) REFERENCES live_categories(id) ON DELETE CASCADE
);

-- Przypisania kanałów Live do pakietów
CREATE TABLE IF NOT EXISTS stream_packages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    stream_id INTEGER NOT NULL,
    package_id INTEGER NOT NULL,
    FOREIGN KEY(stream_id) REFERENCES live_channels(id) ON DELETE CASCADE,
    FOREIGN KEY(package_id) REFERENCES packages(id) ON DELETE CASCADE
);

-- Kategorie VOD
CREATE TABLE IF NOT EXISTS vod_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

-- Filmy VOD
CREATE TABLE IF NOT EXISTS vod (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    url TEXT NOT NULL,
    year INTEGER DEFAULT NULL,
    description TEXT DEFAULT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY(category_id) REFERENCES vod_categories(id) ON DELETE CASCADE
);

-- Przypisania VOD do pakietów
CREATE TABLE IF NOT EXISTS vod_packages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    vod_id INTEGER NOT NULL,
    package_id INTEGER NOT NULL,
    FOREIGN KEY(vod_id) REFERENCES vod(id) ON DELETE CASCADE,
    FOREIGN KEY(package_id) REFERENCES packages(id) ON DELETE CASCADE
);

-- Kategorie seriali
CREATE TABLE IF NOT EXISTS series_categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE
);

-- Seriale
CREATE TABLE IF NOT EXISTS series (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    url TEXT NOT NULL,
    seasons INTEGER DEFAULT NULL,
    description TEXT DEFAULT NULL,
    category_id INTEGER NOT NULL,
    FOREIGN KEY(category_id) REFERENCES series_categories(id) ON DELETE CASCADE
);

-- Przypisania seriali do pakietów
CREATE TABLE IF NOT EXISTS series_packages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    series_id INTEGER NOT NULL,
    package_id INTEGER NOT NULL,
    FOREIGN KEY(series_id) REFERENCES series(id) ON DELETE CASCADE,
    FOREIGN KEY(package_id) REFERENCES packages(id) ON DELETE CASCADE
);

-- Ustawienia systemu
CREATE TABLE IF NOT EXISTS settings (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    value TEXT DEFAULT NULL
);

-- =================================
-- PRZYKŁADOWE DANE
-- =================================


-- Pakiety
INSERT INTO packages (name, type) VALUES
('Pakiet Standard Live', 'live'),
('Pakiet Premium Live', 'live'),
('Pakiet VOD Standard', 'vod'),
('Pakiet VOD Premium', 'vod'),
('Pakiet Seriale', 'series');

-- Kategorie Live
INSERT INTO live_categories (name) VALUES
('Informacyjne'), ('Sport'), ('Filmy'), ('Muzyka');

-- Kanały Live
INSERT INTO live_channels (name, url, category_id) VALUES
('News 24', 'http://example.com/live/news24.m3u8', 1),
('Sport HD', 'http://example.com/live/sporthd.m3u8', 2),
('Cinema Plus', 'http://example.com/live/cinemaplus.m3u8', 3),
('Music Hits', 'http://example.com/live/musichits.m3u8', 4);

-- Przypisania Live do pakietów
INSERT INTO stream_packages (stream_id, package_id) VALUES
(1, 1), (2, 2), (3, 2), (4, 1);

-- Kategorie VOD
INSERT INTO vod_categories (name) VALUES
('Akcja'), ('Komedia'), ('Dramat');

-- Filmy VOD
INSERT INTO vod (title, url, year, description, category_id) VALUES
('Szybcy i Wściekli 9', 'http://example.com/vod/f9.mp4', 2021, 'Kolejna część przygód Dominica Toretta i jego ekipy.', 1),
('Kevin sam w domu', 'http://example.com/vod/kevin.mp4', 1990, 'Świąteczna komedia o chłopcu pozostawionym samemu w domu.', 2),
('Titanic', 'http://example.com/vod/titanic.mp4', 1997, 'Epicka opowieść o miłości na tle katastrofy statku.', 3);

-- Przypisania VOD do pakietów
INSERT INTO vod_packages (vod_id, package_id) VALUES
(1, 3), (2, 4), (3, 4);

-- Kategorie seriali
INSERT INTO series_categories (name) VALUES
('Thriller'), ('Sci-Fi'), ('Fantasy');

-- Seriale
INSERT INTO series (title, url, seasons, description, category_id) VALUES
('Breaking Bad', 'http://example.com/series/breakingbad/index.m3u8', 5, 'Historia nauczyciela chemii, który staje się producentem metamfetaminy.', 1),
('Stranger Things', 'http://example.com/series/strangerthings/index.m3u8', 4, 'Tajemnicze wydarzenia w małym miasteczku Hawkins.', 2),
('Gra o Tron', 'http://example.com/series/got/index.m3u8', 8, 'Epicka walka o władzę nad Siedmioma Królestwami.', 3);

-- Przypisania seriali do pakietów
INSERT INTO series_packages (series_id, package_id) VALUES
(1, 5), (2, 5), (3, 5);

-- Pakiety przypisane użytkownikowi test
INSERT INTO user_packages (user_id, package_id) VALUES
(2, 1), (2, 3), (2, 5);

-- Ustawienia domyślne
INSERT INTO settings (name, value) VALUES
('max_epg_requests_per_minute', '5'),
('max_list_requests_per_minute', '5'),
('allow_import_categories_from_m3u', '1'),
('prefix_imported_categories', ''),
('default_max_connections', '2');
