-- ============================================================================
-- Schema: trading
-- File: 12-drawing_tools.sql
-- Description: User drawings and annotations on trading charts
-- Related: OQI-003 Trading Charts, GAP-006
-- Dependencies: 00-enums.sql, 01-symbols.sql, auth.users
-- ============================================================================

CREATE TABLE IF NOT EXISTS trading.drawing_tools (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    symbol_id UUID NOT NULL REFERENCES trading.symbols(id) ON DELETE CASCADE,
    timeframe trading.timeframe NOT NULL,
    tool_type trading.drawing_tool_type NOT NULL,
    name VARCHAR(100),

    -- Coordinates (stored as JSON for flexibility)
    points JSONB NOT NULL DEFAULT '[]', -- Array of {time, price} points

    -- Style configuration
    style JSONB NOT NULL DEFAULT '{
        "color": "#2196F3",
        "lineWidth": 1,
        "lineStyle": "solid",
        "fillColor": null,
        "fillOpacity": 0.2,
        "showLabel": true,
        "labelPosition": "right"
    }',

    -- Fibonacci specific (if applicable)
    fib_levels DECIMAL(5,4)[] DEFAULT NULL, -- e.g., {0, 0.236, 0.382, 0.5, 0.618, 0.786, 1}

    -- Text content (for text/label tools)
    text_content TEXT,

    -- Visibility and state
    is_visible BOOLEAN NOT NULL DEFAULT true,
    is_locked BOOLEAN NOT NULL DEFAULT false,
    z_index INTEGER NOT NULL DEFAULT 0,

    -- Template/sharing
    is_template BOOLEAN NOT NULL DEFAULT false,
    is_shared BOOLEAN NOT NULL DEFAULT false,

    -- Metadata
    metadata JSONB DEFAULT '{}',

    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    CONSTRAINT drawing_tools_points_not_empty CHECK (jsonb_array_length(points) >= 1)
);

-- Indexes
CREATE INDEX idx_drawing_tools_user_id ON trading.drawing_tools(user_id);
CREATE INDEX idx_drawing_tools_symbol_id ON trading.drawing_tools(symbol_id);
CREATE INDEX idx_drawing_tools_user_symbol ON trading.drawing_tools(user_id, symbol_id, timeframe);
CREATE INDEX idx_drawing_tools_visible ON trading.drawing_tools(user_id, is_visible) WHERE is_visible = true;
CREATE INDEX idx_drawing_tools_templates ON trading.drawing_tools(is_template) WHERE is_template = true;
CREATE INDEX idx_drawing_tools_shared ON trading.drawing_tools(is_shared) WHERE is_shared = true;
CREATE INDEX idx_drawing_tools_points ON trading.drawing_tools USING GIN(points);

-- Trigger
CREATE TRIGGER trg_drawing_tools_updated_at
    BEFORE UPDATE ON trading.drawing_tools
    FOR EACH ROW
    EXECUTE FUNCTION public.update_updated_at();

-- Comments
COMMENT ON TABLE trading.drawing_tools IS 'User drawings and annotations on trading charts';
COMMENT ON COLUMN trading.drawing_tools.points IS 'Array of coordinate points [{time: ISO string, price: number}]';
COMMENT ON COLUMN trading.drawing_tools.style IS 'Visual style configuration (color, line width, fill, etc.)';
COMMENT ON COLUMN trading.drawing_tools.fib_levels IS 'Fibonacci levels for retracement/extension tools';
COMMENT ON COLUMN trading.drawing_tools.z_index IS 'Z-index for layering multiple drawings';
COMMENT ON COLUMN trading.drawing_tools.is_template IS 'Whether this drawing can be used as a template';
COMMENT ON COLUMN trading.drawing_tools.is_shared IS 'Whether this drawing is visible to other users';