-- Migration: Add color requests feature
-- Allows users to request new colors and admins to view/manage requests

-- Create color_requests table
CREATE TABLE IF NOT EXISTS color_requests (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    color_name VARCHAR(100) NOT NULL,
    material_type VARCHAR(50) NOT NULL,
    finish_type VARCHAR(50),
    user_email VARCHAR(255),
    user_name VARCHAR(100),
    description TEXT,
    reference_url VARCHAR(500),
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'completed')),
    admin_notes TEXT,
    request_count INTEGER DEFAULT 1,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    processed_at TIMESTAMP WITH TIME ZONE,
    processed_by VARCHAR(100)
);

-- Create indexes for better performance
CREATE INDEX idx_color_requests_status ON color_requests(status);
CREATE INDEX idx_color_requests_created_at ON color_requests(created_at DESC);
CREATE INDEX idx_color_requests_color_name ON color_requests(LOWER(color_name));

-- Apply updated_at trigger to color_requests table
CREATE TRIGGER update_color_requests_updated_at BEFORE UPDATE
    ON color_requests FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- Add comment to describe the table
COMMENT ON TABLE color_requests IS 'User requests for new filament colors to be added to inventory';
COMMENT ON COLUMN color_requests.status IS 'Request status: pending (new), approved (will be ordered), rejected (won''t be added), completed (added to inventory)';
COMMENT ON COLUMN color_requests.request_count IS 'Number of users who have requested this same color';