-- Saved SQL insights can be pinned as live widgets on the home dashboard.
-- Keep the existing saved_query table compatible with older installations.
CREATE TABLE IF NOT EXISTS saved_query (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    query TEXT NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS dashboard_widget (
    widget_id SERIAL PRIMARY KEY,
    person_id INTEGER NOT NULL REFERENCES person(person_id) ON DELETE CASCADE,
    saved_query_id INTEGER NOT NULL REFERENCES saved_query(id) ON DELETE CASCADE,
    visualization_type VARCHAR(20) NOT NULL DEFAULT 'table',
    position INTEGER NOT NULL DEFAULT 0,
    width INTEGER NOT NULL DEFAULT 1,
    is_visible BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT dashboard_widget_visualization_type_chk
        CHECK (visualization_type IN ('table', 'graph')),
    CONSTRAINT dashboard_widget_unique_view
        UNIQUE (person_id, saved_query_id, visualization_type)
);

CREATE INDEX IF NOT EXISTS dashboard_widget_person_position_idx
    ON dashboard_widget (person_id, position, widget_id);