-- Database schema for Sok Piseth Portfolio
CREATE DATABASE IF NOT EXISTS sokpiseth_portfolio CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE sokpiseth_portfolio;

-- Portfolio items table
CREATE TABLE IF NOT EXISTS portfolio_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    category ENUM('PORTRAIT', 'ABSTRACT', 'WEDDING', 'NATURE', 'TVC', 'FILM', 'MUSIC_VIDEO') NOT NULL,
    image TEXT,
    video_url TEXT,
    thumbnail TEXT,
    type ENUM('image', 'video') NOT NULL DEFAULT 'image',
    aspect_ratio DECIMAL(5,2) DEFAULT 0.75,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_category (category),
    INDEX idx_type (type),
    INDEX idx_sort_order (sort_order),
    INDEX idx_created_at (created_at)
);

-- Insert sample data
INSERT INTO portfolio_items (title, category, image, type, aspect_ratio, sort_order) VALUES
('Portrait Photography', 'PORTRAIT', '/blonde-woman-portrait-beauty.jpg', 'image', 0.75, 1),
('Artistic Makeup', 'ABSTRACT', '/artistic-makeup-wet-hair-portrait.jpg', 'image', 0.75, 2),
('Wedding Photography', 'WEDDING', '/couple-dancing-tango-romantic.jpg', 'image', 0.75, 3),
('Nature Landscape', 'NATURE', '/forest-path-sunlight-trees.jpg', 'image', 1.33, 4),
('Commercial Video', 'TVC', '/professional-photographer-with-camera-in-dark-stud.jpg', 'video', 1.77, 5),
('Film Production', 'FILM', '/underwater-bride-dress-flowing.jpg', 'video', 1.77, 6),
('Music Video', 'MUSIC_VIDEO', '/dancer-jumping-red-smoke-artistic.jpg', 'video', 1.77, 7),
('Avant-garde Portrait', 'ABSTRACT', '/avant-garde-makeup-white-flowers.jpg', 'image', 0.75, 8);

-- Update video items with YouTube URLs
UPDATE portfolio_items SET video_url = 'https://www.youtube.com/watch?v=dQw4w9WgXcQ' WHERE id = 5;
UPDATE portfolio_items SET video_url = 'https://www.youtube.com/watch?v=jNQXAC9IVRw' WHERE id = 6;
UPDATE portfolio_items SET video_url = 'https://www.youtube.com/watch?v=9bZkp7q19f0' WHERE id = 7;

-- Site Settings Table
CREATE TABLE IF NOT EXISTS site_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_setting_key (setting_key)
);

-- Layout Settings Table
CREATE TABLE IF NOT EXISTS layout_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    layout_name VARCHAR(50) NOT NULL,
    layout_config JSON,
    is_active BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_layout_name (layout_name),
    INDEX idx_is_active (is_active)
);

-- Insert default site settings
INSERT INTO site_settings (setting_key, setting_value) VALUES
('site_title', '"Sok Piseth Portfolio"'),
('site_description', '"Professional Photography & Videography Portfolio"'),
('site_keywords', '"photography, videography, portfolio, sok piseth"'),
('contact_email', '"contact@sokpisethdop.com"'),
('contact_phone', '"+855 12 345 678"'),
('social_facebook', '"https://facebook.com/sokpisethdop"'),
('social_instagram', '"https://instagram.com/sokpisethdop"'),
('social_youtube', '"https://youtube.com/sokpisethdop"'),
('layout_type', '"masonry"'),
('items_per_page', '12'),
('enable_animations', 'true')
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);

-- Insert default layout settings
INSERT INTO layout_settings (layout_name, layout_config, is_active) VALUES
('masonry', '{"columns": 4, "gap": 4, "responsive": true}', true),
('grid', '{"columns": 3, "gap": 6, "responsive": true}', false),
('list', '{"columns": 1, "gap": 8, "responsive": false}', false)
ON DUPLICATE KEY UPDATE layout_config = VALUES(layout_config);
