-- Script SQL per creare le tabelle necessarie per il sistema news-finder
-- Eseguire questo script per creare il database e le tabelle

-- Crea il database se non esiste
CREATE DATABASE IF NOT EXISTS news_finder CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Usa il database
USE news_finder;

-- Tabella per archiviare le descrizioni degli articoli già processati
CREATE TABLE IF NOT EXISTS articles_archive (
    id INT AUTO_INCREMENT PRIMARY KEY,
    description TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_description (description(255))
) ENGINE=InnoDB;

-- Tabella per gli articoli da processare (output del primo script)
CREATE TABLE IF NOT EXISTS articles_todo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    url VARCHAR(500) NOT NULL,
    title VARCHAR(500) NOT NULL,
    description TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Tabella per gli articoli completati (output del secondo script)
CREATE TABLE IF NOT EXISTS articles_ready (
    id INT AUTO_INCREMENT PRIMARY KEY,
    language VARCHAR(5) NOT NULL,
    title VARCHAR(500) NOT NULL,
    content LONGTEXT NOT NULL,
    img1 VARCHAR(500) NULL,
    img2 VARCHAR(500) NULL,
    img3 VARCHAR(500) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_language (language),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB;

-- Inserisci alcuni dati di esempio nella tabella articles_archive
INSERT INTO articles_archive (description) VALUES 
('AI music generation tool creates original compositions'),
('Machine learning algorithm analyzes musical patterns'),
('Neural network generates personalized playlists'),
('Deep learning model composes classical music'),
('AI-powered music recommendation system improves user experience');
