-- Reestrutura cronograma: pais em ptk_project_schedule, filhos em ptk_project_schedule_item.
-- Hierarquia por parent_item_id + position + depth (sem level string).
-- Collation alinhada a utf8mb4_unicode_ci para evitar mix com tabelas legadas.

-- 1) Tabela de itens (mesma collation das tabelas ptk_*)
CREATE TABLE IF NOT EXISTS `ptk_project_schedule_item` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `schedule_id` INT NOT NULL,
  `parent_item_id` INT NULL,
  `position` INT NOT NULL DEFAULT 0,
  `depth` INT NOT NULL DEFAULT 0,
  `title` VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `hours` DECIMAL(10, 2) NOT NULL DEFAULT 0.00,
  `start_date` DATE NOT NULL,
  `end_date` DATE NOT NULL,
  `observations` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL,
  `progress` INT NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `created_user` INT NULL,
  `updated_user` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx_schedule_item_schedule_id` (`schedule_id`),
  INDEX `idx_schedule_item_parent_item_id` (`parent_item_id`),
  INDEX `idx_schedule_item_depth` (`depth`),
  INDEX `idx_schedule_item_position` (`position`),
  CONSTRAINT `fk_schedule_item_schedule`
    FOREIGN KEY (`schedule_id`) REFERENCES `ptk_project_schedule` (`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Reexecução após falha parcial: se parent_id ainda existe, a migration não concluiu.
-- Limpa itens para evitar duplicar no INSERT abaixo.
SET @cleanup_partial := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE()
        AND table_name = 'ptk_project_schedule'
        AND column_name = 'parent_id'
    ),
    'DELETE FROM `ptk_project_schedule_item`',
    'SELECT 1'
  )
);
PREPARE stmt_cleanup FROM @cleanup_partial; EXECUTE stmt_cleanup; DEALLOCATE PREPARE stmt_cleanup;

-- 2) Árvore: cada nó -> root_id + depth
DROP TEMPORARY TABLE IF EXISTS `_tmp_schedule_tree`;
CREATE TEMPORARY TABLE `_tmp_schedule_tree` AS
WITH RECURSIVE tree AS (
  SELECT
    id,
    parent_id,
    id AS root_id,
    0 AS depth_calc,
    position,
    title,
    hours,
    start_date,
    end_date,
    observations,
    progress,
    created_at,
    updated_at,
    created_user,
    updated_user,
    project_id
  FROM ptk_project_schedule
  WHERE parent_id IS NULL
  UNION ALL
  SELECT
    c.id,
    c.parent_id,
    t.root_id,
    t.depth_calc + 1,
    c.position,
    c.title,
    c.hours,
    c.start_date,
    c.end_date,
    c.observations,
    c.progress,
    c.created_at,
    c.updated_at,
    c.created_user,
    c.updated_user,
    c.project_id
  FROM ptk_project_schedule c
  INNER JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

-- 3) Inserir filhos como items (parent_item_id ainda null) — ordem determinística
INSERT INTO `ptk_project_schedule_item` (
  `schedule_id`, `parent_item_id`, `position`, `depth`,
  `title`, `hours`, `start_date`, `end_date`, `observations`, `progress`,
  `created_at`, `updated_at`, `created_user`, `updated_user`
)
SELECT
  t.root_id,
  NULL,
  COALESCE(t.position, 0),
  GREATEST(t.depth_calc - 1, 0),
  t.title,
  t.hours,
  t.start_date,
  t.end_date,
  t.observations,
  COALESCE(t.progress, 0),
  t.created_at,
  t.updated_at,
  t.created_user,
  t.updated_user
FROM `_tmp_schedule_tree` t
WHERE t.depth_calc > 0
ORDER BY t.depth_calc ASC, t.root_id ASC, t.position ASC, t.id ASC;

-- 4) Mapa old_schedule_id -> new_item_id (1:1 por ordem de inserção; sem JOIN em title)
DROP TEMPORARY TABLE IF EXISTS `_tmp_children_ordered`;
CREATE TEMPORARY TABLE `_tmp_children_ordered` AS
SELECT
  t.id AS old_schedule_id,
  t.root_id,
  t.parent_id AS old_parent_id,
  t.depth_calc,
  ROW_NUMBER() OVER (ORDER BY t.depth_calc ASC, t.root_id ASC, t.position ASC, t.id ASC) AS rn
FROM `_tmp_schedule_tree` t
WHERE t.depth_calc > 0;

