-- Migration 0014: create_crm_module_extensions
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 14 — CRM MODULE EXTENSIONS (Phase 3)
-- Additive only. `comments` is new (lighter-weight, threaded, distinct
-- from the longer-form `notes` table from Phase 1). `appointments`,
-- `leads`, and `customers` gain nullable columns for recurring
-- scheduling, lead priority/duplicate-merge tracking, and CSV import
-- provenance — none of it breaks existing rows.
-- =====================================================================

-- Short, often-threaded remarks on any entity — distinct from `notes`
-- (Phase 1), which is for longer-form structured notes. A lead's
-- "Activity Timeline" mixes both; Comments are what a quick "@Rita can
-- you follow up on this?" becomes.
CREATE TABLE IF NOT EXISTS comments (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type  VARCHAR(40)     NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    parent_id    BIGINT UNSIGNED NULL COMMENT 'Set for a threaded reply to another comment',
    body         VARCHAR(2000)   NOT NULL,
    created_by   BIGINT UNSIGNED NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at   DATETIME        NULL,
    KEY idx_comments_entity (entity_type, entity_id),
    KEY idx_comments_parent (parent_id),
    CONSTRAINT fk_comments_parent FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_comments_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Recurring appointments: a "parent" appointment carries the
-- recurrence rule; each generated occurrence is its own real row
-- (so any single occurrence can be individually rescheduled/cancelled
-- without touching the series) linked back via parent_appointment_id.
ALTER TABLE appointments
    ADD COLUMN parent_appointment_id BIGINT UNSIGNED NULL AFTER lead_id,
    ADD COLUMN recurrence_rule JSON NULL COMMENT '{"frequency":"weekly","interval":1,"until":"2026-12-31","days_of_week":[2]} — only set on the parent row',
    ADD COLUMN is_recurring_parent TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN reminder_minutes_before SMALLINT UNSIGNED NULL DEFAULT 60,
    ADD COLUMN reminder_sent_at DATETIME NULL,
    ADD KEY idx_appointments_parent (parent_appointment_id),
    ADD CONSTRAINT fk_appointments_parent FOREIGN KEY (parent_appointment_id) REFERENCES appointments(id) ON DELETE CASCADE ON UPDATE CASCADE;

-- Lead Management: priority (already had stage/status via lead_stages;
-- this is urgency, independent of pipeline position), and duplicate/
-- merge bookkeeping so a merged-away lead stays queryable for audit
-- instead of being hard-deleted.
ALTER TABLE leads
    ADD COLUMN priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium' AFTER lead_stage_id,
    ADD COLUMN merged_into_lead_id BIGINT UNSIGNED NULL COMMENT 'Set when this lead was merged into another (the survivor)',
    ADD COLUMN import_batch_id CHAR(36) NULL COMMENT 'Groups rows created by the same CSV import run',
    ADD KEY idx_leads_priority (priority),
    ADD KEY idx_leads_merged_into (merged_into_lead_id),
    ADD KEY idx_leads_import_batch (import_batch_id),
    ADD CONSTRAINT fk_leads_merged_into FOREIGN KEY (merged_into_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE customers
    ADD COLUMN import_batch_id CHAR(36) NULL AFTER source_lead_id,
    ADD KEY idx_customers_import_batch (import_batch_id);

-- CSV import run log — one row per `php` or admin-triggered import,
-- so Users/Leads/Customers import screens can all show "last import:
-- 214 created, 3 skipped as duplicates, 2 failed" without re-parsing
-- the original file.
CREATE TABLE IF NOT EXISTS import_batches (
    id             CHAR(36)        NOT NULL COMMENT 'UUID, shared with leads.import_batch_id / customers.import_batch_id',
    entity_type    VARCHAR(40)     NOT NULL COMMENT 'leads, customers, companies...',
    original_filename VARCHAR(255) NULL,
    total_rows     INT UNSIGNED    NOT NULL DEFAULT 0,
    created_count  INT UNSIGNED    NOT NULL DEFAULT 0,
    skipped_count  INT UNSIGNED    NOT NULL DEFAULT 0,
    failed_count   INT UNSIGNED    NOT NULL DEFAULT 0,
    error_log      JSON            NULL COMMENT 'Array of {row, message} for failed/skipped rows',
    imported_by    BIGINT UNSIGNED NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_import_batches_entity (entity_type),
    CONSTRAINT fk_import_batches_user FOREIGN KEY (imported_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
