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

CREATE TABLE IF NOT EXISTS screen_groups (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    group_key VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    type ENUM('matrix','standalone_group') NOT NULL DEFAULT 'matrix',
    mode ENUM('individual','matrix','campaign','emergency') NOT NULL DEFAULT 'individual',
    canvas_width INT UNSIGNED NOT NULL DEFAULT 3840,
    canvas_height INT UNSIGNED NOT NULL DEFAULT 2160,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS playlists (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    description TEXT NULL,
    type ENUM('screen','matrix','emergency','campaign') NOT NULL DEFAULT 'screen',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS content_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content_type ENUM('image','video','html','pricing','portfolio','package','offer','notice','qr') NOT NULL,
    file_path VARCHAR(500) NULL,
    html_content MEDIUMTEXT NULL,
    background_color VARCHAR(20) NULL,
    text_color VARCHAR(20) NULL,
    layout_template VARCHAR(100) NULL,
    cta_text VARCHAR(200) NULL,
    cta_url VARCHAR(500) NULL,
    qr_code_url VARCHAR(500) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS playlist_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    playlist_id INT UNSIGNED NOT NULL,
    content_id INT UNSIGNED NOT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    duration_seconds INT UNSIGNED NOT NULL DEFAULT 10,
    start_date DATE NULL,
    end_date DATE NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    days_of_week VARCHAR(50) NULL,
    priority INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_playlist_items_playlist FOREIGN KEY (playlist_id) REFERENCES playlists(id) ON DELETE CASCADE,
    CONSTRAINT fk_playlist_items_content FOREIGN KEY (content_id) REFERENCES content_items(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS screens (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    screen_key VARCHAR(100) NOT NULL UNIQUE,
    name VARCHAR(150) NOT NULL,
    type ENUM('matrix_member','standalone') NOT NULL DEFAULT 'standalone',
    role VARCHAR(100) NOT NULL,
    group_id INT UNSIGNED NULL,
    position_x INT NOT NULL DEFAULT 0,
    position_y INT NOT NULL DEFAULT 0,
    width INT UNSIGNED NOT NULL DEFAULT 1920,
    height INT UNSIGNED NOT NULL DEFAULT 1080,
    orientation ENUM('landscape','portrait') NOT NULL DEFAULT 'landscape',
    assigned_playlist_id INT UNSIGNED NULL,
    status ENUM('unknown','online','offline') NOT NULL DEFAULT 'unknown',
    last_seen_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_screens_group FOREIGN KEY (group_id) REFERENCES screen_groups(id) ON DELETE SET NULL,
    CONSTRAINT fk_screens_playlist FOREIGN KEY (assigned_playlist_id) REFERENCES playlists(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS screen_status (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    screen_key VARCHAR(100) NOT NULL UNIQUE,
    current_playlist_id INT UNSIGNED NULL,
    current_item_id INT UNSIGNED NULL,
    current_mode VARCHAR(50) NULL,
    browser_width INT UNSIGNED NULL,
    browser_height INT UNSIGNED NULL,
    reported_resolution VARCHAR(100) NULL,
    is_fullscreen TINYINT(1) NOT NULL DEFAULT 0,
    last_ping_at DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS schedules (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    target_type ENUM('playlist','content','screen','group') NOT NULL,
    target_id INT UNSIGNED NOT NULL,
    schedule_type ENUM('always','date_range','time_of_day','day_of_week','priority_override') NOT NULL DEFAULT 'always',
    start_date DATE NULL,
    end_date DATE NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    days_of_week VARCHAR(50) NULL,
    priority INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS settings (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(150) NOT NULL UNIQUE,
    setting_value TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
