-- Criação da tabela ptk_schedule_status para gerenciar status do cronograma

CREATE TABLE IF NOT EXISTS `ptk_schedule_status` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `description` VARCHAR(100) NOT NULL COMMENT 'Descrição do status',
  `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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Status disponíveis para cronogramas de projeto';

-- Inserir os status iniciais
INSERT INTO `ptk_schedule_status` (`description`, `created_at`) VALUES
('Não Iniciado', NOW()),
('Em Andamento', NOW()),
('A Faturar', NOW()),
('Concluído', NOW()),
('Suspenso', NOW());

UPDATE ptk_project_schedule SET status = 1 WHERE status = 'A' OR status IS NULL;

-- Converter 'I' (Inativo) para 5 (Suspenso) - lógica mais próxima
UPDATE ptk_project_schedule SET status = 5 WHERE status = 'I';


-- Alterar a tabela ptk_project_schedule para usar foreign key
-- 1. Renomear coluna status para status_id
ALTER TABLE `ptk_project_schedule` 
  MODIFY COLUMN `status` INT NULL DEFAULT 1 COMMENT 'ID do status do cronograma';

-- 2. Adicionar foreign key
ALTER TABLE `ptk_project_schedule`
  ADD CONSTRAINT `fk_schedule_status` 
  FOREIGN KEY (`status`) 
  REFERENCES `ptk_schedule_status` (`id`) 
  ON UPDATE RESTRICT 
  ON DELETE RESTRICT;

-- 3. Criar índice na coluna status_id
CREATE INDEX `idx_project_schedule_status` ON `ptk_project_schedule` (`status`);
