-- ============================================================
-- MIGRACIÓN: Soporte Multi-Empresa
-- Ejecutar en phpMyAdmin sobre la BD sistema_gestion
-- ============================================================

USE `sistema_gestion`;

-- ── 1. Tabla companies ────────────────────────────────────
CREATE TABLE IF NOT EXISTS `companies` (
  `id`            INT AUTO_INCREMENT PRIMARY KEY,
  `name`          VARCHAR(150) NOT NULL,
  `rut`           VARCHAR(20) NULL,
  `address`       TEXT NULL,
  `phone`         VARCHAR(30) NULL,
  `email`         VARCHAR(150) NULL,
  `website`       VARCHAR(200) NULL,
  `logo`          VARCHAR(255) NULL COMMENT 'Ruta relativa a uploads/logos/',
  `primary_color` VARCHAR(20) DEFAULT '#1d4ed8',
  `footer_text`   TEXT NULL,
  `terms`         TEXT NULL,
  `active`        TINYINT(1) DEFAULT 1,
  `created_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at`    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Empresa por defecto (migra datos de system_settings)
INSERT INTO `companies` (`id`, `name`, `rut`, `primary_color`, `footer_text`, `terms`)
SELECT
  1,
  COALESCE((SELECT value FROM system_settings WHERE setting_key = 'company_name'), 'Mi Empresa'),
  COALESCE((SELECT value FROM system_settings WHERE setting_key = 'company_rut'), NULL),
  COALESCE((SELECT value FROM system_settings WHERE setting_key = 'pdf_primary_color'), '#1d4ed8'),
  COALESCE((SELECT value FROM system_settings WHERE setting_key = 'pdf_footer_text'), 'Gracias por su confianza.'),
  COALESCE((SELECT value FROM system_settings WHERE setting_key = 'pdf_terms'), NULL)
ON DUPLICATE KEY UPDATE `name` = VALUES(`name`);

-- ── 2. Agregar company_id a requests ─────────────────────
ALTER TABLE `requests`
  ADD COLUMN IF NOT EXISTS `company_id` INT NULL AFTER `created_by`,
  ADD CONSTRAINT `fk_req_company`
    FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL;

-- ── 3. Agregar company_id a quotations ───────────────────
ALTER TABLE `quotations`
  ADD COLUMN IF NOT EXISTS `company_id` INT NULL AFTER `created_by`,
  ADD CONSTRAINT `fk_quot_company`
    FOREIGN KEY IF NOT EXISTS (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL;

-- Asignar empresa 1 a registros existentes
UPDATE `requests`   SET company_id = 1 WHERE company_id IS NULL;
UPDATE `quotations` SET company_id = 1 WHERE company_id IS NULL;

-- ── 4. Agregar company_id a users (opcional: usuario pertenece a empresa) ─
ALTER TABLE `users`
  ADD COLUMN IF NOT EXISTS `default_company_id` INT NULL AFTER `client_id`,
  ADD CONSTRAINT `fk_user_company`
    FOREIGN KEY IF NOT EXISTS (`default_company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL;
