nettown_backend/migrations/migrations_v1.sql

-- ----------------------------------------------------------------
-- 1. CORE ENTITIES
-- ----------------------------------------------------------------

CREATE TABLE company (
    company_id                         SERIAL PRIMARY KEY,
    company_name                       VARCHAR(100) NOT NULL,
    company_cnpj                       VARCHAR(14)  NOT NULL UNIQUE,
    company_logo                       TEXT NOT NULL,
    company_created_at                 TIMESTAMP NOT NULL DEFAULT NOW(),
    company_deleted_at                 TIMESTAMP NOT NULL DEFAULT 'infinity'
);


CREATE TABLE "user" (
    user_id                            SERIAL PRIMARY KEY,
    company_id                         INT NOT NULL,
    user_name                          VARCHAR(100) NOT NULL,
    user_phone                         VARCHAR(20) NOT NULL,
    user_email                         VARCHAR(100) NOT NULL UNIQUE,
    user_role                          VARCHAR(10) NOT NULL,
    user_password                      VARCHAR(255) NOT NULL,
    user_created_at                    TIMESTAMP NOT NULL DEFAULT NOW(),
    user_deleted_at                    TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_users_company FOREIGN KEY (company_id) REFERENCES company(company_id)
);

CREATE TABLE operator (
    operator_id                        SERIAL PRIMARY KEY,
    company_id                         INT NOT NULL,
    operator_name                      VARCHAR(100) NOT NULL,
    operator_initials                  VARCHAR(5) NOT NULL,
    operator_email                     VARCHAR(100) NOT NULL,
    operator_phone                     VARCHAR(20) NOT NULL,
    operator_department                VARCHAR(20) NOT NULL,
    operator_status                    VARCHAR(30) NOT NULL DEFAULT 'Disponível',
    operator_available_for_escalation  BOOLEAN NOT NULL DEFAULT TRUE,
    operator_created_at                TIMESTAMP NOT NULL DEFAULT NOW(),
    operator_deleted_at                TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_operators_company FOREIGN KEY (company_id) REFERENCES company(company_id)
);

CREATE TABLE operator_channel (
    operator_channel_id                SERIAL PRIMARY KEY,
    operator_id                        INT NOT NULL,
    operator_channel                   VARCHAR(20) NOT NULL,
    operator_channel_created_at        TIMESTAMP NOT NULL DEFAULT NOW(),
    operator_channel_deleted_at        TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_op_channel_operator FOREIGN KEY (operator_id) REFERENCES operator(operator_id)
);

CREATE TABLE sla_config (
    sla_config_id                      SERIAL PRIMARY KEY,
    company_id                         INT NOT NULL,
    sla_config_department              VARCHAR(20) NOT NULL,
    sla_config_response_hours          INT NOT NULL DEFAULT 2,
    sla_config_resolution_hours        INT NOT NULL DEFAULT 24,
    sla_config_updated_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    sla_config_deleted_at              TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_sla_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT uq_sla_dept UNIQUE (company_id, sla_config_department)
);

CREATE TABLE sku (
    sku_id                             SERIAL PRIMARY KEY,
    company_id                         INT NOT NULL,
    sku_name                           VARCHAR(100) NOT NULL,
    sku_value                          DECIMAL(12,2) NOT NULL,
    sku_sold                           INT NOT NULL DEFAULT 0,
    sku_line                           VARCHAR(50) NOT NULL,
    sku_created_at                     TIMESTAMP NOT NULL DEFAULT NOW(),
    sku_deleted_at                     TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_skus_company FOREIGN KEY (company_id) REFERENCES company(company_id)
);

CREATE TABLE integration (
    integration_id                     SERIAL PRIMARY KEY,
    company_id                         INT NOT NULL,
    integration_provider               VARCHAR(30) NOT NULL,
    integration_account_id             TEXT NOT NULL,
    integration_external_account_id    TEXT NOT NULL,
    integration_account_name           VARCHAR(100) NOT NULL,
    integration_status                 VARCHAR(20) NOT NULL,
    integration_access_token           TEXT NOT NULL,
    integration_refresh_token          TEXT NOT NULL,
    integration_is_connected           BOOLEAN NOT NULL DEFAULT TRUE,
    integration_last_sync_at           TIMESTAMP NOT NULL,
    integration_last_error             TEXT NOT NULL,
    integration_created_at             TIMESTAMP NOT NULL DEFAULT NOW(),
    integration_updated_at             TIMESTAMP NOT NULL DEFAULT NOW(),
    integration_deleted_at             TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_integrations_company FOREIGN KEY (company_id) REFERENCES company(company_id)
);


