-- =====================================================================
-- ARCA Hosting — Migracion Fase 2.5 (Fortalecimiento del Core)
-- Objetivos 9 (soft delete) y 10 (UUID) del documento de la fase.
-- Segura de ejecutar sobre una instalacion de Fase 1/2 ya en uso: solo
-- agrega columnas e indices, no modifica datos existentes salvo para
-- rellenar (backfill) los nuevos campos de forma no destructiva.
-- =====================================================================

USE arca_hosting;

-- ---------------------------------------------------------------------
-- UUID en entidades principales (objetivo 10)
-- ---------------------------------------------------------------------
ALTER TABLE clients        ADD COLUMN uuid CHAR(36) NULL AFTER id;
ALTER TABLE companies      ADD COLUMN uuid CHAR(36) NULL AFTER id;
ALTER TABLE users          ADD COLUMN uuid CHAR(36) NULL AFTER id;
ALTER TABLE service_plans  ADD COLUMN uuid CHAR(36) NULL AFTER id;

-- Backfill de las filas que ya existian antes de esta migracion (los
-- Repositories generan el UUID en PHP para los registros nuevos; MySQL
-- UUID() se usa aqui solo para rellenar el historico de una sola vez).
UPDATE clients       SET uuid = UUID() WHERE uuid IS NULL;
UPDATE companies     SET uuid = UUID() WHERE uuid IS NULL;
UPDATE users         SET uuid = UUID() WHERE uuid IS NULL;
UPDATE service_plans SET uuid = UUID() WHERE uuid IS NULL;

ALTER TABLE clients        MODIFY COLUMN uuid CHAR(36) NOT NULL, ADD UNIQUE KEY uq_clients_uuid (uuid);
ALTER TABLE companies      MODIFY COLUMN uuid CHAR(36) NOT NULL, ADD UNIQUE KEY uq_companies_uuid (uuid);
ALTER TABLE users          MODIFY COLUMN uuid CHAR(36) NOT NULL, ADD UNIQUE KEY uq_users_uuid (uuid);
ALTER TABLE service_plans  MODIFY COLUMN uuid CHAR(36) NOT NULL, ADD UNIQUE KEY uq_service_plans_uuid (uuid);

-- ---------------------------------------------------------------------
-- Soft delete con autor (objetivo 9). `clients` ya tenia deleted_at desde
-- la Fase 1; aqui se le agrega deleted_by y se agrega el par completo a
-- companies, users y service_plans.
-- ---------------------------------------------------------------------
ALTER TABLE clients ADD COLUMN deleted_by BIGINT UNSIGNED NULL AFTER deleted_at,
    ADD CONSTRAINT fk_clients_deleted_by FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE companies
    ADD COLUMN deleted_at DATETIME NULL,
    ADD COLUMN deleted_by BIGINT UNSIGNED NULL,
    ADD CONSTRAINT fk_companies_deleted_by FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE users
    ADD COLUMN deleted_at DATETIME NULL,
    ADD COLUMN deleted_by BIGINT UNSIGNED NULL,
    ADD CONSTRAINT fk_users_deleted_by FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL;

ALTER TABLE service_plans
    ADD COLUMN deleted_at DATETIME NULL,
    ADD COLUMN deleted_by BIGINT UNSIGNED NULL,
    ADD CONSTRAINT fk_service_plans_deleted_by FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL;

-- El catalogo de servicios ahora se filtra siempre por deleted_at IS NULL
-- (ver ServiceRepository); este indice acelera esa consulta, la mas
-- frecuente del modulo.
CREATE INDEX idx_service_plans_company_deleted ON service_plans (company_id, deleted_at);
CREATE INDEX idx_clients_company_deleted ON clients (company_id, deleted_at);

-- ---------------------------------------------------------------------
-- Revision de indices (objetivo 14): un par de indices que faltaban para
-- las consultas mas frecuentes de client_services (usadas en la ficha del
-- cliente y, en la Fase 5, para calcular pendientes de facturar).
-- ---------------------------------------------------------------------
CREATE INDEX idx_client_services_client ON client_services (client_id, status);
CREATE INDEX idx_client_services_plan ON client_services (service_plan_id);