DROP TEMPORARY TABLE IF EXISTS `_tmp_items_ordered`;
CREATE TEMPORARY TABLE `_tmp_items_ordered` AS
SELECT
  i.id AS new_item_id,
  ROW_NUMBER() OVER (ORDER BY i.id ASC) AS rn
FROM `ptk_project_schedule_item` i;

DROP TEMPORARY TABLE IF EXISTS `_tmp_schedule_item_map`;
CREATE TEMPORARY TABLE `_tmp_schedule_item_map` (
  `old_schedule_id` INT NOT NULL,
  `new_item_id` INT NOT NULL,
  `root_schedule_id` INT NOT NULL,
  `old_parent_id` INT NULL,
  `depth_calc` INT NOT NULL,
  PRIMARY KEY (`old_schedule_id`),
  INDEX (`new_item_id`),
  INDEX (`old_parent_id`)
) ENGINE=InnoDB;

INSERT INTO `_tmp_schedule_item_map` (old_schedule_id, new_item_id, root_schedule_id, old_parent_id, depth_calc)
SELECT
  c.old_schedule_id,
  i.new_item_id,
  c.root_id,
  c.old_parent_id,
  c.depth_calc
FROM `_tmp_children_ordered` c
INNER JOIN `_tmp_items_ordered` i ON i.rn = c.rn;

-- 5) Ajustar parent_item_id e depth
-- MySQL 1137: não pode reabrir a mesma TEMPORARY TABLE duas vezes na mesma query.
-- Cópia usada só no JOIN do parent.
DROP TEMPORARY TABLE IF EXISTS `_tmp_schedule_item_map_parent`;
CREATE TEMPORARY TABLE `_tmp_schedule_item_map_parent` AS
SELECT `old_schedule_id`, `new_item_id` FROM `_tmp_schedule_item_map`;
CREATE INDEX `_tmp_map_parent_old` ON `_tmp_schedule_item_map_parent` (`old_schedule_id`);

UPDATE `ptk_project_schedule_item` i
INNER JOIN `_tmp_schedule_item_map` m ON m.new_item_id = i.id
LEFT JOIN `_tmp_schedule_item_map_parent` mp ON mp.old_schedule_id = m.old_parent_id
SET
  i.parent_item_id = mp.new_item_id,
  i.depth = CASE
    WHEN mp.new_item_id IS NULL THEN 0
    ELSE GREATEST(m.depth_calc - 1, 0)
  END;

-- 6) Itens âncora para users/tickets ligados diretamente à raiz
--    Guarda o mapa por ID (sem comparar title entre collations)
DROP TEMPORARY TABLE IF EXISTS `_tmp_root_anchor`;
CREATE TEMPORARY TABLE `_tmp_root_anchor` (
  `root_schedule_id` INT NOT NULL,
  `anchor_item_id` INT NULL,
  PRIMARY KEY (`root_schedule_id`)
) ENGINE=InnoDB;

INSERT INTO `_tmp_root_anchor` (root_schedule_id, anchor_item_id)
SELECT r.id, NULL
FROM `ptk_project_schedule` r
WHERE r.parent_id IS NULL
  AND (
    EXISTS (SELECT 1 FROM `ptk_project_schedule_user` u WHERE u.schedule_id = r.id)
    OR EXISTS (SELECT 1 FROM `ptk_schedule_ticket` st WHERE st.schedule_id = r.id)
  );

INSERT INTO `ptk_project_schedule_item` (
  `schedule_id`, `parent_item_id`, `position`, `depth`,
  `title`, `hours`, `start_date`, `end_date`, `observations`, `progress`,
  `created_at`, `updated_user`, `created_user`
)
SELECT
  r.id,
  NULL,
  -1,
  0,
  CONCAT(CONVERT(r.title USING utf8mb4) COLLATE utf8mb4_unicode_ci, CONVERT(' (Geral)' USING utf8mb4) COLLATE utf8mb4_unicode_ci),
  r.hours,
  r.start_date,
  r.end_date,
  r.observations,
  COALESCE(r.progress, 0),
  NOW(),
  NULL,
  r.created_user
FROM `ptk_project_schedule` r
INNER JOIN `_tmp_root_anchor` a ON a.root_schedule_id = r.id;

UPDATE `_tmp_root_anchor` a
INNER JOIN `ptk_project_schedule_item` i
  ON i.schedule_id = a.root_schedule_id
 AND i.position = -1
SET a.anchor_item_id = i.id;

