-- Phase 6 of the Projects/Tasks governance upgrade: saved structured filters ("views").
-- Filters share the exact {field, operator, value} shape used by AutomationRule's
-- conditions_json (see ConditionEvaluator) — a saved view and a rule's condition list are
-- interchangeable data, not two parallel filter grammars.
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/saved_views.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS dim_saved_views (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    user_id INT NOT NULL COMMENT 'Owning user — a saved view is private, not shared, in this first cut',
    project_id BIGINT NULL COMMENT 'NULL = applies across every project',
    name VARCHAR(255) NOT NULL,
    filters_json LONGTEXT NOT NULL COMMENT 'JSON array of {field, operator, value} — ANDed',
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    INDEX idx_saved_views_user (tenant_id, user_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;