-- ----------------------------------------------------------------
-- 2. CLIENTS & CONVERSATIONS
-- ----------------------------------------------------------------

CREATE TABLE client (
    client_id                          SERIAL PRIMARY KEY,
    company_id                         INT NOT NULL,
    client_provider_id                 TEXT NOT NULL,
    client_phone                       VARCHAR(20) NOT NULL,
    client_name                        VARCHAR(100) NOT NULL,
    client_email                       VARCHAR(100) NOT NULL,
    client_segment                     VARCHAR(100) NOT NULL,
    client_is_registered               BOOLEAN NOT NULL DEFAULT FALSE,
    client_created_at                  TIMESTAMP NOT NULL DEFAULT NOW(),
    client_deleted_at                  TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_clients_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT uq_client_phone_company UNIQUE (company_id, client_phone)
);

CREATE TABLE conversation (
    conversation_id                    SERIAL PRIMARY KEY,
    company_id                         INT NOT NULL,
    integration_id                     INT NOT NULL,
    operator_id                        INT NOT NULL,
    client_id                          INT NOT NULL,
    conversation_external_id           TEXT NOT NULL,
    conversation_provider_id           TEXT NOT NULL,
    conversation_channel               VARCHAR(20) NOT NULL,
    conversation_status                VARCHAR(20) NOT NULL DEFAULT 'open',
    conversation_is_automated          BOOLEAN NOT NULL DEFAULT FALSE,
    conversation_started_at            TIMESTAMP NOT NULL DEFAULT NOW(),
    conversation_closed_at             TIMESTAMP NOT NULL,
    conversation_sla_deadline          TIMESTAMP NOT NULL,
    conversation_last_message_at       TIMESTAMP NOT NULL,
    conversation_last_message_preview  TEXT NOT NULL,
    conversation_last_message_from     VARCHAR(10) NOT NULL,
    conversation_impact_value          DECIMAL(12,2) NOT NULL,
    conversation_ticket_value          DECIMAL(12,2) NOT NULL,
    conversation_conversion_chance     INT NOT NULL,
    conversation_optimum_window        VARCHAR(20) NOT NULL,
    conversation_deleted_at            TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_conv_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT fk_conv_integration FOREIGN KEY (integration_id) REFERENCES integration(integration_id),
    CONSTRAINT fk_conv_operator FOREIGN KEY (operator_id) REFERENCES operator(operator_id),
    CONSTRAINT fk_conv_client FOREIGN KEY (client_id) REFERENCES client(client_id)
);

CREATE TABLE message (
    message_id                         SERIAL PRIMARY KEY,
    conversation_id                    INT NOT NULL,
    quoted_message_id                  INT NOT NULL,
    message_external_id                TEXT NOT NULL,
    message_provider_id                TEXT NOT NULL,
    message_sender_provider_id         TEXT NOT NULL,
    message_is_operator                BOOLEAN NOT NULL,
    message_type                       VARCHAR(20) NOT NULL DEFAULT 'text',
    message_content                    TEXT NOT NULL,
    message_seen                       BOOLEAN NOT NULL,
    message_delivered                  BOOLEAN NOT NULL,
    message_edited                     BOOLEAN NOT NULL DEFAULT FALSE,
    message_deleted                    BOOLEAN NOT NULL DEFAULT FALSE,
    message_hidden                     BOOLEAN NOT NULL DEFAULT FALSE,
    message_is_event                   BOOLEAN NOT NULL DEFAULT FALSE,
    message_event_type                 INT NOT NULL,
    message_sent_at                    TIMESTAMP NOT NULL DEFAULT NOW(),
    message_deleted_at                 TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_msg_conversation FOREIGN KEY (conversation_id) REFERENCES conversation(conversation_id),
    CONSTRAINT fk_quoted_message FOREIGN KEY (quoted_message_id) REFERENCES message(message_id)
);

