-- ═══════════════════════════════════════════════════════════════════════
-- Sprint 11: Multi-tenant
-- Convierte el Service Desk en multi-empresa con Fycls como operadora
--
-- IMPORTANTE: Ejecutar BACKUP antes de correr este SQL
-- ═══════════════════════════════════════════════════════════════════════

START TRANSACTION;

-- ─────────────────────────────────────────────────────────────────────
-- 1. Crear tabla de empresas
-- ─────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `companies` (
    `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `slug` VARCHAR(50) NOT NULL,
    `legal_name` VARCHAR(200) DEFAULT NULL,
    `nit` VARCHAR(30) DEFAULT NULL,
    `domain` VARCHAR(100) DEFAULT NULL COMMENT 'Para auto-asignación por dominio de email',
    `logo_path` VARCHAR(255) DEFAULT NULL,
    `primary_color` VARCHAR(7) NOT NULL DEFAULT '#1d4ed8',
    `contact_email` VARCHAR(150) DEFAULT NULL,
    `contact_phone` VARCHAR(50) DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    `is_operator` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '1=empresa que opera el servicedesk (Fycls)',
    `settings` JSON DEFAULT NULL,
    `order` INT NOT NULL DEFAULT 0,
    `created_at` TIMESTAMP NULL DEFAULT NULL,
    `updated_at` TIMESTAMP NULL DEFAULT NULL,
    `deleted_at` TIMESTAMP NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `companies_slug_unique` (`slug`),
    KEY `companies_is_active_index` (`is_active`),
    KEY `companies_is_operator_index` (`is_operator`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─────────────────────────────────────────────────────────────────────
-- 2. Insertar las 4 empresas iniciales
-- ─────────────────────────────────────────────────────────────────────
INSERT INTO `companies` (`name`, `slug`, `legal_name`, `domain`, `primary_color`, `is_operator`, `is_active`, `order`, `contact_email`, `created_at`, `updated_at`)
VALUES
    ('Fycls',  'fycls',  'Fycls S.A.S.', 'fycls.com',    '#1d4ed8', 1, 1, 0, 'soporte@fycls.com', NOW(), NOW()),
    ('Fedef',  'fedef',  'Fedef',        'fedef-co.com', '#16a34a', 0, 1, 1, NULL, NOW(), NOW()),
    ('Gessig', 'gessig', 'Gessig',       'gessig.com',   '#0891b2', 0, 1, 2, NULL, NOW(), NOW()),
    ('Trexdi', 'trexdi', 'Trexdi',       'trexdi.com',   '#dc2626', 0, 1, 3, NULL, NOW(), NOW())
ON DUPLICATE KEY UPDATE updated_at=NOW();

-- ─────────────────────────────────────────────────────────────────────
-- 3. Agregar company_id a las tablas que se aíslan
-- ─────────────────────────────────────────────────────────────────────
-- Idempotente: usa ALTER IGNORE/IF NOT EXISTS donde sea posible

ALTER TABLE `users`             ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `users_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_users_company` (`company_id`);

ALTER TABLE `tickets`           ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `tickets_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_tickets_company` (`company_id`);

ALTER TABLE `ticket_categories` ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `ticket_categories_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_ticket_categories_company` (`company_id`);

ALTER TABLE `departments`       ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `departments_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_departments_company` (`company_id`);

ALTER TABLE `agent_groups`      ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `agent_groups_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_agent_groups_company` (`company_id`);

ALTER TABLE `kb_articles`       ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `kb_articles_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_kb_articles_company` (`company_id`);

ALTER TABLE `kb_categories`     ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `kb_categories_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_kb_categories_company` (`company_id`);

ALTER TABLE `assets`            ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `assets_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_assets_company` (`company_id`);

ALTER TABLE `asset_categories`  ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `asset_categories_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_asset_categories_company` (`company_id`);

ALTER TABLE `automation_rules`  ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `automation_rules_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_automation_rules_company` (`company_id`);

ALTER TABLE `reply_templates`   ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `reply_templates_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_reply_templates_company` (`company_id`);

ALTER TABLE `business_hours`    ADD COLUMN IF NOT EXISTS `company_id` BIGINT UNSIGNED NULL AFTER `id`,
                                ADD CONSTRAINT `business_hours_company_id_foreign` FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL,
                                ADD INDEX IF NOT EXISTS `idx_business_hours_company` (`company_id`);

-- ─────────────────────────────────────────────────────────────────────
-- 4. Verificación
-- ─────────────────────────────────────────────────────────────────────
SELECT 'Empresas creadas:' AS info;
SELECT id, name, slug, is_operator, is_active FROM companies ORDER BY `order`;

SELECT 'Tablas con company_id:' AS info;
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLUMN_NAME = 'company_id'
ORDER BY TABLE_NAME;

COMMIT;

-- ═══════════════════════════════════════════════════════════════════════
-- POST-INSTALACIÓN MANUAL
-- ═══════════════════════════════════════════════════════════════════════
-- Si tienes datos legacy del cliente original (Fedef), ejecuta:
--
--   UPDATE users           SET company_id = (SELECT id FROM companies WHERE slug='fedef') WHERE company_id IS NULL;
--   UPDATE tickets         SET company_id = (SELECT id FROM companies WHERE slug='fedef') WHERE company_id IS NULL;
--   UPDATE ticket_categories SET company_id = (SELECT id FROM companies WHERE slug='fedef') WHERE company_id IS NULL;
--   UPDATE departments     SET company_id = (SELECT id FROM companies WHERE slug='fedef') WHERE company_id IS NULL;
--   ... (etc para cada tabla con company_id)
-- ═══════════════════════════════════════════════════════════════════════
