

-- =========================================================
-- install/schema.sql
-- =========================================================
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
);


-- =========================================================
-- install/seed.sql
-- =========================================================
USE vault_display;

INSERT INTO screen_groups (group_key, name, type, mode, canvas_width, canvas_height)
VALUES ('matrix-wall', 'Front Four-Screen Matrix', 'matrix', 'individual', 3840, 2160)
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO playlists (id, name, description, type, is_active) VALUES
(1, 'Matrix Screen 1 - Pricing', 'Default pricing board for top-left matrix screen.', 'screen', 1),
(2, 'Matrix Screen 2 - Portfolio', 'Default portfolio board for top-right matrix screen.', 'screen', 1),
(3, 'Matrix Screen 3 - Signage and Branding', 'Default signage/branding board for bottom-left matrix screen.', 'screen', 1),
(4, 'Matrix Screen 4 - Offers and QR', 'Default offers/QR board for bottom-right matrix screen.', 'screen', 1),
(5, 'Counter Rolling Ads', 'Counter-facing customer advert reel.', 'screen', 1),
(6, 'Shop TV Rolling Ads', 'General shop TV advert reel.', 'screen', 1),
(7, 'Window Rolling Ads', 'Window-facing high-contrast advert reel.', 'screen', 1),
(8, 'Full Matrix Campaign', 'Full 3840x2160 wall campaign playlist.', 'matrix', 1),
(9, 'Emergency Notice', 'Emergency override playlist.', 'emergency', 1)
ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description), type = VALUES(type), is_active = VALUES(is_active);

SET @matrix_group_id = (SELECT id FROM screen_groups WHERE group_key = 'matrix-wall' LIMIT 1);

INSERT INTO screens
(screen_key, name, type, role, group_id, position_x, position_y, width, height, orientation, assigned_playlist_id)
VALUES
('matrix-1', 'Matrix Top Left', 'matrix_member', 'pricing', @matrix_group_id, 0, 0, 1920, 1080, 'landscape', 1),
('matrix-2', 'Matrix Top Right', 'matrix_member', 'portfolio', @matrix_group_id, 1920, 0, 1920, 1080, 'landscape', 2),
('matrix-3', 'Matrix Bottom Left', 'matrix_member', 'signage_branding', @matrix_group_id, 0, 1080, 1920, 1080, 'landscape', 3),
('matrix-4', 'Matrix Bottom Right', 'matrix_member', 'offers_qr', @matrix_group_id, 1920, 1080, 1920, 1080, 'landscape', 4),
('counter', 'Counter Customer Screen', 'standalone', 'rolling_ads_counter', NULL, 0, 0, 1920, 1080, 'landscape', 5),
('tv', 'Shop TV', 'standalone', 'rolling_ads_tv', NULL, 0, 0, 1920, 1080, 'landscape', 6),
('window', 'Window Screen TV', 'standalone', 'rolling_ads_window', NULL, 0, 0, 1920, 1080, 'landscape', 7)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
type = VALUES(type),
role = VALUES(role),
group_id = VALUES(group_id),
position_x = VALUES(position_x),
position_y = VALUES(position_y),
width = VALUES(width),
height = VALUES(height),
orientation = VALUES(orientation),
assigned_playlist_id = VALUES(assigned_playlist_id);


-- =========================================================
-- install/demo-content.sql
-- =========================================================
USE vault_display;

INSERT INTO content_items
(title, content_type, html_content, is_active)
VALUES
(
'Pricing Board',
'html',
'<div class="pricing-board"><h1>Next Theme Pricing</h1><div class="line">Websites from £495</div><div class="line">Branding from £150</div><div class="line">Magnetic Signs from £95</div><div class="line">Trade Packages Available</div></div>',
1
),
(
'Portfolio Board',
'html',
'<div class="pricing-board"><h1>Recent Work</h1><div class="line">Trade Websites</div><div class="line">Vehicle Branding</div><div class="line">SEO Campaigns</div><div class="line">Local Business Packages</div></div>',
1
),
(
'Offers Board',
'html',
'<div class="pricing-board"><h1>Special Offers</h1><div class="line">Easy Pay Available</div><div class="line">Starter Package £1200</div><div class="line">Local Lead Builder £3500</div></div>',
1
);