CREATE TABLE message_attachment (
    attachment_id                      SERIAL PRIMARY KEY,
    message_id                         INT NOT NULL,
    attachment_external_id             TEXT NOT NULL,
    attachment_url                     TEXT NOT NULL,
    attachment_type                    VARCHAR(50) NOT NULL,
    attachment_mime_type               VARCHAR(100) NOT NULL,
    attachment_file_name               VARCHAR(255) NOT NULL,
    attachment_size                    BIGINT NOT NULL,
    attachment_created_at              TIMESTAMP NOT NULL DEFAULT NOW(),
    attachment_deleted_at              TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_attachment_message FOREIGN KEY (message_id) REFERENCES message(message_id)
);

CREATE TABLE message_reaction (
    reaction_id                        SERIAL PRIMARY KEY,
    message_id                         INT NOT NULL,
    reaction_sender_provider_id        TEXT NOT NULL,
    reaction_value                     VARCHAR(20) NOT NULL,
    reaction_is_sender                 BOOLEAN NOT NULL DEFAULT FALSE,
    reaction_created_at                TIMESTAMP NOT NULL DEFAULT NOW(),
    reaction_deleted_at                TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_reaction_message FOREIGN KEY (message_id) REFERENCES message(message_id)
);

CREATE TABLE conversation_participant (
    participant_id                     SERIAL PRIMARY KEY,
    conversation_id                    INT NOT NULL,
    participant_provider_id            TEXT NOT NULL,
    participant_name                   VARCHAR(100) NOT NULL,
    participant_is_admin               BOOLEAN NOT NULL DEFAULT FALSE,
    participant_created_at             TIMESTAMP NOT NULL DEFAULT NOW(),
    participant_deleted_at             TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_participant_conversation FOREIGN KEY (conversation_id) REFERENCES conversation(conversation_id)
);

CREATE TABLE webhook_event (
    webhook_event_id                   SERIAL PRIMARY KEY,
    integration_id                     INT NOT NULL,
    webhook_event_type                 VARCHAR(50) NOT NULL,
    webhook_event_external_id          TEXT NOT NULL,
    webhook_event_payload              JSONB NOT NULL,
    webhook_event_processed            BOOLEAN NOT NULL DEFAULT FALSE,
    webhook_event_received_at          TIMESTAMP NOT NULL DEFAULT NOW(),
    webhook_event_processed_at         TIMESTAMP NOT NULL,
    webhook_event_deleted_at           TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_webhook_integration FOREIGN KEY (integration_id) REFERENCES integration(integration_id)
);


-- ----------------------------------------------------------------
-- 3. AI-PROCESSED DATA
-- ----------------------------------------------------------------

CREATE TABLE conversation_analysis (
    analysis_id                                SERIAL PRIMARY KEY,
    conversation_id                            INT NOT NULL UNIQUE,
    company_id                                 INT NOT NULL,
    
    conversation_analysis_sentiment            VARCHAR(20) NOT NULL,
    conversation_analysis_sentiment_score      NUMERIC(3,2) NOT NULL,
    conversation_analysis_aspect               VARCHAR(50) NOT NULL,
    conversation_analysis_sub_aspect           VARCHAR(100) NOT NULL,
    conversation_analysis_analyzed_at          TIMESTAMP NOT NULL DEFAULT NOW(),
    conversation_analysis_deleted_at           TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_analysis_conv FOREIGN KEY (conversation_id) REFERENCES conversation(conversation_id),
    CONSTRAINT fk_analysis_company FOREIGN KEY (company_id) REFERENCES company(company_id)
);

CREATE TABLE aspect_feedback (
    aspect_feedback_id                         SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    
    conversation_id                            INT NOT NULL,
    aspect_feedback_aspect                     VARCHAR(50) NOT NULL,
    aspect_feedback_sentiment                  VARCHAR(20) NOT NULL,
    aspect_feedback_text                       TEXT NOT NULL,
    aspect_feedback_created_at                 TIMESTAMP NOT NULL DEFAULT NOW(),
    aspect_feedback_deleted_at                 TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_af_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT fk_af_conv FOREIGN KEY (conversation_id) REFERENCES conversation(conversation_id)
);

