-- YouTube Recipe Publisher Database Schema
-- MySQL/MariaDB

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('admin', 'user') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- YouTube Channels table
CREATE TABLE IF NOT EXISTS youtube_channels (
    id INT AUTO_INCREMENT PRIMARY KEY,
    channel_id VARCHAR(255) UNIQUE NOT NULL,
    channel_name VARCHAR(255) NOT NULL,
    channel_url VARCHAR(500) NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    last_check TIMESTAMP NULL,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Videos table
CREATE TABLE IF NOT EXISTS videos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_id VARCHAR(255) UNIQUE NOT NULL,
    channel_id INT NOT NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT,
    thumbnail_url VARCHAR(500),
    video_url VARCHAR(500) NOT NULL,
    published_at TIMESTAMP NOT NULL,
    duration VARCHAR(50),
    transcript TEXT,
    ingredients TEXT,
    status ENUM('pending', 'processing', 'completed', 'failed', 'published') DEFAULT 'pending',
    error_message TEXT,
    discovered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP NULL,
    FOREIGN KEY (channel_id) REFERENCES youtube_channels(id) ON DELETE CASCADE,
    INDEX idx_status (status),
    INDEX idx_published_at (published_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Generated Recipes table
CREATE TABLE IF NOT EXISTS generated_recipes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    video_id INT UNIQUE NOT NULL,
    gpt_model VARCHAR(100),
    gpt_prompt TEXT,
    recipe_title VARCHAR(500),
    recipe_content LONGTEXT,
    recipe_data JSON, -- Structured data: prep_time, cook_time, servings, calories, etc.
    categories JSON, -- Array of suggested categories
    wp_post_id BIGINT NULL,
    wp_post_status VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configuration table
CREATE TABLE IF NOT EXISTS configuration (
    id INT AUTO_INCREMENT PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value TEXT,
    config_type ENUM('string', 'text', 'json', 'boolean', 'number') DEFAULT 'string',
    description TEXT,
    is_sensitive BOOLEAN DEFAULT FALSE, -- For API keys
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    updated_by INT,
    FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Logs table
CREATE TABLE IF NOT EXISTS logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    log_type ENUM('info', 'warning', 'error', 'success') DEFAULT 'info',
    module VARCHAR(100), -- youtube, openai, wordpress, processor, etc.
    message TEXT NOT NULL,
    details JSON,
    video_id INT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE SET NULL,
    INDEX idx_log_type (log_type),
    INDEX idx_module (module),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert default admin user (password: admin123 - CHANGE THIS!)
INSERT INTO users (username, email, password_hash, role) VALUES
('admin', 'admin@example.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin');

-- Insert default configuration values
INSERT INTO configuration (config_key, config_value, config_type, description, is_sensitive) VALUES
-- YouTube API
('youtube_api_key', '', 'string', 'YouTube Data API v3 Key', TRUE),

-- OpenAI API
('openai_api_key', '', 'string', 'OpenAI API Key', TRUE),
('openai_model', 'gpt-4-turbo-preview', 'string', 'OpenAI Model to use (e.g., gpt-4, gpt-4-turbo-preview, gpt-4.5)', FALSE),
('openai_temperature', '0.7', 'number', 'OpenAI Temperature (0-1)', FALSE),

-- WordPress Configuration
('wordpress_url', '', 'string', 'WordPress Site URL (e.g., https://monsite.com)', FALSE),
('wordpress_username', '', 'string', 'WordPress Username', FALSE),
('wordpress_app_password', '', 'string', 'WordPress Application Password', TRUE),
('wordpress_post_status', 'draft', 'string', 'Default post status: draft or publish', FALSE),

-- Recipe Format Configuration
('recipe_structure', '["Titre", "Introduction", "Temps de préparation", "Temps de cuisson", "Nombre de portions", "Ingrédients", "Instructions", "Conseils et astuces", "Informations nutritionnelles"]', 'json', 'Order of recipe sections', FALSE),

-- GPT Prompt Template
('gpt_prompt_template', 'Tu es un blogueur de recettes de cuisine donc utilise le "je" pour la décrire et expliquer.\n\n⚠️ IMPORTANT : Tu dois retourner UNIQUEMENT du JSON valide, RIEN d\'autre. Pas de texte avant ou après le JSON.\n\nConsignes STRICTES :\n1. N\'utilise AUCUN émoji, AUCUN #, AUCUN markdown (pas de **, *, ###, etc.)\n2. Retourne UNIQUEMENT du JSON valide selon le format ci-dessous\n3. Pour les instructions : chaque étape = UN OBJET avec "title" et "content"\n4. Ne répète PAS les ingrédients dans les instructions\n5. Les conseils vont dans le champ "tips" séparé, PAS dans les instructions\n\nSTRUCTURE DES INSTRUCTIONS :\n- Chaque instruction est un objet : {"title": "Nom de l\'étape", "content": "Description en "je""}\n- NE PAS mettre ### ou ** ou autres symboles\n- NE PAS répéter les ingrédients\n- NE PAS mettre les tips dans les instructions\n\nEXEMPLE CONCRET de format attendu :\n{\n  "title": "Pâtes au poulet teriyaki",\n  "introduction": "Aujourd\'hui je te propose des pâtes au poulet teriyaki, une recette rapide et savoureuse.",\n  "prep_time": 10,\n  "cook_time": 15,\n  "servings": 2,\n  "calories": 650,\n  "ingredients": [\n    "2 blancs de poulet",\n    "175g de pâtes"\n  ],\n  "instructions": [\n    {"title": "Préparer le poulet", "content": "Je coupe les blancs de poulet en fines lamelles. Je les assaisonne légèrement avec du sel et du poivre."},\n    {"title": "Cuire les pâtes", "content": "Je porte une casserole d\'eau salée à ébullition. J\'ajoute les pâtes et je les cuis selon les indications du paquet."},\n    {"title": "Préparer la sauce", "content": "Dans un bol, je mélange la sauce soja, le mirin et le sucre. Je fais chauffer doucement jusqu\'à ce que le mélange épaississe."},\n    {"title": "Saisir le poulet", "content": "Je fais chauffer l\'huile dans une poêle. J\'ajoute le poulet et je le fais dorer de tous les côtés."},\n    {"title": "Assembler le plat", "content": "J\'ajoute les pâtes égouttées dans la poêle avec le poulet. Je verse la sauce et je mélange bien pour enrober les pâtes."}\n  ],\n  "tips": "Je veille à ne pas trop cuire le poulet pour qu\'il reste tendre. Si la sauce épaissit trop, j\'ajoute un peu d\'eau de cuisson des pâtes.",\n  "categories": ["Pâtes", "Poulet", "Cuisine japonaise"],\n  "difficulty": "Facile"\n}\n\nDonnées source :\nTitre de la vidéo YouTube : {video_title}\nTranscription: {transcript}\nIngrédients (depuis la description): {ingredients}\n\nExemple de recette à suivre comme modèle de style :\n{recipe_example}\n\nRETOURNE MAINTENANT LE JSON POUR CETTE RECETTE (JSON uniquement, rien d\'autre) :', 'text', 'GPT Prompt Template. Use {transcript}, {ingredients}, {video_title}, {recipe_example} as placeholders', FALSE),

-- Cron Configuration
('cron_enabled', 'true', 'boolean', 'Enable automatic video checking', FALSE),
('cron_frequency', '3600', 'number', 'Check frequency in seconds (3600 = 1 hour)', FALSE),

-- Processing Options
('auto_process_videos', 'true', 'boolean', 'Automatically process new videos', FALSE),
('auto_publish_to_wp', 'true', 'boolean', 'Automatically publish to WordPress', FALSE),

-- Rich Snippets
('enable_rich_snippets', 'true', 'boolean', 'Enable Schema.org Recipe rich snippets', FALSE);