INSERT INTO playlist_items
(playlist_id, content_id, sort_order, duration_seconds, is_active)
VALUES
(1, 1, 1, 15, 1),
(2, 2, 1, 15, 1),
(3, 2, 1, 15, 1),
(4, 3, 1, 15, 1),
(5, 3, 1, 15, 1),
(6, 2, 1, 15, 1),
(7, 3, 1, 15, 1);


-- =========================================================
-- install/step04-matrix-emergency.sql
-- =========================================================
USE vault_display;

INSERT INTO settings (setting_key, setting_value)
VALUES ('emergency_override', '0')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

INSERT INTO content_items (id, title, content_type, html_content, is_active)
VALUES
(
1001,
'Full Matrix Campaign Demo',
'html',
'<div class="pricing-board"><h1>Next Theme</h1><div class="line">Websites • Branding • Signage • Marketing</div><div class="line">Everything your trade business needs in one place</div></div>',
1
),
(
1002,
'Emergency Notice Demo',
'notice',
'<div class="pricing-board"><h1>Notice</h1><div class="line">Please speak to a member of staff</div><div class="line">Next Theme display override active</div></div>',
1
)
ON DUPLICATE KEY UPDATE
    title = VALUES(title),
    content_type = VALUES(content_type),
    html_content = VALUES(html_content),
    is_active = VALUES(is_active);

INSERT INTO playlist_items (playlist_id, content_id, sort_order, duration_seconds, is_active)
VALUES
(8, 1001, 1, 20, 1),
(9, 1002, 1, 20, 1)
ON DUPLICATE KEY UPDATE
    sort_order = VALUES(sort_order),
    duration_seconds = VALUES(duration_seconds),
    is_active = VALUES(is_active);


-- =========================================================
-- install/step05-scheduling.sql
-- =========================================================
USE vault_display;

INSERT INTO playlists (id, name, description, type, is_active)
VALUES
(10, 'Out Of Hours Display', 'Default closed/out-of-hours display playlist.', 'campaign', 1)
ON DUPLICATE KEY UPDATE name = VALUES(name), description = VALUES(description), type = VALUES(type), is_active = VALUES(is_active);

INSERT INTO content_items
(title, content_type, html_content, layout_template, is_active)
SELECT
'Out Of Hours Notice',
'html',
'<div class="pricing-board matrix-emergency"><h1>Next Theme</h1><div class="line">We are currently closed</div><div class="line">Scan online for websites, branding, signage and marketing</div><div class="line">nexttheme.co.uk</div></div>',
'notice',
1
WHERE NOT EXISTS (
    SELECT 1 FROM content_items WHERE title = 'Out Of Hours Notice' LIMIT 1
);

SET @out_hours_content_id = (SELECT id FROM content_items WHERE title = 'Out Of Hours Notice' ORDER BY id ASC LIMIT 1);

INSERT INTO playlist_items
(playlist_id, content_id, sort_order, duration_seconds, priority, is_active)
SELECT 10, @out_hours_content_id, 1, 20, 0, 1
WHERE @out_hours_content_id IS NOT NULL
AND NOT EXISTS (
    SELECT 1 FROM playlist_items WHERE playlist_id = 10 AND content_id = @out_hours_content_id LIMIT 1
);

INSERT INTO settings (setting_key, setting_value)
VALUES
('out_of_hours_playlist_id', '10'),
('opening_hours_json', '{"mon":{"open":"09:00","close":"17:00","closed":false},"tue":{"open":"09:00","close":"17:00","closed":false},"wed":{"open":"09:00","close":"17:00","closed":false},"thu":{"open":"09:00","close":"17:00","closed":false},"fri":{"open":"09:00","close":"17:00","closed":false},"sat":{"open":"09:00","close":"14:00","closed":false},"sun":{"open":"09:00","close":"17:00","closed":true}}')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);