CREATE TABLE emotion_snapshot (
    emotion_id                                 SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    emotion_snapshot_date                      DATE NOT NULL,
    emotion_happiness                          NUMERIC(5,2) NOT NULL DEFAULT 0,
    emotion_sadness                            NUMERIC(5,2) NOT NULL DEFAULT 0,
    emotion_anger                              NUMERIC(5,2) NOT NULL DEFAULT 0,
    emotion_fear                               NUMERIC(5,2) NOT NULL DEFAULT 0,
    emotion_anticipation                       NUMERIC(5,2) NOT NULL DEFAULT 0,
    emotion_confidence                         NUMERIC(5,2) NOT NULL DEFAULT 0,
    emotion_surprise                           NUMERIC(5,2) NOT NULL DEFAULT 0,
    emotion_total_analyzed                     INT NOT NULL DEFAULT 0,
    CONSTRAINT fk_emotion_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT uq_emotion_date UNIQUE (company_id, emotion_snapshot_date)
);

CREATE TABLE public_opinion (
    opinion_id                                 SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    conversation_id                            INT NOT NULL,
    opinion_is_positive                        BOOLEAN NOT NULL,
    opinion_classified_at                      TIMESTAMP NOT NULL DEFAULT NOW(),
    opinion_deleted_at                         TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_opinion_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT fk_opinion_conv FOREIGN KEY (conversation_id) REFERENCES conversation(conversation_id)
);

CREATE TABLE alert (
    alert_id                                   SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    client_id                                  INT NOT NULL,
    alert_type                                 VARCHAR(20) NOT NULL,
    alert_priority                             VARCHAR(10) NOT NULL,
    alert_title                                TEXT NOT NULL,
    alert_description                          TEXT NOT NULL,
    alert_tips                                 TEXT NOT NULL,
    alert_is_resolved                          BOOLEAN NOT NULL DEFAULT FALSE,
    alert_created_at                           TIMESTAMP NOT NULL DEFAULT NOW(),
    alert_deleted_at                           TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_alert_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT fk_alert_client FOREIGN KEY (client_id) REFERENCES client(client_id)
);

CREATE TABLE ai_action (
    ai_action_id                               SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    ai_action_idea                             TEXT NOT NULL,
    ai_action_is_accepted                      BOOLEAN NOT NULL,
    ai_action_created_at                       TIMESTAMP NOT NULL DEFAULT NOW(),
    ai_action_responded_at                     TIMESTAMP NOT NULL,
    ai_action_deleted_at                       TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_ai_action_company FOREIGN KEY (company_id) REFERENCES company(company_id)
);


-- ----------------------------------------------------------------
-- 4. PERSONAS & STRATEGIES
-- ----------------------------------------------------------------

CREATE TABLE persona (
    persona_id                                 SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    persona_name                               VARCHAR(100) NOT NULL,
    persona_type                               VARCHAR(50) NOT NULL DEFAULT 'O PERFIL',
    persona_description                        TEXT NOT NULL,
    persona_details                            TEXT NOT NULL,
    persona_risk_level                         VARCHAR(10) NOT NULL DEFAULT 'Médio',
    persona_churn_risk_pct                     NUMERIC(5,2) NOT NULL,
    persona_expansion_potential                VARCHAR(20) NOT NULL,
    persona_expansion_strategy                 TEXT NOT NULL,
    persona_engagement_strategy                TEXT NOT NULL,
    persona_created_at                         TIMESTAMP NOT NULL DEFAULT NOW(),
    persona_deleted_at                         TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_personas_company FOREIGN KEY (company_id) REFERENCES company(company_id)
);

CREATE TABLE client_persona (
    client_persona_id                          SERIAL PRIMARY KEY,
    client_id                                  INT NOT NULL,
    persona_id                                 INT NOT NULL,
    client_persona_assigned_at                 TIMESTAMP NOT NULL DEFAULT NOW(),
    client_persona_deleted_at                  TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_cp_client FOREIGN KEY (client_id) REFERENCES client(client_id),
    CONSTRAINT fk_cp_persona FOREIGN KEY (persona_id) REFERENCES persona(persona_id)
);

