-- Image Uploader SQLite Schema
-- Migration von JSON zu SQLite für bessere Performance

-- Groups Tabelle für Upload-Gruppen
CREATE TABLE IF NOT EXISTS groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id TEXT UNIQUE NOT NULL,
    year INTEGER NOT NULL,
    title TEXT NOT NULL,
    description TEXT,
    name TEXT,
    upload_date DATETIME NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Images Tabelle für einzelne Bilder  
CREATE TABLE IF NOT EXISTS images (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    group_id TEXT NOT NULL,
    file_name TEXT NOT NULL,
    original_name TEXT NOT NULL,
    file_path TEXT NOT NULL,
    upload_order INTEGER NOT NULL,
    file_size INTEGER,
    mime_type TEXT,
    preview_path TEXT,  -- Path to preview/thumbnail image (added in migration 003)
    image_description TEXT,  -- Optional description for each image (added in migration 004)
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (group_id) REFERENCES groups(group_id) ON DELETE CASCADE
);

-- Indizes für bessere Performance
CREATE INDEX IF NOT EXISTS idx_groups_group_id ON groups(group_id);

CREATE INDEX IF NOT EXISTS idx_groups_year ON groups(year);

CREATE INDEX IF NOT EXISTS idx_groups_upload_date ON groups(upload_date);

CREATE INDEX IF NOT EXISTS idx_images_group_id ON images(group_id);

CREATE INDEX IF NOT EXISTS idx_images_upload_order ON images(upload_order);

-- Trigger für updated_at
CREATE TRIGGER IF NOT EXISTS update_groups_timestamp 
AFTER UPDATE ON groups
FOR EACH ROW
BEGIN
    UPDATE groups SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
END;