-- ============================================================
-- PurrCore — Schema COMPLETO v2.0
-- MySQL 8.0 / MariaDB 10.6+
-- ============================================================


-- Catálogo
CREATE TABLE IF NOT EXISTS categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    parent_id INT NULL,
    name VARCHAR(150) NOT NULL,
    slug VARCHAR(150) UNIQUE NOT NULL,
    description TEXT,
    image VARCHAR(255),
    sort_order INT DEFAULT 0,
    active TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    description TEXT,
    short_description VARCHAR(500),
    price DECIMAL(10,2) NOT NULL,
    compare_price DECIMAL(10,2) NULL,
    cost_price DECIMAL(10,2) NULL,
    sku VARCHAR(100) UNIQUE,
    weight DECIMAL(8,3) DEFAULT 0,
    stock_qty INT DEFAULT 0,
    low_stock_threshold INT DEFAULT 5,
    stock_status ENUM('in_stock','out_of_stock','backorder') DEFAULT 'in_stock',
    has_variants TINYINT DEFAULT 0,
    is_subscription TINYINT DEFAULT 0,
    active TINYINT DEFAULT 1,
    featured TINYINT DEFAULT 0,
    meta_title VARCHAR(255),
    meta_description VARCHAR(500),
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_images (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    filename VARCHAR(255) NOT NULL,
    alt_text VARCHAR(255),
    sort_order INT DEFAULT 0,
    is_primary TINYINT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attributes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS attribute_values (
    id INT PRIMARY KEY AUTO_INCREMENT,
    attribute_id INT NOT NULL,
    value VARCHAR(100) NOT NULL,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (attribute_id) REFERENCES attributes(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS product_variants (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    sku VARCHAR(100) UNIQUE,
    price DECIMAL(10,2) NOT NULL,
    compare_price DECIMAL(10,2) NULL,
    stock_qty INT DEFAULT 0,
    stock_status ENUM('in_stock','out_of_stock','backorder') DEFAULT 'in_stock',
    weight DECIMAL(8,3) DEFAULT 0,
    image VARCHAR(255),
    active TINYINT DEFAULT 1,
    sort_order INT DEFAULT 0,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS variant_attributes (
    variant_id INT NOT NULL,
    attribute_value_id INT NOT NULL,
    PRIMARY KEY (variant_id, attribute_value_id),
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE,
    FOREIGN KEY (attribute_value_id) REFERENCES attribute_values(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS volume_prices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT,
    variant_id INT NULL,
    min_qty INT NOT NULL,
    max_qty INT NULL,
    price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Clientes
CREATE TABLE IF NOT EXISTS customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    phone VARCHAR(30),
    role ENUM('customer','admin') DEFAULT 'customer',
    points_balance INT DEFAULT 0,
    stripe_customer_id VARCHAR(100) NULL,
    email_verified TINYINT DEFAULT 0,
    verify_token VARCHAR(128) NULL,
    reset_token VARCHAR(128) NULL,
    reset_expires DATETIME NULL,
    active TINYINT DEFAULT 1,
    last_login DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customer_addresses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    type ENUM('billing','shipping') DEFAULT 'shipping',
    is_default TINYINT DEFAULT 0,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    company VARCHAR(150),
    address_1 VARCHAR(255),
    address_2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postcode VARCHAR(20),
    country VARCHAR(5) DEFAULT 'ES',
    phone VARCHAR(30),
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Carrito
CREATE TABLE IF NOT EXISTS carts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(128) NOT NULL,
    customer_id INT NULL,
    coupon_id INT NULL,
    points_to_redeem INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS cart_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    cart_id INT NOT NULL,
    product_id INT NOT NULL,
    variant_id INT NULL,
    qty INT NOT NULL DEFAULT 1,
    subscription_plan_id INT NULL,
    FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Pedidos
CREATE TABLE IF NOT EXISTS orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_number VARCHAR(30) UNIQUE NOT NULL,
    external_order_id VARCHAR(100) NULL,
    external_platform ENUM('amazon','tiktok','manual') NULL,
    customer_id INT NULL,
    customer_email VARCHAR(255) NOT NULL,
    status ENUM('pending','processing','paid','shipped','delivered','cancelled','refunded') DEFAULT 'pending',
    subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    points_discount DECIMAL(10,2) DEFAULT 0,
    shipping_amount DECIMAL(10,2) DEFAULT 0,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) NOT NULL DEFAULT 0,
    coupon_id INT NULL,
    coupon_code VARCHAR(50) NULL,
    points_used INT DEFAULT 0,
    points_earned INT DEFAULT 0,
    stripe_payment_intent VARCHAR(255) NULL,
    stripe_charge_id VARCHAR(255) NULL,
    payment_status ENUM('pending','paid','failed','refunded') DEFAULT 'pending',
    payment_method VARCHAR(50) DEFAULT 'stripe',
    shipping_method VARCHAR(100),
    tracking_number VARCHAR(100) NULL,
    notes TEXT NULL,
    admin_notes TEXT NULL,
    ship_first_name VARCHAR(100),
    ship_last_name VARCHAR(100),
    ship_address_1 VARCHAR(255),
    ship_address_2 VARCHAR(255),
    ship_city VARCHAR(100),
    ship_state VARCHAR(100),
    ship_postcode VARCHAR(20),
    ship_country VARCHAR(5),
    ship_phone VARCHAR(30),
    bill_first_name VARCHAR(100),
    bill_last_name VARCHAR(100),
    bill_nif VARCHAR(20) NULL,
    bill_company VARCHAR(150) NULL,
    bill_address_1 VARCHAR(255),
    bill_city VARCHAR(100),
    bill_postcode VARCHAR(20),
    bill_country VARCHAR(5),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON UPDATE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NULL,
    variant_id INT NULL,
    product_name VARCHAR(255),
    variant_label VARCHAR(255),
    sku VARCHAR(100),
    qty INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS order_history (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    status_from VARCHAR(50) NULL,
    status_to VARCHAR(50),
    note TEXT,
    notify_customer TINYINT DEFAULT 0,
    created_by VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Cupones
CREATE TABLE IF NOT EXISTS coupons (
    id INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(50) UNIQUE NOT NULL,
    description VARCHAR(255),
    type ENUM('percent','fixed','free_shipping') NOT NULL,
    value DECIMAL(10,2) NOT NULL DEFAULT 0,
    min_order DECIMAL(10,2) DEFAULT 0,
    max_discount DECIMAL(10,2) NULL,
    max_uses INT NULL,
    used_count INT DEFAULT 0,
    max_uses_per_customer INT NULL DEFAULT 1,
    combinable_with_points TINYINT DEFAULT 1,
    auto_generated TINYINT DEFAULT 0,
    customer_id INT NULL,
    product_id INT NULL,
    category_id INT NULL,
    starts_at DATETIME NULL,
    expires_at DATETIME NULL,
    active TINYINT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE SET NULL,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS coupon_uses (
    id INT PRIMARY KEY AUTO_INCREMENT,
    coupon_id INT NOT NULL,
    customer_id INT NULL,
    order_id INT NOT NULL,
    used_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (coupon_id) REFERENCES coupons(id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Puntos y fidelización
CREATE TABLE IF NOT EXISTS points_transactions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    order_id INT NULL,
    coupon_id INT NULL,
    type ENUM('earn','redeem','expire','admin_adjust','referral','review','coupon_generated') NOT NULL,
    points INT NOT NULL,
    balance_after INT NOT NULL,
    description VARCHAR(255),
    expires_at DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Suscripciones
CREATE TABLE IF NOT EXISTS subscription_plans (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    variant_id INT NULL,
    name VARCHAR(150),
    interval_unit ENUM('week','month','year') NOT NULL,
    interval_count INT DEFAULT 1,
    price DECIMAL(10,2) NOT NULL,
    discount_percent DECIMAL(5,2) DEFAULT 0,
    stripe_price_id VARCHAR(255),
    active TINYINT DEFAULT 1,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscriptions (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    plan_id INT NOT NULL,
    stripe_subscription_id VARCHAR(255) UNIQUE,
    status ENUM('active','paused','cancelled','past_due','incomplete') DEFAULT 'active',
    current_period_start DATE NULL,
    next_billing_date DATE NULL,
    shipping_address_id INT NULL,
    pause_until DATE NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    cancelled_at DATETIME NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (plan_id) REFERENCES subscription_plans(id),
    FOREIGN KEY (shipping_address_id) REFERENCES customer_addresses(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- FACTURAS
CREATE TABLE IF NOT EXISTS invoices (
    id INT PRIMARY KEY AUTO_INCREMENT,
    invoice_number VARCHAR(30) UNIQUE NOT NULL,
    type ENUM('invoice','refund','manual') NOT NULL DEFAULT 'invoice',
    order_id INT NULL,
    related_invoice_id INT NULL,
    customer_id INT NULL,
    customer_email VARCHAR(255),
    customer_name VARCHAR(200),
    customer_nif VARCHAR(20),
    customer_address VARCHAR(255),
    customer_city VARCHAR(100),
    customer_postcode VARCHAR(20),
    customer_country VARCHAR(5) DEFAULT 'ES',
    subtotal DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    shipping_amount DECIMAL(10,2) DEFAULT 0,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    total DECIMAL(10,2) DEFAULT 0,
    items_json TEXT,
    pdf_path VARCHAR(255) NULL,
    issued_at DATE,
    notes TEXT,
    status ENUM('draft','issued','cancelled') DEFAULT 'issued',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    FOREIGN KEY (related_invoice_id) REFERENCES invoices(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- NEWSLETTER
CREATE TABLE IF NOT EXISTS newsletter_subscribers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(150),
    token VARCHAR(64) UNIQUE NOT NULL,
    source VARCHAR(50) DEFAULT 'web',
    active TINYINT DEFAULT 1,
    confirmed TINYINT DEFAULT 0,
    confirmed_at DATETIME NULL,
    unsubscribed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS newsletter_campaigns (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject VARCHAR(255) NOT NULL,
    body_html TEXT,
    body_text TEXT,
    from_name VARCHAR(150),
    from_email VARCHAR(255),
    status ENUM('draft','scheduled','sending','sent','cancelled') DEFAULT 'draft',
    scheduled_at DATETIME NULL,
    sent_at DATETIME NULL,
    sent_count INT DEFAULT 0,
    failed_count INT DEFAULT 0,
    open_count INT DEFAULT 0,
    click_count INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS newsletter_sends (
    id INT PRIMARY KEY AUTO_INCREMENT,
    campaign_id INT NOT NULL,
    subscriber_id INT NOT NULL,
    status ENUM('sent','failed','bounced') DEFAULT 'sent',
    opened_at DATETIME NULL,
    clicked_at DATETIME NULL,
    error VARCHAR(255) NULL,
    sent_at DATETIME NULL,
    FOREIGN KEY (campaign_id) REFERENCES newsletter_campaigns(id) ON DELETE CASCADE,
    FOREIGN KEY (subscriber_id) REFERENCES newsletter_subscribers(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- RESEÑAS
CREATE TABLE IF NOT EXISTS reviews (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_id INT NOT NULL,
    customer_id INT NULL,
    author_name VARCHAR(100),
    author_email VARCHAR(255),
    rating TINYINT NOT NULL,
    title VARCHAR(255),
    body TEXT,
    verified_purchase TINYINT DEFAULT 0,
    source ENUM('site','amazon','tiktok','google','other') DEFAULT 'site',
    approved TINYINT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL,
    CHECK (rating BETWEEN 1 AND 5)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- COOKIES / GDPR
CREATE TABLE IF NOT EXISTS cookie_consents (
    id INT PRIMARY KEY AUTO_INCREMENT,
    session_id VARCHAR(128),
    customer_id INT NULL,
    analytics TINYINT DEFAULT 0,
    marketing TINYINT DEFAULT 0,
    preferences TINYINT DEFAULT 0,
    ip_address VARCHAR(45),
    user_agent VARCHAR(500),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- BACKUPS LOG
CREATE TABLE IF NOT EXISTS backup_log (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type ENUM('db','files','full') DEFAULT 'full',
    filename VARCHAR(255),
    size_bytes BIGINT DEFAULT 0,
    status ENUM('success','failed') DEFAULT 'success',
    error_message TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- CONFIGURACIÓN
CREATE TABLE IF NOT EXISTS settings (
    `key` VARCHAR(100) PRIMARY KEY,
    `value` TEXT,
    `type` VARCHAR(30) DEFAULT 'string',
    `group` VARCHAR(50) DEFAULT 'general',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- SEO Redirects (301/302) para gestión de URLs antiguas
CREATE TABLE IF NOT EXISTS seo_redirects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    from_url VARCHAR(500) NOT NULL,
    to_url VARCHAR(500) NOT NULL,
    code SMALLINT DEFAULT 301,
    active TINYINT DEFAULT 1,
    hits INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY idx_from_url (from_url(191))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ÍNDICES
CREATE INDEX idx_products_slug ON products(slug);
CREATE INDEX idx_products_active ON products(active, featured);
CREATE INDEX idx_orders_status ON orders(status, created_at);
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_number ON orders(order_number);
CREATE INDEX idx_orders_external ON orders(external_order_id, external_platform);
CREATE INDEX idx_coupons_code ON coupons(code, active);
CREATE INDEX idx_points_customer ON points_transactions(customer_id, created_at);
CREATE INDEX idx_subscriptions_customer ON subscriptions(customer_id, status);
CREATE INDEX idx_invoices_order ON invoices(order_id);
CREATE INDEX idx_invoices_customer ON invoices(customer_id);
CREATE INDEX idx_newsletter_email ON newsletter_subscribers(email, active);


-- ─── CMS: PÁGINAS Y BLOQUES DE CONTENIDO ──────────────────────
-- Páginas CMS (home, contacto, sobre nosotros, legales…)
CREATE TABLE IF NOT EXISTS cms_pages (
    id INT PRIMARY KEY AUTO_INCREMENT,
    `key` VARCHAR(80) UNIQUE NOT NULL,        -- 'home', 'contact', 'about', 'privacy'…
    title VARCHAR(255) NOT NULL,
    meta_title VARCHAR(255),
    meta_description VARCHAR(500),
    active TINYINT DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Bloques de contenido por página (sección hero, historia, stats, etc.)
CREATE TABLE IF NOT EXISTS cms_blocks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    page_key VARCHAR(80) NOT NULL,            -- clave de la página
    block_key VARCHAR(80) NOT NULL,           -- 'hero', 'about', 'features', 'cta'…
    label VARCHAR(150) NOT NULL,              -- nombre legible en el admin
    sort_order INT DEFAULT 0,
    active TINYINT DEFAULT 1,
    UNIQUE KEY uq_page_block (page_key, block_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Campos dentro de cada bloque
CREATE TABLE IF NOT EXISTS cms_fields (
    id INT PRIMARY KEY AUTO_INCREMENT,
    page_key VARCHAR(80) NOT NULL,
    block_key VARCHAR(80) NOT NULL,
    field_key VARCHAR(80) NOT NULL,           -- 'title', 'subtitle', 'text', 'image', 'btn_text', 'btn_url'
    field_type ENUM('text','textarea','richtext','image','url','color','toggle','number') DEFAULT 'text',
    label VARCHAR(150) NOT NULL,
    value MEDIUMTEXT,
    sort_order INT DEFAULT 0,
    UNIQUE KEY uq_field (page_key, block_key, field_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Menú de navegación
CREATE TABLE IF NOT EXISTS nav_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    menu VARCHAR(40) DEFAULT 'main',          -- 'main', 'footer'
    label VARCHAR(100) NOT NULL,
    url VARCHAR(255) NOT NULL,
    target VARCHAR(10) DEFAULT '_self',
    sort_order INT DEFAULT 0,
    active TINYINT DEFAULT 1,
    parent_id INT NULL,
    FOREIGN KEY (parent_id) REFERENCES nav_items(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
