-- 使用者表
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100) UNIQUE,
    role ENUM('admin', 'editor', 'viewer') DEFAULT 'viewer',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- YouTube影片表
CREATE TABLE youtube_videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    video_id VARCHAR(50) NOT NULL,
    thumbnail_url VARCHAR(500),
    duration VARCHAR(20),
    views VARCHAR(50),
    category VARCHAR(100),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- Instagram相片表
CREATE TABLE instagram_photos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    description TEXT,
    image_url VARCHAR(500) NOT NULL,
    instagram_url VARCHAR(500),
    likes VARCHAR(50),
    hashtags TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_by INT,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
);

-- SEO設定表
CREATE TABLE seo_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    page_name VARCHAR(100) UNIQUE NOT NULL,
    meta_title VARCHAR(200),
    meta_description TEXT,
    meta_keywords TEXT,
    canonical_url VARCHAR(500),
    og_title VARCHAR(200),
    og_description TEXT,
    og_image VARCHAR(500),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- 活動記錄表
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    table_name VARCHAR(50),
    record_id INT,
    details TEXT,
    ip_address VARCHAR(50),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
);

-- 插入預設管理員使用者
-- 密碼: admin123 (使用SHA-256加密)
INSERT INTO users (username, password, email, role) VALUES
('admin', '8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918', 'admin@industrial-media.com', 'admin');

-- 插入預設SEO設定
INSERT INTO seo_settings (page_name, meta_title, meta_description, meta_keywords) VALUES
('home', '工業風多媒體展示平台', '工業風格多媒體展示平台，整合YouTube影片與Instagram相片，提供獨特的工業美學體驗。', '工業風, YouTube, Instagram, 多媒體, 影片, 相片, 工業設計');

-- 插入範例YouTube影片
INSERT INTO youtube_videos (title, description, video_id, thumbnail_url, duration, views, category, created_by) VALUES
('工業機械運作展示', '展示現代工業機械的精密運作過程', 'dQw4w9WgXcQ', 'https://img.youtube.com/vi/dQw4w9WgXcQ/maxresdefault.jpg', '4:20', '125K', '機械', 1),
('工廠自動化生產線', '全自動化工廠生產線的運作實錄', '9bZkp7q19f0', 'https://img.youtube.com/vi/9bZkp7q19f0/maxresdefault.jpg', '6:15', '89K', '自動化', 1),
('工業設計原理', '工業產品的設計流程與原理', 'JGwWNGJdvx8', 'https://img.youtube.com/vi/JGwWNGJdvx8/maxresdefault.jpg', '8:30', '210K', '設計', 1);

-- 插入範例Instagram相片
INSERT INTO instagram_photos (title, description, image_url, likes, hashtags, created_by) VALUES
('工業風咖啡廳', '裸露管線與混凝土的工業風設計', 'https://images.unsplash.com/photo-1513475382585-d06e58bcb0e0?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&q=80', '1.2K', '#工業風 #咖啡廳 #設計', 1),
('機械細節', '工業機械的精密細節特寫', 'https://images.unsplash.com/photo-1581094794329-c8112a89af12?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&q=80', '2.4K', '#機械 #細節 #工業攝影', 1),
('工廠內部', '現代化工廠內部結構', 'https://images.unsplash.com/photo-1581094794329-c8112a89af12?ixlib=rb-4.0.3&auto=format&fit=crop&w=800&q=80', '3.1K', '#工廠 #內部 #結構', 1);

-- 建立索引以提高查詢效能
CREATE INDEX idx_videos_active ON youtube_videos(is_active);
CREATE INDEX idx_photos_active ON instagram_photos(is_active);
CREATE INDEX idx_videos_category ON youtube_videos(category);
CREATE INDEX idx_logs_user ON activity_logs(user_id);
CREATE INDEX idx_logs_created ON activity_logs(created_at);