-- Marketing QBit (com.kingsrook.qbits:qbit-marketing) - PostgreSQL DDL
--
-- Generated by scripts/generate-model.py (the single spec for entities, enums and DDL).
-- Table names are the QQQ table names (marketing_*), and column names are the
-- snake_case forms of the QQQ field names, which the qbit sets as each field's
-- backend name (MarketingQBitConfig.inferBackendNames, default true).
--
-- Liquibase: paste into a formatted-SQL changeset. Idempotent (IF NOT EXISTS).

-- A product the marketing team promotes: its brief, maturity and official links.
CREATE TABLE IF NOT EXISTS marketing_product (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   name VARCHAR(100) NOT NULL,
   slug VARCHAR(64) NOT NULL,
   tagline VARCHAR(255),
   summary TEXT,
   maturity INTEGER NOT NULL,
   readiness_notes TEXT,
   primary_url VARCHAR(1000),
   repository_url VARCHAR(1000),
   documentation_url VARCHAR(1000),
   releases_url VARCHAR(1000),
   license VARCHAR(100),
   platforms VARCHAR(255),
   latest_release VARCHAR(100),
   adoption_path TEXT,
   value_propositions TEXT,
   owner_user_id VARCHAR(100),
   last_verified_date DATE,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_product PRIMARY KEY (id),
   CONSTRAINT uq_marketing_product_1 UNIQUE (slug)
);


