59 lines
2.2 KiB
SQL
59 lines
2.2 KiB
SQL
-- Tilbudgivern Advanced Database Setup
|
|
-- Ensures all tables have the correct schema for the application
|
|
|
|
USE tilbudgivern;
|
|
|
|
-- Update project_quotes table to ensure metadata column exists
|
|
ALTER TABLE project_quotes
|
|
ADD COLUMN IF NOT EXISTS metadata LONGTEXT NULL;
|
|
|
|
-- Update material_prices table to ensure name column exists (not material_name)
|
|
ALTER TABLE material_prices
|
|
ADD COLUMN IF NOT EXISTS name VARCHAR(255) NULL AFTER id;
|
|
|
|
-- Fix material_prices table if it has wrong column names
|
|
-- Check if material_name exists and rename it to name
|
|
SET @col_exists = 0;
|
|
SELECT COUNT(*) INTO @col_exists
|
|
FROM information_schema.columns
|
|
WHERE table_name='material_prices'
|
|
AND column_name='material_name'
|
|
AND table_schema='tilbudgivern';
|
|
|
|
SET @sql = IF(@col_exists > 0, 'ALTER TABLE material_prices CHANGE material_name name VARCHAR(255)', 'SELECT 1');
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
-- Ensure material_prices has all required columns
|
|
ALTER TABLE material_prices
|
|
ADD COLUMN IF NOT EXISTS category VARCHAR(100) NULL,
|
|
ADD COLUMN IF NOT EXISTS subcategory VARCHAR(255) NULL,
|
|
ADD COLUMN IF NOT EXISTS unit VARCHAR(50) NULL DEFAULT 'stk',
|
|
ADD COLUMN IF NOT EXISTS supplier_name VARCHAR(255) NULL,
|
|
ADD COLUMN IF NOT EXISTS sku VARCHAR(100) NULL,
|
|
ADD COLUMN IF NOT EXISTS confidence DECIMAL(3,2) NULL DEFAULT 1.00,
|
|
ADD COLUMN IF NOT EXISTS document_date DATE NULL;
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_project_quotes_created_at ON project_quotes(created_at);
|
|
CREATE INDEX IF NOT EXISTS idx_material_prices_category ON material_prices(category);
|
|
CREATE INDEX IF NOT EXISTS idx_project_quotes_project_type ON project_quotes(project_type);
|
|
|
|
-- Ensure OpenAI usage tracking table exists
|
|
CREATE TABLE IF NOT EXISTS openai_usage_tracking (
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
request_type VARCHAR(100) NOT NULL,
|
|
tokens_used INT NOT NULL DEFAULT 0,
|
|
cost_usd DECIMAL(10,4) NOT NULL DEFAULT 0.0000,
|
|
model VARCHAR(100) NOT NULL DEFAULT 'gpt-4o-mini',
|
|
request_timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
success BOOLEAN DEFAULT TRUE,
|
|
error_message TEXT NULL,
|
|
INDEX idx_timestamp (request_timestamp),
|
|
INDEX idx_request_type (request_type)
|
|
);
|
|
|
|
-- Show confirmation
|
|
SELECT 'Database schema updated successfully' as status;
|