-- ============================================================
-- Migracja 012: Etap 9 - CRM-lite i obsluga leadow (SLA)
-- Tartak Dana CMS
-- Kompatybilnosc: MySQL 8.0
-- ============================================================


SET @db_name = DATABASE();

-- ------------------------------------------------------------
-- inquiries.status: nowy workflow (new -> in_progress -> replied -> closed)
-- Zachowujemy "read" tymczasowo dla zgodnosci wstecznej.
-- ------------------------------------------------------------
ALTER TABLE `inquiries`
    MODIFY COLUMN `status` ENUM('new','in_progress','read','replied','closed')
    NOT NULL DEFAULT 'new';

UPDATE `inquiries`
SET `status` = 'in_progress'
WHERE `status` = 'read';

-- ------------------------------------------------------------
-- CRM pola operacyjne
-- ------------------------------------------------------------
SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND COLUMN_NAME = 'assigned_user_id'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD COLUMN `assigned_user_id` INT UNSIGNED NULL AFTER `status`'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND COLUMN_NAME = 'sla_due_at'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD COLUMN `sla_due_at` DATETIME NULL AFTER `last_status_change_at`'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND COLUMN_NAME = 'first_response_at'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD COLUMN `first_response_at` DATETIME NULL AFTER `sla_due_at`'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND COLUMN_NAME = 'reaction_time_minutes'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD COLUMN `reaction_time_minutes` INT UNSIGNED NULL AFTER `first_response_at`'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.COLUMNS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND COLUMN_NAME = 'closed_at'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD COLUMN `closed_at` DATETIME NULL AFTER `reaction_time_minutes`'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND INDEX_NAME = 'idx_inquiries_assigned_user'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD INDEX `idx_inquiries_assigned_user` (`assigned_user_id`)'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND INDEX_NAME = 'idx_inquiries_sla_due'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD INDEX `idx_inquiries_sla_due` (`sla_due_at`)'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND INDEX_NAME = 'idx_inquiries_first_response'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD INDEX `idx_inquiries_first_response` (`first_response_at`)'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.STATISTICS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND INDEX_NAME = 'idx_inquiries_closed_at'
        ),
        'DO 0',
        'ALTER TABLE `inquiries` ADD INDEX `idx_inquiries_closed_at` (`closed_at`)'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql = (
    SELECT IF(
        EXISTS(
            SELECT 1
            FROM information_schema.TABLE_CONSTRAINTS
            WHERE TABLE_SCHEMA = @db_name
              AND TABLE_NAME = 'inquiries'
              AND CONSTRAINT_NAME = 'fk_inquiries_assigned_user'
              AND CONSTRAINT_TYPE = 'FOREIGN KEY'
        ),
        'DO 0',
        'ALTER TABLE `inquiries`
            ADD CONSTRAINT `fk_inquiries_assigned_user`
            FOREIGN KEY (`assigned_user_id`) REFERENCES `users`(`id`)
            ON DELETE SET NULL ON UPDATE CASCADE'
    )
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ------------------------------------------------------------
-- inquiry_events: pelna sciezka obslugi leadu (timeline)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `inquiry_events` (
    `id`            BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `inquiry_id`    INT UNSIGNED    NOT NULL,
    `event_type`    VARCHAR(32)     NOT NULL,
    `from_status`   VARCHAR(32)     NULL,
    `to_status`     VARCHAR(32)     NULL,
    `note_text`     VARCHAR(1000)   NULL,
    `actor_user_id` INT UNSIGNED    NULL,
    `meta_json`     VARCHAR(2000)   NULL,
    `created_at`    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    INDEX `idx_inquiry_events_inquiry_created` (`inquiry_id`, `created_at`),
    INDEX `idx_inquiry_events_actor_created` (`actor_user_id`, `created_at`),
    INDEX `idx_inquiry_events_type_created` (`event_type`, `created_at`),
    CONSTRAINT `fk_inquiry_events_inquiry`
        FOREIGN KEY (`inquiry_id`) REFERENCES `inquiries`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `fk_inquiry_events_actor`
        FOREIGN KEY (`actor_user_id`) REFERENCES `users`(`id`)
        ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `inquiry_events` (
    `inquiry_id`, `event_type`, `from_status`, `to_status`, `note_text`, `actor_user_id`, `meta_json`, `created_at`
)
SELECT
    i.`id`,
    'created',
    NULL,
    i.`status`,
    NULL,
    NULL,
    NULL,
    i.`created_at`
FROM `inquiries` i
LEFT JOIN `inquiry_events` e
    ON e.`inquiry_id` = i.`id` AND e.`event_type` = 'created'
WHERE e.`id` IS NULL;

-- ------------------------------------------------------------
-- Ustawienia CRM
-- ------------------------------------------------------------
INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
    ('crm_lead_sla_minutes', '60')
ON DUPLICATE KEY UPDATE
    `setting_value` = IF(TRIM(COALESCE(`setting_value`, '')) = '', VALUES(`setting_value`), `setting_value`);