-- 7) Preparar colunas item_id em user e ticket (idempotente)
SET @add_user_item := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule_user' AND column_name = 'item_id'
    ),
    'SELECT 1',
    'ALTER TABLE `ptk_project_schedule_user` ADD COLUMN `item_id` INT NULL AFTER `id`'
  )
);
PREPARE stmt_u FROM @add_user_item; EXECUTE stmt_u; DEALLOCATE PREPARE stmt_u;

SET @add_ticket_item := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.columns
      WHERE table_schema = DATABASE() AND table_name = 'ptk_schedule_ticket' AND column_name = 'item_id'
    ),
    'SELECT 1',
    'ALTER TABLE `ptk_schedule_ticket` ADD COLUMN `item_id` INT NULL AFTER `id`'
  )
);
PREPARE stmt_t FROM @add_ticket_item; EXECUTE stmt_t; DEALLOCATE PREPARE stmt_t;

-- 8) Remapear users: filhos -> item; raiz -> âncora
UPDATE `ptk_project_schedule_user` u
INNER JOIN `_tmp_schedule_item_map` m ON m.old_schedule_id = u.schedule_id
SET u.item_id = m.new_item_id
WHERE u.schedule_id IS NOT NULL;

UPDATE `ptk_project_schedule_user` u
INNER JOIN `_tmp_root_anchor` a ON a.root_schedule_id = u.schedule_id
SET u.item_id = a.anchor_item_id
WHERE u.item_id IS NULL AND a.anchor_item_id IS NOT NULL;

-- 9) Remapear tickets
UPDATE `ptk_schedule_ticket` st
INNER JOIN `_tmp_schedule_item_map` m ON m.old_schedule_id = st.schedule_id
SET st.item_id = m.new_item_id
WHERE st.schedule_id IS NOT NULL;

UPDATE `ptk_schedule_ticket` st
INNER JOIN `_tmp_root_anchor` a ON a.root_schedule_id = st.schedule_id
SET st.item_id = a.anchor_item_id
WHERE st.item_id IS NULL AND a.anchor_item_id IS NOT NULL;

-- Remover vínculos órfãos que não puderam ser mapeados
DELETE FROM `ptk_project_schedule_user` WHERE `item_id` IS NULL;
DELETE FROM `ptk_schedule_ticket` WHERE `item_id` IS NULL;

-- 10) Trocar FKs de user (só se schedule_id ainda existir) — nomes de constraint dinâmicos
SET @has_user_schedule := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule_user' AND column_name = 'schedule_id'
);

SET @user_sched_fk := (
  SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ptk_project_schedule_user'
    AND COLUMN_NAME = 'schedule_id'
    AND REFERENCED_TABLE_NAME IS NOT NULL
  LIMIT 1
);
SET @drop_user_fk := IF(
  @has_user_schedule > 0 AND @user_sched_fk IS NOT NULL,
  CONCAT('ALTER TABLE `ptk_project_schedule_user` DROP FOREIGN KEY `', @user_sched_fk, '`'),
  'SELECT 1'
);
PREPARE stmt10a FROM @drop_user_fk; EXECUTE stmt10a; DEALLOCATE PREPARE stmt10a;

SET @drop_user_uk := (
  SELECT IF(
    @has_user_schedule > 0 AND EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule_user' AND index_name = 'uk_schedule_user'
    ),
    'ALTER TABLE `ptk_project_schedule_user` DROP INDEX `uk_schedule_user`',
    'SELECT 1'
  )
);
PREPARE stmt10b FROM @drop_user_uk; EXECUTE stmt10b; DEALLOCATE PREPARE stmt10b;

-- índices em schedule_id (exceto PRIMARY) após dropar FK
SET @drop_user_idx := (
  SELECT IF(
    @has_user_schedule > 0 AND EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule_user' AND index_name = 'fk_schedule_user_schedule'
    ),
    'ALTER TABLE `ptk_project_schedule_user` DROP INDEX `fk_schedule_user_schedule`',
    'SELECT 1'
  )
);
PREPARE stmt10c FROM @drop_user_idx; EXECUTE stmt10c; DEALLOCATE PREPARE stmt10c;

