-- Migration: Create offline_queue table for storing offline operations
-- Date: 2025-03-08

CREATE TABLE IF NOT EXISTS offline_queue (
    id INT AUTO_INCREMENT PRIMARY KEY,
    operation_type VARCHAR(50) NOT NULL,
    table_name VARCHAR(50),
    data JSON NOT NULL,
    attempts INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_operation_type (operation_type),
    INDEX idx_created_at (created_at),
    INDEX idx_attempts (attempts)
);

-- Add offline_id columns to existing tables for conflict resolution
ALTER TABLE sales ADD COLUMN IF NOT EXISTS offline_id VARCHAR(100) UNIQUE;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS offline_id VARCHAR(100) UNIQUE;
ALTER TABLE loyalty_transactions ADD COLUMN IF NOT EXISTS offline_id VARCHAR(100) UNIQUE;

-- Add indexes for offline_id columns
CREATE INDEX IF NOT EXISTS idx_sales_offline_id ON sales(offline_id);
CREATE INDEX IF NOT EXISTS idx_customers_offline_id ON customers(offline_id);
CREATE INDEX IF NOT EXISTS idx_loyalty_transactions_offline_id ON loyalty_transactions(offline_id);