-- Templates table for storing import templates
CREATE TABLE IF NOT EXISTS templates (
    id SERIAL PRIMARY KEY,
    company TEXT NOT NULL,
    product_type TEXT NOT NULL,
    supplier TEXT,
    msrp DECIMAL(10,2),
    cost_each DECIMAL(10,2),
    qty_per_unit INTEGER,
    case_qty INTEGER,
    hts_code TEXT,
    description TEXT,
    weight DECIMAL(10,2),
    length DECIMAL(10,2),
    width DECIMAL(10,2),
    height DECIMAL(10,2),
    tax_cat TEXT,
    size_cat TEXT,
    categories TEXT[],
    ship_restrictions TEXT[],
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(company, product_type)
);

-- AI Prompts table for storing validation prompts
CREATE TABLE IF NOT EXISTS ai_prompts (
    id SERIAL PRIMARY KEY,
    prompt_text TEXT NOT NULL,
    prompt_type TEXT NOT NULL CHECK (prompt_type IN ('general', 'company_specific', 'system')),
    company TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT unique_company_prompt UNIQUE (company),
    CONSTRAINT company_required_for_specific CHECK (
        (prompt_type = 'general' AND company IS NULL) OR 
        (prompt_type = 'system' AND company IS NULL) OR 
        (prompt_type = 'company_specific' AND company IS NOT NULL)
    )
);

-- Create a unique partial index to ensure only one general prompt
CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_general_prompt 
ON ai_prompts (prompt_type) 
WHERE prompt_type = 'general';

-- Create a unique partial index to ensure only one system prompt
CREATE UNIQUE INDEX IF NOT EXISTS idx_unique_system_prompt 
ON ai_prompts (prompt_type) 
WHERE prompt_type = 'system';

-- Reusable Images table for storing persistent images
CREATE TABLE IF NOT EXISTS reusable_images (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    filename TEXT NOT NULL,
    file_path TEXT NOT NULL,
    image_url TEXT NOT NULL,
    is_global BOOLEAN NOT NULL DEFAULT false,
    company TEXT,
    mime_type TEXT,
    file_size INTEGER,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT company_required_for_non_global CHECK (
        (is_global = true AND company IS NULL) OR
        (is_global = false AND company IS NOT NULL)
    )
);

-- Create index on company for efficient querying
CREATE INDEX IF NOT EXISTS idx_reusable_images_company ON reusable_images(company);
-- Create index on is_global for efficient querying
CREATE INDEX IF NOT EXISTS idx_reusable_images_is_global ON reusable_images(is_global);

-- AI Validation Performance Tracking
CREATE TABLE IF NOT EXISTS ai_validation_performance (
    id SERIAL PRIMARY KEY,
    prompt_length INTEGER NOT NULL,
    product_count INTEGER NOT NULL,
    start_time TIMESTAMP WITH TIME ZONE NOT NULL,
    end_time TIMESTAMP WITH TIME ZONE NOT NULL,
    duration_seconds DECIMAL(10,2) GENERATED ALWAYS AS (EXTRACT(EPOCH FROM (end_time - start_time))) STORED,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Create index on prompt_length for efficient querying
CREATE INDEX IF NOT EXISTS idx_ai_validation_prompt_length ON ai_validation_performance(prompt_length);

-- Function to update the updated_at timestamp
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- Trigger to automatically update the updated_at column
CREATE TRIGGER update_templates_updated_at
    BEFORE UPDATE ON templates
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Trigger to automatically update the updated_at column for ai_prompts
CREATE TRIGGER update_ai_prompts_updated_at
    BEFORE UPDATE ON ai_prompts
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
    
-- Trigger to automatically update the updated_at column for reusable_images
CREATE TRIGGER update_reusable_images_updated_at
    BEFORE UPDATE ON reusable_images
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();