SET @drop_user_idx2 := (
  SELECT IF(
    @has_user_schedule > 0 AND EXISTS(
      SELECT 1 FROM information_schema.statistics
      WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule_user' AND index_name = 'ptk_project_schedule_user_schedule_id_fkey'
    ),
    'ALTER TABLE `ptk_project_schedule_user` DROP INDEX `ptk_project_schedule_user_schedule_id_fkey`',
    'SELECT 1'
  )
);
PREPARE stmt10c2 FROM @drop_user_idx2; EXECUTE stmt10c2; DEALLOCATE PREPARE stmt10c2;

SET @alter_user := (
  SELECT IF(
    @has_user_schedule > 0,
    'ALTER TABLE `ptk_project_schedule_user`
      DROP COLUMN `schedule_id`,
      MODIFY COLUMN `item_id` INT NOT NULL,
      ADD UNIQUE KEY `uk_schedule_item_user` (`item_id`, `user_id`),
      ADD INDEX `fk_schedule_user_item` (`item_id`),
      ADD CONSTRAINT `fk_schedule_user_item`
        FOREIGN KEY (`item_id`) REFERENCES `ptk_project_schedule_item` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE',
    'SELECT 1'
  )
);
PREPARE stmt10d FROM @alter_user; EXECUTE stmt10d; DEALLOCATE PREPARE stmt10d;

-- 11) Trocar FKs de ticket
SET @has_ticket_schedule := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'ptk_schedule_ticket' AND column_name = 'schedule_id'
);

SET @ticket_sched_fk := (
  SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ptk_schedule_ticket'
    AND COLUMN_NAME = 'schedule_id'
    AND REFERENCED_TABLE_NAME IS NOT NULL
  LIMIT 1
);
SET @drop_ticket_fk := IF(
  @has_ticket_schedule > 0 AND @ticket_sched_fk IS NOT NULL,
  CONCAT('ALTER TABLE `ptk_schedule_ticket` DROP FOREIGN KEY `', @ticket_sched_fk, '`'),
  'SELECT 1'
);
PREPARE stmt11a FROM @drop_ticket_fk; EXECUTE stmt11a; DEALLOCATE PREPARE stmt11a;

SET @drop_idx1 := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.statistics
           WHERE table_schema = DATABASE() AND table_name = 'ptk_schedule_ticket' AND index_name = 'idx_schedule_ticket_schedule_id'),
    'ALTER TABLE `ptk_schedule_ticket` DROP INDEX `idx_schedule_ticket_schedule_id`',
    'SELECT 1'
  )
);
PREPARE stmt1 FROM @drop_idx1; EXECUTE stmt1; DEALLOCATE PREPARE stmt1;

SET @drop_idx2 := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.statistics
           WHERE table_schema = DATABASE() AND table_name = 'ptk_schedule_ticket' AND index_name = 'schedule_id'),
    'ALTER TABLE `ptk_schedule_ticket` DROP INDEX `schedule_id`',
    'SELECT 1'
  )
);
PREPARE stmt2 FROM @drop_idx2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;

SET @drop_idx3 := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.statistics
           WHERE table_schema = DATABASE() AND table_name = 'ptk_schedule_ticket' AND index_name = 'ptk_schedule_ticket_schedule_id_fkey'),
    'ALTER TABLE `ptk_schedule_ticket` DROP INDEX `ptk_schedule_ticket_schedule_id_fkey`',
    'SELECT 1'
  )
);
PREPARE stmt2b FROM @drop_idx3; EXECUTE stmt2b; DEALLOCATE PREPARE stmt2b;

SET @alter_ticket := (
  SELECT IF(
    @has_ticket_schedule > 0,
    'ALTER TABLE `ptk_schedule_ticket`
      DROP COLUMN `schedule_id`,
      MODIFY COLUMN `item_id` INT NOT NULL,
      ADD INDEX `idx_schedule_ticket_item_id` (`item_id`),
      ADD CONSTRAINT `fk_ptk_schedule_ticket_item`
        FOREIGN KEY (`item_id`) REFERENCES `ptk_project_schedule_item` (`id`)
        ON DELETE CASCADE ON UPDATE CASCADE',
    'SELECT 1'
  )
);
PREPARE stmt11b FROM @alter_ticket; EXECUTE stmt11b; DEALLOCATE PREPARE stmt11b;

-- 12) Remover filhos antigos da tabela de schedule
SET @has_parent := (
  SELECT COUNT(*) FROM information_schema.columns
  WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule' AND column_name = 'parent_id'
);
SET @del_children := (
  SELECT IF(@has_parent > 0, 'DELETE FROM `ptk_project_schedule` WHERE `parent_id` IS NOT NULL', 'SELECT 1')
);
PREPARE stmt12 FROM @del_children; EXECUTE stmt12; DEALLOCATE PREPARE stmt12;

