-- V026 Visual ETL Studio
CREATE TABLE IF NOT EXISTS etl_pipelines_v026 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE,name varchar(180) NOT NULL,description text NOT NULL DEFAULT '',owner_id varchar(36) NOT NULL REFERENCES users(id),nodes json NOT NULL DEFAULT '[]',edges json NOT NULL DEFAULT '[]',variables json NOT NULL DEFAULT '{}',status varchar(30) NOT NULL DEFAULT 'draft',schedule_cron varchar(120),timezone varchar(60) NOT NULL DEFAULT 'America/Sao_Paulo',enabled boolean NOT NULL DEFAULT true,version integer NOT NULL DEFAULT 1,last_run_at timestamptz,next_run_at timestamptz,created_at timestamptz NOT NULL DEFAULT now(),updated_at timestamptz NOT NULL DEFAULT now(),UNIQUE(tenant_id,name));
CREATE TABLE IF NOT EXISTS etl_pipeline_versions_v026 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE,pipeline_id varchar(36) NOT NULL REFERENCES etl_pipelines_v026(id) ON DELETE CASCADE,version integer NOT NULL,snapshot json NOT NULL DEFAULT '{}',created_by varchar(36) NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now(),UNIQUE(pipeline_id,version));
CREATE TABLE IF NOT EXISTS etl_pipeline_runs_v026 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE,pipeline_id varchar(36) NOT NULL REFERENCES etl_pipelines_v026(id) ON DELETE CASCADE,requested_by varchar(36) NOT NULL REFERENCES users(id),mode varchar(30) NOT NULL DEFAULT 'full',status varchar(30) NOT NULL DEFAULT 'queued',progress integer NOT NULL DEFAULT 0,rows_in integer NOT NULL DEFAULT 0,rows_out integer NOT NULL DEFAULT 0,output_key varchar(1000) NOT NULL DEFAULT '',checksum varchar(128) NOT NULL DEFAULT '',error text NOT NULL DEFAULT '',started_at timestamptz,finished_at timestamptz,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS etl_node_runs_v026 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL REFERENCES tenants_v020(id) ON DELETE CASCADE,pipeline_run_id varchar(36) NOT NULL REFERENCES etl_pipeline_runs_v026(id) ON DELETE CASCADE,node_id varchar(120) NOT NULL,node_type varchar(50) NOT NULL,status varchar(30) NOT NULL DEFAULT 'queued',rows_in integer NOT NULL DEFAULT 0,rows_out integer NOT NULL DEFAULT 0,sample json NOT NULL DEFAULT '[]',error text NOT NULL DEFAULT '',started_at timestamptz,finished_at timestamptz);
CREATE TABLE IF NOT EXISTS etl_templates_v026 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) REFERENCES tenants_v020(id) ON DELETE CASCADE,name varchar(180) NOT NULL,category varchar(80) NOT NULL DEFAULT 'general',description text NOT NULL DEFAULT '',nodes json NOT NULL DEFAULT '[]',edges json NOT NULL DEFAULT '[]',public boolean NOT NULL DEFAULT false,created_by varchar(36) NOT NULL REFERENCES users(id),created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX IF NOT EXISTS ix_etl_pipeline_tenant_v026 ON etl_pipelines_v026(tenant_id,status);
CREATE INDEX IF NOT EXISTS ix_etl_run_tenant_v026 ON etl_pipeline_runs_v026(tenant_id,status,created_at);
ALTER TABLE etl_pipelines_v026 ENABLE ROW LEVEL SECURITY; ALTER TABLE etl_pipelines_v026 FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS etl_pipelines_tenant_v026 ON etl_pipelines_v026; CREATE POLICY etl_pipelines_tenant_v026 ON etl_pipelines_v026 USING (tenant_id::text=current_setting('app.current_tenant_id',true)) WITH CHECK (tenant_id::text=current_setting('app.current_tenant_id',true));