CREATE TABLE best_action (
    best_action_id                             SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    persona_id                                 INT NOT NULL,
    best_action_type                           VARCHAR(20) NOT NULL,
    best_action_idea                           TEXT NOT NULL,
    best_action_created_at                     TIMESTAMP NOT NULL DEFAULT NOW(),
    best_action_deleted_at                     TIMESTAMP NOT NULL DEFAULT 'infinity',
    CONSTRAINT fk_ba_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT fk_ba_persona FOREIGN KEY (persona_id) REFERENCES persona(persona_id)
);


-- ----------------------------------------------------------------
-- 5. TIME-SERIES / SNAPSHOTS
-- ----------------------------------------------------------------

CREATE TABLE volume_snapshot (
    volume_id                                  SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    volume_snapshot_date                       DATE NOT NULL,
    volume_channel                             VARCHAR(20) NOT NULL,
    volume_message_count                       INT NOT NULL DEFAULT 0,
    volume_conversation_count                  INT NOT NULL DEFAULT 0,
    CONSTRAINT fk_volume_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT uq_volume_date_channel UNIQUE (company_id, volume_snapshot_date, volume_channel)
);

CREATE TABLE sentiment_evolution (
    evolution_id                               SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    evolution_snapshot_date                    DATE NOT NULL,
    evolution_sentiment_score                  NUMERIC(3,2) NOT NULL,
    CONSTRAINT fk_evo_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT uq_evo_date UNIQUE (company_id, evolution_snapshot_date)
);

CREATE TABLE playbooks_monitor (
    playbook_id                                SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    playbook_snapshot_date                     DATE NOT NULL,
    playbook_new_detected                      INT NOT NULL DEFAULT 0,
    playbook_converted                         INT NOT NULL DEFAULT 0,
    playbook_client_type                       VARCHAR(10) NOT NULL DEFAULT 'new',
    CONSTRAINT fk_pb_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT uq_pb_date UNIQUE (company_id, playbook_snapshot_date)
);

CREATE TABLE operator_daily_stats (
    stat_id                                    SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    operator_id                                INT NOT NULL,
    operator_stat_date                         DATE NOT NULL,
    operator_attendances_count                 INT NOT NULL DEFAULT 0,
    operator_avg_response_seconds              INT NOT NULL DEFAULT 0,
    operator_sla_compliance_pct                NUMERIC(5,2) NOT NULL DEFAULT 0,
    operator_sales_amount                      DECIMAL(12,2) NOT NULL DEFAULT 0,
    CONSTRAINT fk_stats_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT fk_stats_operator FOREIGN KEY (operator_id) REFERENCES operator(operator_id),
    CONSTRAINT uq_stats_op_date UNIQUE (operator_id, operator_stat_date)
);

CREATE TABLE kpi_snapshot (
    kpi_id                                     SERIAL PRIMARY KEY,
    company_id                                 INT NOT NULL,
    kpi_snapshot_date                          DATE NOT NULL,
    kpi_current_sales                          DECIMAL(12,2) NOT NULL DEFAULT 0,
    kpi_avg_ticket                             DECIMAL(12,2) NOT NULL DEFAULT 0,
    kpi_lifetime_at_risk                       DECIMAL(12,2) NOT NULL DEFAULT 0,
    kpi_critical_risk_clients                  INT NOT NULL DEFAULT 0,
    kpi_sla_compliance_pct                     NUMERIC(5,2) NOT NULL DEFAULT 0,
    kpi_churn_low_pct                          NUMERIC(5,2) NOT NULL DEFAULT 0,
    kpi_churn_moderate_pct                     NUMERIC(5,2) NOT NULL DEFAULT 0,
    kpi_churn_high_pct                         NUMERIC(5,2) NOT NULL DEFAULT 0,
    kpi_churn_critical_pct                     NUMERIC(5,2) NOT NULL DEFAULT 0,
    kpi_ltv_total                              DECIMAL(12,2) NOT NULL DEFAULT 0,
    kpi_ltv_at_risk                            DECIMAL(12,2) NOT NULL DEFAULT 0,
    kpi_total_registered_clients               INT NOT NULL DEFAULT 0,
    kpi_total_active_operators                 INT NOT NULL DEFAULT 0,
    kpi_general_emotion                        VARCHAR(20) NOT NULL,
    CONSTRAINT fk_kpi_company FOREIGN KEY (company_id) REFERENCES company(company_id),
    CONSTRAINT uq_kpi_date UNIQUE (company_id, kpi_snapshot_date)
);