-- Planning Poker + Classificação SP → Horas
-- Idempotente (seguro se já aplicado via SQL avulso)

-- 1) story_points no ticket
SET @col_exists := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ptk_ticket'
    AND COLUMN_NAME = 'story_points'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `ptk_ticket` ADD COLUMN `story_points` INT NULL AFTER `total_minutes_estimated`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- 2) Tabelas do Planning Poker
CREATE TABLE IF NOT EXISTS `ptk_poker_session` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NULL,
  `status` VARCHAR(20) NOT NULL DEFAULT 'open',
  `created_user` INT NULL,
  `updated_user` INT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_poker_session_created_user` (`created_user`),
  INDEX `idx_poker_session_status` (`status`),
  CONSTRAINT `fk_poker_session_user`
    FOREIGN KEY (`created_user`) REFERENCES `amb_user` (`id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ptk_poker_session_ticket` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `session_id` INT NOT NULL,
  `ticket_id` INT NOT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `status` VARCHAR(20) NOT NULL DEFAULT 'pending',
  `final_points` INT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_poker_session_ticket` (`session_id`, `ticket_id`),
  INDEX `idx_poker_st_session` (`session_id`),
  INDEX `idx_poker_st_ticket` (`ticket_id`),
  INDEX `idx_poker_st_status` (`status`),
  CONSTRAINT `fk_poker_st_session`
    FOREIGN KEY (`session_id`) REFERENCES `ptk_poker_session` (`id`)
    ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_poker_st_ticket`
    FOREIGN KEY (`ticket_id`) REFERENCES `ptk_ticket` (`id`)
    ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `ptk_poker_round` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `session_ticket_id` INT NOT NULL,
  `round_number` INT NOT NULL DEFAULT 1,
  `status` VARCHAR(20) NOT NULL DEFAULT 'voting',
  `consensus_value` VARCHAR(10) NULL,
  `duration_seconds` INT NULL,
  `ends_at` DATETIME NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  INDEX `idx_poker_round_st` (`session_ticket_id`),
  INDEX `idx_poker_round_status` (`status`),
  CONSTRAINT `fk_poker_round_st`
    FOREIGN KEY (`session_ticket_id`) REFERENCES `ptk_poker_session_ticket` (`id`)
    ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Timer (caso a tabela já existisse sem as colunas)
SET @col_exists := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ptk_poker_round'
    AND COLUMN_NAME = 'duration_seconds'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `ptk_poker_round` ADD COLUMN `duration_seconds` INT NULL AFTER `consensus_value`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists := (
  SELECT COUNT(*)
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
    AND TABLE_NAME = 'ptk_poker_round'
    AND COLUMN_NAME = 'ends_at'
);
SET @sql := IF(
  @col_exists = 0,
  'ALTER TABLE `ptk_poker_round` ADD COLUMN `ends_at` DATETIME NULL AFTER `duration_seconds`',
  'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS `ptk_poker_vote` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `round_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `value` VARCHAR(10) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_poker_vote_round_user` (`round_id`, `user_id`),
  INDEX `idx_poker_vote_round` (`round_id`),
  INDEX `idx_poker_vote_user` (`user_id`),
  CONSTRAINT `fk_poker_vote_round`
    FOREIGN KEY (`round_id`) REFERENCES `ptk_poker_round` (`id`)
    ON DELETE CASCADE ON UPDATE NO ACTION,
  CONSTRAINT `fk_poker_vote_user`
    FOREIGN KEY (`user_id`) REFERENCES `amb_user` (`id`)
    ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3) Classificação SP → horas (CRUD)
CREATE TABLE IF NOT EXISTS `ptk_poker_point_hours` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `story_points` INT NOT NULL,
  `hours` FLOAT NOT NULL,
  `description` VARCHAR(100) NULL,
  `created_user` INT NULL,
  `updated_user` INT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_poker_point_hours_points` (`story_points`),
  INDEX `idx_poker_point_hours_points` (`story_points`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4) Formulários de permissão
