-- =============================================================================
-- web_forms_module.sql — Forms module for the Web CMS (dim_web_* family).
-- Adds dynamic form definitions + submission capture so public sites (e.g.
-- LarCare) can serve arbitrary forms (contact, sms-consent, ...) driven by
-- config instead of hardcoded backend routes.
--
-- Follows the exact tenant/business/site/audit column pattern used by the
-- other dim_web_* tables in projects_foundation_refactor.sql.
-- Safe to re-run: CREATE TABLE IF NOT EXISTS.
-- =============================================================================

-- ── dim_web_forms ────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_web_forms (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NOT NULL,
    form_key VARCHAR(100) NOT NULL,
    name VARCHAR(255) NULL,
    success_message VARCHAR(500) NULL,
    notify_emails VARCHAR(500) NULL,
    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 NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_web_forms_key (site_id, form_key),
    INDEX idx_dim_web_forms_tenant (tenant_id),
    INDEX idx_dim_web_forms_site (site_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── dim_web_form_fields ──────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_web_form_fields (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    form_id BIGINT NOT NULL,
    field_key VARCHAR(100) NOT NULL,
    label VARCHAR(255) NULL,
    field_type VARCHAR(50) NULL,
    is_required TINYINT(1) NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    config_json TEXT NULL,
    UNIQUE KEY uq_dim_web_form_fields (form_id, field_key),
    INDEX idx_dim_web_form_fields_form (form_id, sort_order),
    CONSTRAINT fk_dim_web_form_fields_form FOREIGN KEY (form_id) REFERENCES dim_web_forms (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── fact_web_form_submissions ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fact_web_form_submissions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    form_id BIGINT NOT NULL,
    site_id BIGINT NOT NULL,
    data_json TEXT NOT NULL,
    ip_hash VARCHAR(128) NULL,
    user_agent VARCHAR(500) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_fact_web_form_submissions_form (form_id, created_at),
    INDEX idx_fact_web_form_submissions_site (site_id, created_at),
    CONSTRAINT fk_fact_web_form_submissions_form FOREIGN KEY (form_id) REFERENCES dim_web_forms (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;