-- AI Chat SaaS za WooCommerce trgovine
-- MySQL 5.7+ / 8.0, InnoDB, utf8mb4

CREATE TABLE tenants (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    api_key_hash VARCHAR(64) NOT NULL,
    widget_key VARCHAR(64) NOT NULL,
    plan VARCHAR(50) NOT NULL DEFAULT 'free',
    status ENUM('active','suspended') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_tenants_email (email),
    UNIQUE KEY uq_tenants_api_key_hash (api_key_hash),
    UNIQUE KEY uq_tenants_widget_key (widget_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE woo_connections (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    store_url VARCHAR(255) NOT NULL,
    consumer_key VARCHAR(255) NOT NULL,
    consumer_secret VARCHAR(255) NOT NULL,
    last_synced_at DATETIME NULL,
    status ENUM('pending','syncing','connected','error') NOT NULL DEFAULT 'pending',
    last_error VARCHAR(500) NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_woo_connections_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    woo_product_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(500) NOT NULL,
    description TEXT NULL,
    price DECIMAL(10,2) NULL,
    category VARCHAR(255) NULL,
    url VARCHAR(500) NULL,
    image_url VARCHAR(500) NULL,
    stock_status VARCHAR(50) NULL,
    embedding JSON NULL,
    updated_at DATETIME NOT NULL,
    UNIQUE KEY uq_products_tenant_woo (tenant_id, woo_product_id),
    CONSTRAINT fk_products_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FULLTEXT KEY ft_products_name_description (name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ročno vnesena vsebina (FAQ, opisi, politike ipd.) - alternativa/dopolnilo
-- WooCommerce uvozu, del iste baze znanja za chat bota
CREATE TABLE content_items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    title VARCHAR(500) NOT NULL,
    body TEXT NOT NULL,
    url VARCHAR(500) NULL,
    embedding JSON NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    CONSTRAINT fk_content_items_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    FULLTEXT KEY ft_content_items_title_body (title, body)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Ročna "queue" tabela namesto Redis/Horizon - obdeluje jo cron/process_jobs.php
CREATE TABLE jobs (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    type VARCHAR(100) NOT NULL,
    payload JSON NULL,
    status ENUM('pending','processing','done','failed') NOT NULL DEFAULT 'pending',
    attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    last_error VARCHAR(500) NULL,
    run_after DATETIME NOT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    CONSTRAINT fk_jobs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    KEY idx_jobs_status_run_after (status, run_after)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE conversations (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    session_id VARCHAR(64) NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_conversations_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    KEY idx_conversations_tenant_session (tenant_id, session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id INT UNSIGNED NOT NULL,
    role ENUM('user','assistant') NOT NULL,
    content TEXT NOT NULL,
    created_at DATETIME NOT NULL,
    CONSTRAINT fk_messages_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE webhook_log (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT UNSIGNED NOT NULL,
    topic VARCHAR(100) NOT NULL,
    payload JSON NULL,
    received_at DATETIME NOT NULL,
    CONSTRAINT fk_webhook_log_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;