ALTER TABLE accounts
    ADD COLUMN IF NOT EXISTS parent_account_id BIGINT UNSIGNED NULL AFTER tenant_id,
    ADD COLUMN IF NOT EXISTS lead_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL AFTER updated_at,
    ADD CONSTRAINT fk_accounts_parent_account FOREIGN KEY (parent_account_id) REFERENCES accounts(id) ON DELETE SET NULL;

ALTER TABLE contacts
    ADD COLUMN IF NOT EXISTS lead_score TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER status,
    ADD COLUMN IF NOT EXISTS last_interaction_at DATETIME NULL AFTER owner_user_id,
    ADD COLUMN IF NOT EXISTS deleted_at DATETIME NULL AFTER updated_at;

ALTER TABLE contact_interactions
    ADD COLUMN IF NOT EXISTS outcome VARCHAR(80) NULL AFTER details;

ALTER TABLE contact_attachments
    ADD COLUMN IF NOT EXISTS visibility ENUM('internal', 'shared') NOT NULL DEFAULT 'internal' AFTER file_size;

CREATE TABLE IF NOT EXISTS account_contact_links (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NOT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_account_contact_link (tenant_id, account_id, contact_id),
    INDEX idx_account_contact_contact (tenant_id, contact_id, is_primary),
    CONSTRAINT fk_account_contact_link_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_account_contact_link_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    CONSTRAINT fk_account_contact_link_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NOT NULL,
    content TEXT NOT NULL,
    mentions_json JSON NULL,
    is_pinned TINYINT(1) NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_notes_contact (tenant_id, contact_id, created_at),
    CONSTRAINT fk_contact_notes_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_notes_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_notes_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_tasks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(190) NOT NULL,
    status ENUM('open', 'done', 'cancelled') NOT NULL DEFAULT 'open',
    due_at DATETIME NULL,
    completed_at DATETIME NULL,
    assigned_to BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_tasks_contact_status (tenant_id, contact_id, status, due_at),
    CONSTRAINT fk_contact_tasks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_tasks_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_tasks_assigned_to FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_contact_tasks_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_segments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    name VARCHAR(120) NOT NULL,
    criteria_json JSON NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_contact_segments_name (tenant_id, name),
    CONSTRAINT fk_contact_segments_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_segment_members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    segment_id BIGINT UNSIGNED NOT NULL,
    contact_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_contact_segment_member (tenant_id, segment_id, contact_id),
    INDEX idx_contact_segment_members_contact (tenant_id, contact_id),
    CONSTRAINT fk_contact_segment_members_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_segment_members_segment FOREIGN KEY (segment_id) REFERENCES contact_segments(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_segment_members_contact FOREIGN KEY (contact_id) REFERENCES contacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_dedupe_candidates (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    primary_contact_id BIGINT UNSIGNED NOT NULL,
    duplicate_contact_id BIGINT UNSIGNED NOT NULL,
    confidence_score TINYINT UNSIGNED NOT NULL DEFAULT 0,
    reason VARCHAR(120) NULL,
    status ENUM('pending', 'merged', 'ignored') NOT NULL DEFAULT 'pending',
    resolved_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_dedupe_tenant_status (tenant_id, status, confidence_score),
    CONSTRAINT fk_contact_dedupe_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_dedupe_primary FOREIGN KEY (primary_contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_dedupe_duplicate FOREIGN KEY (duplicate_contact_id) REFERENCES contacts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_merge_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    primary_contact_id BIGINT UNSIGNED NOT NULL,
    merged_contact_id BIGINT UNSIGNED NOT NULL,
    actor_user_id BIGINT UNSIGNED NULL,
    merged_payload JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_merge_tenant_created (tenant_id, created_at),
    CONSTRAINT fk_contact_merge_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_merge_primary FOREIGN KEY (primary_contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_merge_merged FOREIGN KEY (merged_contact_id) REFERENCES contacts(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_merge_actor FOREIGN KEY (actor_user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_import_jobs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    source_name VARCHAR(120) NOT NULL,
    status ENUM('running', 'finished', 'finished_with_errors', 'failed') NOT NULL DEFAULT 'running',
    success_rows INT UNSIGNED NOT NULL DEFAULT 0,
    failed_rows INT UNSIGNED NOT NULL DEFAULT 0,
    created_by BIGINT UNSIGNED NULL,
    finished_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_import_jobs_tenant (tenant_id, created_at),
    CONSTRAINT fk_contact_import_jobs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_import_jobs_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_import_rows (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    job_id BIGINT UNSIGNED NOT NULL,
    row_number INT UNSIGNED NOT NULL,
    payload_json JSON NULL,
    status ENUM('imported', 'failed', 'skipped') NOT NULL DEFAULT 'imported',
    error_message VARCHAR(255) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_import_rows_job (job_id, row_number),
    CONSTRAINT fk_contact_import_rows_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_contact_import_rows_job FOREIGN KEY (job_id) REFERENCES contact_import_jobs(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS contact_sync_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    provider VARCHAR(60) NOT NULL,
    entity_type VARCHAR(60) NOT NULL,
    external_id VARCHAR(120) NOT NULL,
    status VARCHAR(40) NOT NULL,
    message VARCHAR(255) NULL,
    payload_json JSON NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_contact_sync_logs_tenant (tenant_id, created_at),
    CONSTRAINT fk_contact_sync_logs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS account_health_snapshots (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT UNSIGNED NOT NULL,
    account_id BIGINT UNSIGNED NOT NULL,
    contacts_count INT UNSIGNED NOT NULL DEFAULT 0,
    open_opportunities INT UNSIGNED NOT NULL DEFAULT 0,
    revenue_open DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    avg_lead_score DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    snap_at DATETIME NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_account_health_tenant_account (tenant_id, account_id, snap_at),
    CONSTRAINT fk_account_health_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE,
    CONSTRAINT fk_account_health_account FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO contact_segments (tenant_id, name, criteria_json)
SELECT t.id, 'Clientes Activos', JSON_OBJECT('status', 'customer')
FROM tenants t
WHERE t.slug = 'demo'
ON DUPLICATE KEY UPDATE criteria_json = VALUES(criteria_json);
