-- Amrod to Zoho Inventory Sync Database Schema (Fixed)
-- Run this to create the required database and tables

CREATE DATABASE IF NOT EXISTS amrod_zoho_sync CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE amrod_zoho_sync;

-- Sync History Table
CREATE TABLE IF NOT EXISTS sync_history (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sync_type ENUM('full', 'incremental', 'stock', 'images') NOT NULL,
    status ENUM('running', 'completed', 'failed', 'partial') NOT NULL,
    started_at DATETIME NOT NULL,
    completed_at DATETIME,
    products_processed INT DEFAULT 0,
    products_created INT DEFAULT 0,
    products_updated INT DEFAULT 0,
    products_failed INT DEFAULT 0,
    error_message TEXT,
    execution_time INT COMMENT 'Seconds',
    memory_peak INT COMMENT 'Bytes',
    INDEX idx_sync_type (sync_type),
    INDEX idx_status (status),
    INDEX idx_started_at (started_at)
) ENGINE=InnoDB;

-- Product Mapping Table (Amrod SKU to Zoho Item ID)
CREATE TABLE IF NOT EXISTS product_mapping (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amrod_sku VARCHAR(100) NOT NULL UNIQUE,
    amrod_product_code VARCHAR(100),
    zoho_item_id BIGINT NOT NULL,
    product_name VARCHAR(500),
    category VARCHAR(255),
    last_synced_at DATETIME,
    amrod_last_modified DATETIME,
    zoho_last_modified DATETIME,
    sync_status ENUM('synced', 'pending', 'error', 'disabled') DEFAULT 'synced',
    error_count INT DEFAULT 0,
    last_error TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_amrod_sku (amrod_sku),
    INDEX idx_zoho_item_id (zoho_item_id),
    INDEX idx_sync_status (sync_status),
    INDEX idx_last_synced (last_synced_at)
) ENGINE=InnoDB;

-- Sync Queue Table (for failed items to retry)
CREATE TABLE IF NOT EXISTS sync_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amrod_sku VARCHAR(100) NOT NULL,
    action ENUM('create', 'update', 'delete', 'stock_update') NOT NULL,
    priority INT DEFAULT 5 COMMENT '1=highest, 10=lowest',
    attempts INT DEFAULT 0,
    max_attempts INT DEFAULT 3,
    product_data JSON,
    error_message TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    next_retry_at DATETIME,
    processed_at DATETIME,
    INDEX idx_amrod_sku (amrod_sku),
    INDEX idx_next_retry (next_retry_at),
    INDEX idx_priority (priority)
) ENGINE=InnoDB;

-- API Rate Limiting Table
CREATE TABLE IF NOT EXISTS api_rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    api_name VARCHAR(50) NOT NULL COMMENT 'amrod or zoho',
    request_count INT DEFAULT 0,
    window_start DATETIME NOT NULL,
    window_type ENUM('minute', 'hour', 'day') NOT NULL,
    reset_at DATETIME NOT NULL,
    UNIQUE KEY unique_window (api_name, window_type, window_start)
) ENGINE=InnoDB;

-- OAuth Tokens Table (for Zoho)
CREATE TABLE IF NOT EXISTS oauth_tokens (
    id INT AUTO_INCREMENT PRIMARY KEY,
    service VARCHAR(50) NOT NULL DEFAULT 'zoho',
    access_token TEXT,
    refresh_token TEXT,
    token_type VARCHAR(50),
    expires_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_service (service)
) ENGINE=InnoDB;

-- Sync Log Table (detailed logging)
CREATE TABLE IF NOT EXISTS sync_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    sync_history_id INT,
    level ENUM('debug', 'info', 'warning', 'error') NOT NULL,
    message TEXT NOT NULL,
    context JSON COMMENT 'Additional data',
    amrod_sku VARCHAR(100),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sync_history (sync_history_id),
    INDEX idx_level (level),
    INDEX idx_created_at (created_at),
    FOREIGN KEY (sync_history_id) REFERENCES sync_history(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Price History Table (optional - track price changes)
CREATE TABLE IF NOT EXISTS price_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    amrod_sku VARCHAR(100) NOT NULL,
    amrod_price DECIMAL(10,2),
    markup_percentage DECIMAL(5,2),
    zoho_price DECIMAL(10,2),
    changed_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_amrod_sku (amrod_sku),
    INDEX idx_changed_at (changed_at)
) ENGINE=InnoDB;

-- Sync State Table (track last sync timestamps)
CREATE TABLE IF NOT EXISTS sync_state (
    id INT AUTO_INCREMENT PRIMARY KEY,
    state_key VARCHAR(100) NOT NULL UNIQUE,
    state_value TEXT,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Admin Users Table
CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    notification_preference ENUM('all', 'errors_only', 'none') DEFAULT 'errors_only',
    active BOOLEAN DEFAULT TRUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Insert initial sync state
INSERT INTO sync_state (state_key, state_value) VALUES 
    ('last_full_sync', NULL),
    ('last_incremental_sync', NULL),
    ('last_stock_sync', NULL),
    ('amrod_last_update_check', NULL)
ON DUPLICATE KEY UPDATE state_key=state_key;

-- Insert sample admin user for notifications
INSERT INTO admin_users (name, email, notification_preference) VALUES 
    ('Francois', 'francois@admartagency.co.za', 'errors_only')
ON DUPLICATE KEY UPDATE name=name;

-- Create views for easy reporting
CREATE OR REPLACE VIEW v_sync_summary AS
SELECT 
    DATE(started_at) as sync_date,
    sync_type,
    COUNT(*) as total_syncs,
    SUM(products_processed) as total_products,
    SUM(products_created) as total_created,
    SUM(products_updated) as total_updated,
    SUM(products_failed) as total_failed,
    AVG(execution_time) as avg_execution_time,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_syncs
FROM sync_history
GROUP BY DATE(started_at), sync_type
ORDER BY sync_date DESC, sync_type;

CREATE OR REPLACE VIEW v_pending_queue AS
SELECT 
    sq.*,
    pm.product_name,
    pm.category
FROM sync_queue sq
LEFT JOIN product_mapping pm ON sq.amrod_sku = pm.amrod_sku
WHERE sq.processed_at IS NULL 
  AND sq.attempts < sq.max_attempts
  AND (sq.next_retry_at IS NULL OR sq.next_retry_at <= NOW())
ORDER BY sq.priority ASC, sq.created_at ASC;

CREATE OR REPLACE VIEW v_error_products AS
SELECT 
    pm.amrod_sku,
    pm.product_name,
    pm.category,
    pm.sync_status,
    pm.error_count,
    pm.last_error,
    pm.last_synced_at,
    pm.updated_at
FROM product_mapping pm
WHERE pm.sync_status = 'error' OR pm.error_count > 0
ORDER BY pm.error_count DESC, pm.updated_at DESC;