-- 13) Remover self-FK e colunas parent_id / level
SET @parent_fk := (
  SELECT CONSTRAINT_NAME FROM information_schema.KEY_COLUMN_USAGE
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ptk_project_schedule'
    AND COLUMN_NAME = 'parent_id'
    AND REFERENCED_TABLE_NAME IS NOT NULL
  LIMIT 1
);
SET @drop_parent_fk := IF(
  @parent_fk IS NOT NULL,
  CONCAT('ALTER TABLE `ptk_project_schedule` DROP FOREIGN KEY `', @parent_fk, '`'),
  'SELECT 1'
);
PREPARE stmt3 FROM @drop_parent_fk; EXECUTE stmt3; DEALLOCATE PREPARE stmt3;

SET @drop_parent_idx := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.statistics
           WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule' AND index_name = 'fk_schedule_parent'),
    'ALTER TABLE `ptk_project_schedule` DROP INDEX `fk_schedule_parent`',
    'SELECT 1'
  )
);
PREPARE stmt4 FROM @drop_parent_idx; EXECUTE stmt4; DEALLOCATE PREPARE stmt4;

SET @drop_parent_idx2 := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.statistics
           WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule' AND index_name = 'idx_project_schedule_parent_id'),
    'ALTER TABLE `ptk_project_schedule` DROP INDEX `idx_project_schedule_parent_id`',
    'SELECT 1'
  )
);
PREPARE stmt5 FROM @drop_parent_idx2; EXECUTE stmt5; DEALLOCATE PREPARE stmt5;

SET @drop_level_idx := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.statistics
           WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule' AND index_name = 'idx_level'),
    'ALTER TABLE `ptk_project_schedule` DROP INDEX `idx_level`',
    'SELECT 1'
  )
);
PREPARE stmt6 FROM @drop_level_idx; EXECUTE stmt6; DEALLOCATE PREPARE stmt6;

SET @drop_level_idx2 := (
  SELECT IF(
    EXISTS(SELECT 1 FROM information_schema.statistics
           WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule' AND index_name = 'idx_project_schedule_level'),
    'ALTER TABLE `ptk_project_schedule` DROP INDEX `idx_project_schedule_level`',
    'SELECT 1'
  )
);
PREPARE stmt7 FROM @drop_level_idx2; EXECUTE stmt7; DEALLOCATE PREPARE stmt7;

SET @drop_cols := (
  SELECT IF(
    @has_parent > 0,
    'ALTER TABLE `ptk_project_schedule` DROP COLUMN `parent_id`, DROP COLUMN `level`',
    'SELECT 1'
  )
);
PREPARE stmt13 FROM @drop_cols; EXECUTE stmt13; DEALLOCATE PREPARE stmt13;

-- 14) FK self em items (após dados prontos)
SET @add_item_parent_fk := (
  SELECT IF(
    EXISTS(
      SELECT 1 FROM information_schema.table_constraints
      WHERE table_schema = DATABASE() AND table_name = 'ptk_project_schedule_item' AND constraint_name = 'fk_schedule_item_parent'
    ),
    'SELECT 1',
    'ALTER TABLE `ptk_project_schedule_item`
      ADD CONSTRAINT `fk_schedule_item_parent`
        FOREIGN KEY (`parent_item_id`) REFERENCES `ptk_project_schedule_item` (`id`)
        ON DELETE SET NULL ON UPDATE CASCADE'
  )
);
PREPARE stmt14 FROM @add_item_parent_fk; EXECUTE stmt14; DEALLOCATE PREPARE stmt14;

-- 15) Rollup básico de horas/datas/progresso nos pais
UPDATE `ptk_project_schedule` s
INNER JOIN (
  SELECT
    schedule_id,
    COALESCE(SUM(hours), 0) AS sum_hours,
    MIN(start_date) AS min_start,
    MAX(end_date) AS max_end,
    COALESCE(
      ROUND(
        SUM(progress * hours) / NULLIF(SUM(hours), 0)
      ),
      0
    ) AS avg_progress
  FROM `ptk_project_schedule_item`
  WHERE parent_item_id IS NULL
  GROUP BY schedule_id
) agg ON agg.schedule_id = s.id
SET
  s.hours = agg.sum_hours,
  s.start_date = agg.min_start,
  s.end_date = agg.max_end,
  s.progress = agg.avg_progress;