INSERT INTO `amb_form` (`nameform`, `description`, `help`, `default_rights`, `created_at`, `updated_at`, `created_user`, `updated_user`)
SELECT 'poker', 'Planning Poker', 'Sessões de Planning Poker para estimativa em story points.', 'IAEC', NOW(), NULL, 'system', NULL
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `amb_form` WHERE `nameform` = 'poker');

INSERT INTO `amb_form` (`nameform`, `description`, `help`, `default_rights`, `created_at`, `updated_at`, `created_user`, `updated_user`)
SELECT
  'ptk_poker_point_hours',
  'Classificação Poker (SP → Horas)',
  'CRUD da equivalência entre story points do Planning Poker e horas estimadas.',
  'IAEC',
  NOW(),
  NULL,
  'system',
  NULL
FROM DUAL
WHERE NOT EXISTS (
  SELECT 1 FROM `amb_form` WHERE `nameform` = 'ptk_poker_point_hours'
);

-- 5) Direitos: quem tem poker/sprint/projeto herda IAEC na classificação
INSERT INTO `amb_profile_autoriza` (`amb_profile_id`, `amb_form_id`, `direitos`, `created_at`, `created_user`)
SELECT DISTINCT pa.`amb_profile_id`, f.`id`, 'IAEC', NOW(), 'system'
FROM `amb_profile_autoriza` pa
INNER JOIN `amb_form` f_src ON f_src.`id` = pa.`amb_form_id`
  AND f_src.`nameform` IN ('poker', 'ptk_sprint', 'ptk_project')
CROSS JOIN `amb_form` f
WHERE f.`nameform` = 'ptk_poker_point_hours'
  AND NOT EXISTS (
    SELECT 1 FROM `amb_profile_autoriza` x
    WHERE x.`amb_profile_id` = pa.`amb_profile_id` AND x.`amb_form_id` = f.`id`
  );

INSERT INTO `amb_user_autoriza` (`amb_user_id`, `amb_form_id`, `direitos`, `created_at`, `created_user`)
SELECT DISTINCT ua.`amb_user_id`, f.`id`, 'IAEC', NOW(), 'system'
FROM `amb_user_autoriza` ua
INNER JOIN `amb_form` f_src ON f_src.`id` = ua.`amb_form_id`
  AND f_src.`nameform` IN ('poker', 'ptk_sprint', 'ptk_project')
CROSS JOIN `amb_form` f
WHERE f.`nameform` = 'ptk_poker_point_hours'
  AND NOT EXISTS (
    SELECT 1 FROM `amb_user_autoriza` x
    WHERE x.`amb_user_id` = ua.`amb_user_id` AND x.`amb_form_id` = f.`id`
  );

-- 6) Seed da classificação do time
INSERT INTO `ptk_poker_point_hours` (`story_points`, `hours`, `description`, `created_at`, `created_user`)
SELECT v.`story_points`, v.`hours`, v.`description`, NOW(), NULL
FROM (
  SELECT 1 AS `story_points`, 2 AS `hours`, '1/2' AS `description`
  UNION ALL SELECT 2, 4, '2/4'
  UNION ALL SELECT 3, 6, '6/8'
  UNION ALL SELECT 5, 8, '8/10'
  UNION ALL SELECT 13, 12, '10/16'
) AS v
WHERE NOT EXISTS (
  SELECT 1 FROM `ptk_poker_point_hours` p WHERE p.`story_points` = v.`story_points`
);

UPDATE `ptk_poker_point_hours` SET `hours` = 2,  `description` = '1/2',   `updated_at` = NOW() WHERE `story_points` = 1;
UPDATE `ptk_poker_point_hours` SET `hours` = 4,  `description` = '2/4',   `updated_at` = NOW() WHERE `story_points` = 2;
UPDATE `ptk_poker_point_hours` SET `hours` = 6,  `description` = '6/8',   `updated_at` = NOW() WHERE `story_points` = 3;
UPDATE `ptk_poker_point_hours` SET `hours` = 8,  `description` = '8/10',  `updated_at` = NOW() WHERE `story_points` = 5;
UPDATE `ptk_poker_point_hours` SET `hours` = 12, `description` = '10/16', `updated_at` = NOW() WHERE `story_points` = 13;

DELETE FROM `ptk_poker_point_hours` WHERE `story_points` IN (0, 8);