-- =============================================================================
-- CFDI Matcher Links Table
-- =============================================================================
-- Purpose: Store matcher-specific metadata for invoice-to-deposit links
-- Created: 2026-01-15
-- Author: Claude (CFDI Matcher Enhancement)
--
-- This table works in conjunction with banco_cuenta_mov_link to provide:
-- 1. Match quality metrics (tier, confidence, pattern)
-- 2. Link lifecycle management (active, superseded, deleted)
-- 3. Iteration history tracking
-- 4. User verification workflow
-- =============================================================================

CREATE TABLE IF NOT EXISTS cfdi_matcher_links (
    -- Primary key
    cfdi_matcher_link_id        VARCHAR(36) PRIMARY KEY
        COMMENT 'UUID primary key',

    -- Relationship to generic link table
    banco_cuenta_mov_link_id    VARCHAR(32) NOT NULL
        COMMENT 'FK to banco_cuenta_mov_link - the actual production link',

    -- Core relationship
    iteration_id                INT NOT NULL
        COMMENT 'FK to cfdi_matcher_iterations - which iteration created/managed this link',
    invoice_id                  VARCHAR(36) NOT NULL
        COMMENT 'FK to eleyeme_cfdi_emitidos.eleyeme_cfdi_emitido_id',
    deposit_id                  VARCHAR(32) NOT NULL
        COMMENT 'FK to banco_cuenta_mov.banco_cuenta_mov_id',

    -- Match quality metrics (from matcher algorithm)
    match_tier                  DECIMAL(3,1) DEFAULT NULL
        COMMENT 'Matching tier: 0=exact, 0.5=estado-guided, 1-3=fuzzy',
    match_confidence            TINYINT UNSIGNED DEFAULT NULL
        COMMENT 'Confidence score 0-100',
    match_pattern               VARCHAR(150) DEFAULT NULL
        COMMENT 'Pattern that produced the match (e.g. estado_guided_SANTANDER)',
    match_explanation           TEXT DEFAULT NULL
        COMMENT 'Human-readable explanation of why this match was made',

    -- Link lifecycle management
    link_status                 ENUM('active', 'superseded', 'deleted') NOT NULL DEFAULT 'active'
        COMMENT 'Current status: active=in use, superseded=replaced by better match, deleted=removed',

    created_by_iteration        INT NOT NULL
        COMMENT 'Iteration number that first created this link',
    last_confirmed_iteration    INT DEFAULT NULL
        COMMENT 'Last iteration that confirmed this link is still valid',
    superseded_by_iteration     INT DEFAULT NULL
        COMMENT 'Iteration that replaced this link (if status=superseded)',
    deleted_by_iteration        INT DEFAULT NULL
        COMMENT 'Iteration that removed this link (if status=deleted)',

    -- User verification workflow
    user_verified               BOOLEAN NOT NULL DEFAULT FALSE
        COMMENT 'Has a human verified this match?',
    verified_by                 VARCHAR(32) DEFAULT NULL
        COMMENT 'User ID who verified',
    verified_at                 DATETIME DEFAULT NULL
        COMMENT 'When verification occurred',
    verification_notes          TEXT DEFAULT NULL
        COMMENT 'User notes about verification',

    -- Audit timestamps
    created_at                  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
        COMMENT 'When this record was created',
    updated_at                  DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP
        COMMENT 'Last modification timestamp',

    -- Indexes for performance
    INDEX idx_link_id (banco_cuenta_mov_link_id),
    INDEX idx_iteration (iteration_id),
    INDEX idx_invoice (invoice_id),
    INDEX idx_deposit (deposit_id),
    INDEX idx_status (link_status),
    INDEX idx_created_iteration (created_by_iteration),
    INDEX idx_confirmed_iteration (last_confirmed_iteration),
    INDEX idx_verified (user_verified),

    -- Composite indexes for common queries
    INDEX idx_invoice_status (invoice_id, link_status),
    INDEX idx_deposit_status (deposit_id, link_status),
    INDEX idx_iteration_status (iteration_id, link_status),

    -- Unique constraint: only one active link per invoice-deposit pair
    UNIQUE KEY unique_active_link (invoice_id, deposit_id, link_status)

) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci
  COMMENT='CFDI Matcher: Links between invoices and deposits with lifecycle management';

-- =============================================================================
-- Verification Query (run manually after table creation)
-- =============================================================================

-- DESCRIBE cfdi_matcher_links;
-- SHOW INDEXES FROM cfdi_matcher_links;
-- SELECT COUNT(*) FROM cfdi_matcher_links;
