-- Phase 1 of the CRM upgrade program: embeddable dynamic quote forms. A form is a set of
-- field definitions a Tenant Admin authors; each public submission is resolved into a
-- Contact/Lead exclusively through ContactResolutionService (see contact_resolution.sql) —
-- this migration adds no direct write path into dim_contacts.
-- Idempotent (information_schema-guarded), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/quote_forms.sql

USE db_abos_v0.1;

CREATE TABLE IF NOT EXISTS dim_quote_forms (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    name VARCHAR(255) NOT NULL,
    -- A random, non-guessable token — the public submission endpoint looks the form up by this,
    -- not by its numeric id, so a form can be embedded without exposing tenant/row enumeration.
    public_token VARCHAR(40) NOT NULL,
    fields_json LONGTEXT NOT NULL COMMENT 'JSON array of {key,label,type,required}',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT(1) NOT NULL DEFAULT 0,
    UNIQUE KEY uq_quote_forms_public_token (public_token),
    INDEX idx_quote_forms_tenant (tenant_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- One row per public submission, kept regardless of whether it resolved to a new or existing
-- Lead/Contact — an unfiltered receipt of everything that came in through a given form.
CREATE TABLE IF NOT EXISTS fact_quote_submissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    form_id BIGINT NOT NULL,
    contact_id INT NULL,
    lead_id BIGINT NULL,
    submitted_data_json LONGTEXT NOT NULL COMMENT 'JSON map of field key -> submitted value, as-entered',
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_quote_submissions_tenant_form (tenant_id, form_id),
    INDEX idx_quote_submissions_lead (lead_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;