SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE IF NOT EXISTS categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    slug VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    parent_id INT NULL DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS brands (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    slug VARCHAR(255) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS feeds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    url VARCHAR(512) NOT NULL,
    format ENUM('xml', 'csv', 'json') NOT NULL,
    last_update DATETIME,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    feed_id INT,
    product_id VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    currency VARCHAR(3) DEFAULT 'EUR',
    availability ENUM('in_stock', 'out_of_stock', 'preorder') DEFAULT 'in_stock',
    category_id INT NULL,
    brand_id INT NULL,
    image_url VARCHAR(512),
    product_url VARCHAR(512),
    ean13 VARCHAR(20) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (feed_id) REFERENCES feeds(id) ON DELETE CASCADE,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL,
    INDEX idx_product_id (product_id),
    INDEX idx_feed_id (feed_id)
);

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    role ENUM('admin', 'editor') DEFAULT 'editor',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Migrar categorías
INSERT IGNORE INTO categories (name)
SELECT DISTINCT category_id FROM products WHERE category_id IS NOT NULL AND category_id != '';

-- Migrar marcas
INSERT IGNORE INTO brands (name)
SELECT DISTINCT brand_id FROM products WHERE brand_id IS NOT NULL AND brand_id != '';

-- Actualizar productos
UPDATE products p
JOIN categories c ON p.category_id = c.id
SET p.category_id = c.id;

UPDATE products p
JOIN brands b ON p.brand_id = b.id
SET p.brand_id = b.id;


ALTER TABLE products ADD CONSTRAINT fk_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL;
ALTER TABLE products ADD CONSTRAINT fk_brand FOREIGN KEY (brand_id) REFERENCES brands(id) ON DELETE SET NULL;

-- Ejemplo de categorías padre/hija
INSERT IGNORE INTO categories (name, slug, parent_id) VALUES
('Electrónica', 'electronica', NULL),
('Móviles', 'moviles', (SELECT id FROM categories WHERE name='Electrónica')),
('Portátiles', 'portatiles', (SELECT id FROM categories WHERE name='Electrónica')),
('Hogar', 'hogar', NULL),
('Bellas Artes', 'bellas-artes', NULL),
('Cocina', 'cocina', (SELECT id FROM categories WHERE name='Hogar')),
('Decoración', 'decoracion', (SELECT id FROM categories WHERE name='Hogar'));

SET FOREIGN_KEY_CHECKS=1;