-- V024 Modular SaaS, Visual Data Catalog and Governed Automation
CREATE TABLE IF NOT EXISTS tenant_module_assignments_v024 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL,module_code varchar(100) NOT NULL,enabled boolean NOT NULL DEFAULT true,contract_limits jsonb NOT NULL DEFAULT '{}'::jsonb,configuration jsonb NOT NULL DEFAULT '{}'::jsonb,activated_by varchar(36),activated_at timestamptz NOT NULL DEFAULT now(),updated_at timestamptz NOT NULL DEFAULT now(),UNIQUE(tenant_id,module_code));
CREATE TABLE IF NOT EXISTS data_field_metadata_v024 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL,source_id varchar(36) NOT NULL,dataset_id varchar(36),qualified_object varchar(500) NOT NULL,column_name varchar(255) NOT NULL,physical_type varchar(180) DEFAULT '',friendly_name varchar(255) DEFAULT '',description text DEFAULT '',business_notes text DEFAULT '',classification varchar(60) DEFAULT 'internal',tags jsonb NOT NULL DEFAULT '[]'::jsonb,nullable boolean DEFAULT true,visible boolean DEFAULT true,searchable boolean DEFAULT true,aggregatable boolean DEFAULT true,updated_by varchar(36),updated_at timestamptz NOT NULL DEFAULT now(),UNIQUE(tenant_id,source_id,qualified_object,column_name));
CREATE TABLE IF NOT EXISTS visual_governance_policies_v024 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL,name varchar(180) NOT NULL,dataset_id varchar(36) NOT NULL,subject_type varchar(30) NOT NULL,subject_id varchar(120) NOT NULL,effect varchar(20) DEFAULT 'allow',actions jsonb NOT NULL DEFAULT '["read"]'::jsonb,allowed_columns jsonb NOT NULL DEFAULT '[]'::jsonb,denied_columns jsonb NOT NULL DEFAULT '[]'::jsonb,scope_type varchar(30) DEFAULT 'all',scope_config jsonb NOT NULL DEFAULT '{}'::jsonb,enabled boolean DEFAULT true,priority integer DEFAULT 100,created_by varchar(36) NOT NULL,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS data_process_flows_v024 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL,name varchar(180) NOT NULL,description text DEFAULT '',owner_id varchar(36) NOT NULL,department_id varchar(36),source_id varchar(36),nodes jsonb NOT NULL DEFAULT '[]'::jsonb,edges jsonb NOT NULL DEFAULT '[]'::jsonb,schedule_cron varchar(120),timezone varchar(60) DEFAULT 'America/Sao_Paulo',enabled boolean DEFAULT true,publish_status varchar(30) DEFAULT 'draft',last_run_at timestamptz,next_run_at timestamptz,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS data_process_runs_v024 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL,flow_id varchar(36) NOT NULL,status varchar(30) DEFAULT 'queued',node_status jsonb NOT NULL DEFAULT '{}'::jsonb,rows_processed integer DEFAULT 0,error text DEFAULT '',started_at timestamptz,finished_at timestamptz,created_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE IF NOT EXISTS governed_report_schedules_v024 (id varchar(36) PRIMARY KEY,tenant_id varchar(36) NOT NULL,report_id varchar(36) NOT NULL,owner_id varchar(36) NOT NULL,name varchar(180) NOT NULL,cron varchar(120) NOT NULL,timezone varchar(60) DEFAULT 'America/Sao_Paulo',formats jsonb NOT NULL DEFAULT '["pdf"]'::jsonb,channels jsonb NOT NULL DEFAULT '["dashboard"]'::jsonb,recipient_user_ids jsonb NOT NULL DEFAULT '[]'::jsonb,recipient_team_ids jsonb NOT NULL DEFAULT '[]'::jsonb,recipient_emails jsonb NOT NULL DEFAULT '[]'::jsonb,subject_template varchar(255) DEFAULT 'Relatório atualizado',message_template text DEFAULT '',enabled boolean DEFAULT true,next_run_at timestamptz,last_run_at timestamptz,created_at timestamptz NOT NULL DEFAULT now());
CREATE INDEX IF NOT EXISTS idx_field_meta_tenant_source_v024 ON data_field_metadata_v024(tenant_id,source_id);
CREATE INDEX IF NOT EXISTS idx_governance_tenant_dataset_v024 ON visual_governance_policies_v024(tenant_id,dataset_id);
CREATE INDEX IF NOT EXISTS idx_flow_tenant_schedule_v024 ON data_process_flows_v024(tenant_id,next_run_at);