-- An audience of a product: who they are, the real problem and the task the product helps complete.
CREATE TABLE IF NOT EXISTS marketing_audience (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER NOT NULL,
   name VARCHAR(150) NOT NULL,
   problem TEXT,
   useful_task TEXT,
   description TEXT,
   evidence_note TEXT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_audience PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_audience_product_id ON marketing_audience (product_id);

-- A source that product facts were verified against, with its verification date.
CREATE TABLE IF NOT EXISTS marketing_evidence_source (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER NOT NULL,
   title VARCHAR(255) NOT NULL,
   url VARCHAR(1000) NOT NULL,
   source_type INTEGER NOT NULL,
   publisher VARCHAR(150),
   verified_date DATE,
   verified_by VARCHAR(100),
   excerpt TEXT,
   notes TEXT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_evidence_source PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_evidence_source_product_id ON marketing_evidence_source (product_id);

-- A factual claim about a product, its status (released, planned, disputed) and its source.
CREATE TABLE IF NOT EXISTS marketing_claim (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER NOT NULL,
   claim_text VARCHAR(1000) NOT NULL,
   status INTEGER NOT NULL,
   source_id INTEGER,
   category VARCHAR(64),
   evidence_quote TEXT,
   verified_date DATE,
   discrepancy_note TEXT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_claim PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_claim_product_id ON marketing_claim (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_claim_source_id ON marketing_claim (source_id);

-- A campaign: one goal, audience, message, channels, call to action and measurable success criteria.
CREATE TABLE IF NOT EXISTS marketing_campaign (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER NOT NULL,
   name VARCHAR(200) NOT NULL,
   goal INTEGER NOT NULL,
   status INTEGER NOT NULL,
   audience_id INTEGER,
   objective TEXT,
   problem TEXT,
   messaging TEXT,
   channel_plan TEXT,
   call_to_action VARCHAR(500),
   destination_url VARCHAR(1000),
   utm_campaign VARCHAR(100),
   schedule_start TIMESTAMP WITHOUT TIME ZONE,
   schedule_end TIMESTAMP WITHOUT TIME ZONE,
   owner_user_id VARCHAR(100),
   success_criteria TEXT,
   lead_qualification_enabled BOOLEAN,
   qualify_min_form_submissions INTEGER,
   qualify_require_email_consent BOOLEAN,
   lead_qualification_note TEXT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_campaign PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_campaign_product_id ON marketing_campaign (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_campaign_audience_id ON marketing_campaign (audience_id);

-- A channel content can go to, its transport and the rules it must respect.
CREATE TABLE IF NOT EXISTS marketing_channel (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   name VARCHAR(100) NOT NULL,
   channel_type INTEGER NOT NULL,
   transport_name VARCHAR(64),
   is_active BOOLEAN,
   allows_automation BOOLEAN,
   ai_content_policy INTEGER NOT NULL,
   rules_summary TEXT,
   rules_source_url VARCHAR(1000),
   rules_verified_date DATE,
   rate_limit_per_hour INTEGER,
   max_recipients_per_execution INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_channel PRIMARY KEY (id),
   CONSTRAINT uq_marketing_channel_1 UNIQUE (name)
);


-- A content item: the exact publication or message proposed for a channel.
CREATE TABLE IF NOT EXISTS marketing_content (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   campaign_id INTEGER NOT NULL,
   channel_id INTEGER,
   title VARCHAR(255) NOT NULL,
   body TEXT,
   destination_url VARCHAR(1000),
   call_to_action VARCHAR(500),
   recipient_scope VARCHAR(255),
   scheduled_for TIMESTAMP WITHOUT TIME ZONE,
   status INTEGER,
   author_type INTEGER,
   revision_number INTEGER,
   content_hash VARCHAR(64),
   agent_run_id INTEGER,
   owner_user_id VARCHAR(100),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_content PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_content_product_id ON marketing_content (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_content_campaign_id ON marketing_content (campaign_id);
CREATE INDEX IF NOT EXISTS ix_marketing_content_channel_id ON marketing_content (channel_id);
CREATE INDEX IF NOT EXISTS ix_marketing_content_agent_run_id ON marketing_content (agent_run_id);

-- An immutable revision of a content item (written on every relevant edit).
CREATE TABLE IF NOT EXISTS marketing_content_revision (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   content_id INTEGER NOT NULL,
   revision_number INTEGER NOT NULL,
   title VARCHAR(255),
   body TEXT,
   destination_url VARCHAR(1000),
   call_to_action VARCHAR(500),
   recipient_scope VARCHAR(255),
   scheduled_for TIMESTAMP WITHOUT TIME ZONE,
   channel_id INTEGER,
   content_hash VARCHAR(64) NOT NULL,
   author_type INTEGER,
   author_user_id VARCHAR(100),
   agent_run_id INTEGER,
   change_note VARCHAR(500),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_content_revision PRIMARY KEY (id),
   CONSTRAINT uq_marketing_content_revision_1 UNIQUE (content_id, revision_number)
);

CREATE INDEX IF NOT EXISTS ix_marketing_content_revision_product_id ON marketing_content_revision (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_content_revision_content_id ON marketing_content_revision (content_id);
CREATE INDEX IF NOT EXISTS ix_marketing_content_revision_channel_id ON marketing_content_revision (channel_id);
CREATE INDEX IF NOT EXISTS ix_marketing_content_revision_agent_run_id ON marketing_content_revision (agent_run_id);

-- A human approval of one exact content revision, destination, recipient scope and schedule.
CREATE TABLE IF NOT EXISTS marketing_approval (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   content_id INTEGER NOT NULL,
   revision_id INTEGER,
   revision_number INTEGER,
   content_hash VARCHAR(64) NOT NULL,
   channel_id INTEGER,
   destination_url VARCHAR(1000),
   recipient_scope VARCHAR(255),
   scheduled_for TIMESTAMP WITHOUT TIME ZONE,
   status INTEGER NOT NULL,
   approved_by_user_id VARCHAR(100),
   approved_at TIMESTAMP WITHOUT TIME ZONE,
   note VARCHAR(500),
   invalidated_at TIMESTAMP WITHOUT TIME ZONE,
   invalidated_reason VARCHAR(500),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_approval PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_approval_product_id ON marketing_approval (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_approval_content_id ON marketing_approval (content_id);
CREATE INDEX IF NOT EXISTS ix_marketing_approval_revision_id ON marketing_approval (revision_id);
CREATE INDEX IF NOT EXISTS ix_marketing_approval_channel_id ON marketing_approval (channel_id);

-- A durable execution job: one approved content item to one channel transport.
CREATE TABLE IF NOT EXISTS marketing_execution (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   campaign_id INTEGER,
   content_id INTEGER NOT NULL,
   approval_id INTEGER NOT NULL,
   revision_id INTEGER,
   channel_id INTEGER,
   transport_name VARCHAR(64),
   scheduled_for TIMESTAMP WITHOUT TIME ZONE,
   status INTEGER NOT NULL,
   idempotency_key VARCHAR(200) NOT NULL,
   attempt_count INTEGER,
   max_attempts INTEGER,
   next_attempt_at TIMESTAMP WITHOUT TIME ZONE,
   last_attempt_at TIMESTAMP WITHOUT TIME ZONE,
   completed_at TIMESTAMP WITHOUT TIME ZONE,
   provider_receipt_id VARCHAR(255),
   status_detail TEXT,
   requested_by_user_id VARCHAR(100),
   is_test_data BOOLEAN,
   lease_owner VARCHAR(100),
   lease_expires_at TIMESTAMP WITHOUT TIME ZONE,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_execution PRIMARY KEY (id),
   CONSTRAINT uq_marketing_execution_1 UNIQUE (idempotency_key)
);

CREATE INDEX IF NOT EXISTS ix_marketing_execution_product_id ON marketing_execution (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_execution_campaign_id ON marketing_execution (campaign_id);
CREATE INDEX IF NOT EXISTS ix_marketing_execution_content_id ON marketing_execution (content_id);
CREATE INDEX IF NOT EXISTS ix_marketing_execution_approval_id ON marketing_execution (approval_id);
CREATE INDEX IF NOT EXISTS ix_marketing_execution_revision_id ON marketing_execution (revision_id);
CREATE INDEX IF NOT EXISTS ix_marketing_execution_channel_id ON marketing_execution (channel_id);

-- One attempt to run an execution, with the provider's receipt or failure.
CREATE TABLE IF NOT EXISTS marketing_execution_attempt (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   execution_id INTEGER NOT NULL,
   attempt_number INTEGER,
   started_at TIMESTAMP WITHOUT TIME ZONE,
   finished_at TIMESTAMP WITHOUT TIME ZONE,
   outcome INTEGER NOT NULL,
   recipient_count INTEGER,
   provider_receipt_id VARCHAR(255),
   provider_status VARCHAR(100),
   message TEXT,
   duration_ms BIGINT,
   worker_name VARCHAR(100),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_execution_attempt PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_execution_attempt_product_id ON marketing_execution_attempt (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_execution_attempt_execution_id ON marketing_execution_attempt (execution_id);

-- What the controlled test transport accepted: the exact payload, never sent anywhere.
CREATE TABLE IF NOT EXISTS marketing_test_delivery (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   execution_id INTEGER,
   idempotency_key VARCHAR(200) NOT NULL,
   recipient_key VARCHAR(200),
   channel_name VARCHAR(100),
   title VARCHAR(255),
   body TEXT,
   destination_url VARCHAR(1000),
   receipt_id VARCHAR(100),
   accepted_at TIMESTAMP WITHOUT TIME ZONE,
   duplicate_count INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_test_delivery PRIMARY KEY (id),
   CONSTRAINT uq_marketing_test_delivery_1 UNIQUE (idempotency_key, recipient_key)
);

CREATE INDEX IF NOT EXISTS ix_marketing_test_delivery_product_id ON marketing_test_delivery (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_test_delivery_execution_id ON marketing_test_delivery (execution_id);

-- A run of the marketing agent: task, status, provider, model usage and outputs.
CREATE TABLE IF NOT EXISTS marketing_agent_run (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   campaign_id INTEGER,
   content_id INTEGER,
   task INTEGER NOT NULL,
   status INTEGER NOT NULL,
   instructions TEXT,
   provider_name VARCHAR(64),
   model VARCHAR(100),
   requested_by_user_id VARCHAR(100),
   started_at TIMESTAMP WITHOUT TIME ZONE,
   heartbeat_at TIMESTAMP WITHOUT TIME ZONE,
   finished_at TIMESTAMP WITHOUT TIME ZONE,
   model_calls INTEGER,
   tool_calls INTEGER,
   input_tokens BIGINT,
   output_tokens BIGINT,
   cache_read_tokens BIGINT,
   cost_micros BIGINT,
   drafts_created INTEGER,
   findings_created INTEGER,
   summary TEXT,
   output_json TEXT,
   error_message TEXT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_agent_run PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_agent_run_product_id ON marketing_agent_run (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_agent_run_campaign_id ON marketing_agent_run (campaign_id);
CREATE INDEX IF NOT EXISTS ix_marketing_agent_run_content_id ON marketing_agent_run (content_id);

-- One step of an agent run: a model call, a tool call or a deterministic check.
CREATE TABLE IF NOT EXISTS marketing_agent_step (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   agent_run_id INTEGER NOT NULL,
   seq INTEGER,
   kind INTEGER NOT NULL,
   tool_name VARCHAR(100),
   outcome VARCHAR(32),
   summary VARCHAR(1000),
   input_json TEXT,
   result_json TEXT,
   input_tokens BIGINT,
   output_tokens BIGINT,
   duration_ms BIGINT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_agent_step PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_agent_step_product_id ON marketing_agent_step (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_agent_step_agent_run_id ON marketing_agent_step (agent_run_id);

-- A finding for a person to act on: unsupported claims, stale sources, gaps, limitations, recommendations.
CREATE TABLE IF NOT EXISTS marketing_finding (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   campaign_id INTEGER,
   content_id INTEGER,
   claim_id INTEGER,
   source_id INTEGER,
   agent_run_id INTEGER,
   finding_type INTEGER NOT NULL,
   severity INTEGER NOT NULL,
   status INTEGER NOT NULL,
   title VARCHAR(255) NOT NULL,
   detail TEXT,
   recommendation TEXT,
   origin VARCHAR(32),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_finding PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_finding_product_id ON marketing_finding (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_finding_campaign_id ON marketing_finding (campaign_id);
CREATE INDEX IF NOT EXISTS ix_marketing_finding_content_id ON marketing_finding (content_id);
CREATE INDEX IF NOT EXISTS ix_marketing_finding_claim_id ON marketing_finding (claim_id);
CREATE INDEX IF NOT EXISTS ix_marketing_finding_source_id ON marketing_finding (source_id);
CREATE INDEX IF NOT EXISTS ix_marketing_finding_agent_run_id ON marketing_finding (agent_run_id);

-- A campaign metric: its stage, the event it counts, how it counts and whether it is instrumented.
CREATE TABLE IF NOT EXISTS marketing_metric (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   campaign_id INTEGER NOT NULL,
   stage INTEGER NOT NULL,
   name VARCHAR(150) NOT NULL,
   event_type VARCHAR(100) NOT NULL,
   count_mode INTEGER NOT NULL,
   instrumentation_status INTEGER NOT NULL,
   target INTEGER,
   notes TEXT,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_metric PRIMARY KEY (id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_metric_product_id ON marketing_metric (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_metric_campaign_id ON marketing_metric (campaign_id);

-- A raw outcome event (deduplicated by source and external id), with its funnel stage and anonymous actor.
CREATE TABLE IF NOT EXISTS marketing_outcome_event (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   product_id INTEGER,
   campaign_id INTEGER,
   content_id INTEGER,
   execution_id INTEGER,
   stage INTEGER NOT NULL,
   event_type VARCHAR(100) NOT NULL,
   source VARCHAR(100) NOT NULL,
   external_event_id VARCHAR(200) NOT NULL,
   actor_key VARCHAR(200),
   occurred_at TIMESTAMP WITHOUT TIME ZONE,
   quantity INTEGER,
   is_test_data BOOLEAN,
   note VARCHAR(500),
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_outcome_event PRIMARY KEY (id),
   CONSTRAINT uq_marketing_outcome_event_1 UNIQUE (source, external_event_id)
);

CREATE INDEX IF NOT EXISTS ix_marketing_outcome_event_product_id ON marketing_outcome_event (product_id);
CREATE INDEX IF NOT EXISTS ix_marketing_outcome_event_campaign_id ON marketing_outcome_event (campaign_id);
CREATE INDEX IF NOT EXISTS ix_marketing_outcome_event_content_id ON marketing_outcome_event (content_id);
CREATE INDEX IF NOT EXISTS ix_marketing_outcome_event_execution_id ON marketing_outcome_event (execution_id);

-- A persisted daily rollup of a campaign's outcome events per stage and event type (system-written, recomputable from the raw events).
CREATE TABLE IF NOT EXISTS marketing_metric_daily (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   campaign_id INTEGER NOT NULL,
   product_id INTEGER,
   day_utc DATE NOT NULL,
   funnel_stage INTEGER NOT NULL,
   event_type VARCHAR(100) NOT NULL,
   is_test_data BOOLEAN NOT NULL,
   raw_count INTEGER,
   unique_actors INTEGER,
   events_without_actor INTEGER,
   recomputed_at TIMESTAMP WITHOUT TIME ZONE,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_metric_daily PRIMARY KEY (id),
   CONSTRAINT uq_marketing_metric_daily_1 UNIQUE (campaign_id, day_utc, funnel_stage, event_type, is_test_data)
);

CREATE INDEX IF NOT EXISTS ix_marketing_metric_daily_campaign_id ON marketing_metric_daily (campaign_id);
CREATE INDEX IF NOT EXISTS ix_marketing_metric_daily_product_id ON marketing_metric_daily (product_id);

-- Runtime switches and limits: agent and execution kill switches, provider, model, budgets, concurrency.
CREATE TABLE IF NOT EXISTS marketing_policy (
   id INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
   name VARCHAR(64) NOT NULL,
   agent_enabled BOOLEAN,
   execution_enabled BOOLEAN,
   kill_switch_reason VARCHAR(255),
   provider_name VARCHAR(64),
   model VARCHAR(100),
   daily_token_limit BIGINT,
   monthly_cost_limit_micros BIGINT,
   max_concurrent_agent_runs INTEGER,
   max_tool_calls_per_run INTEGER,
   create_date TIMESTAMP WITHOUT TIME ZONE,
   modify_date TIMESTAMP WITHOUT TIME ZONE,
   CONSTRAINT pk_marketing_policy PRIMARY KEY (id),
   CONSTRAINT uq_marketing_policy_1 UNIQUE (name)
);