-- Multiusuário: User + userId em dados pessoais (Category permanece global)

CREATE TABLE `User` (
    `id` VARCHAR(191) NOT NULL,
    `email` VARCHAR(191) NOT NULL,
    `name` VARCHAR(191) NOT NULL,
    `passwordHash` VARCHAR(191) NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
    `updatedAt` DATETIME(3) NOT NULL,

    UNIQUE INDEX `User_email_key`(`email`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Usuário bootstrap para dados já existentes (login: voce@fluxo.app / fluxo123)
INSERT INTO `User` (`id`, `email`, `name`, `passwordHash`, `createdAt`, `updatedAt`)
VALUES (
  'bootstrap_fluxo_owner',
  'voce@fluxo.app',
  'Você',
  '$2b$10$Ym3EAFf/iXQAwmg15TIbxeB5Ikfs9HFsBzbhDvHNrbygKiJoI5/wy',
  CURRENT_TIMESTAMP(3),
  CURRENT_TIMESTAMP(3)
);

ALTER TABLE `Profile` ADD COLUMN `userId` VARCHAR(191) NULL;
ALTER TABLE `Account` ADD COLUMN `userId` VARCHAR(191) NULL;
ALTER TABLE `RecurringRule` ADD COLUMN `userId` VARCHAR(191) NULL;
ALTER TABLE `Transaction` ADD COLUMN `userId` VARCHAR(191) NULL;
ALTER TABLE `Budget` ADD COLUMN `userId` VARCHAR(191) NULL;
ALTER TABLE `Goal` ADD COLUMN `userId` VARCHAR(191) NULL;
ALTER TABLE `Insight` ADD COLUMN `userId` VARCHAR(191) NULL;
ALTER TABLE `PayslipImport` ADD COLUMN `userId` VARCHAR(191) NULL;

UPDATE `Profile` SET `userId` = 'bootstrap_fluxo_owner' WHERE `userId` IS NULL;
UPDATE `Account` SET `userId` = 'bootstrap_fluxo_owner' WHERE `userId` IS NULL;
UPDATE `RecurringRule` SET `userId` = 'bootstrap_fluxo_owner' WHERE `userId` IS NULL;
UPDATE `Transaction` SET `userId` = 'bootstrap_fluxo_owner' WHERE `userId` IS NULL;
UPDATE `Budget` SET `userId` = 'bootstrap_fluxo_owner' WHERE `userId` IS NULL;
UPDATE `Goal` SET `userId` = 'bootstrap_fluxo_owner' WHERE `userId` IS NULL;
UPDATE `Insight` SET `userId` = 'bootstrap_fluxo_owner' WHERE `userId` IS NULL;
UPDATE `PayslipImport` SET `userId` = 'bootstrap_fluxo_owner' WHERE `userId` IS NULL;

-- Se não havia profile, cria um
INSERT INTO `Profile` (`id`, `userId`, `monthlyIncome`, `dependents`, `riskAppetite`, `savingsTargetPct`, `hourlyWage`, `createdAt`, `updatedAt`)
SELECT 'bootstrap_profile', 'bootstrap_fluxo_owner', 0, 0, 'moderado', 10, 0, CURRENT_TIMESTAMP(3), CURRENT_TIMESTAMP(3)
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `Profile` WHERE `userId` = 'bootstrap_fluxo_owner');

ALTER TABLE `Profile` MODIFY `userId` VARCHAR(191) NOT NULL;
ALTER TABLE `Account` MODIFY `userId` VARCHAR(191) NOT NULL;
ALTER TABLE `RecurringRule` MODIFY `userId` VARCHAR(191) NOT NULL;
ALTER TABLE `Transaction` MODIFY `userId` VARCHAR(191) NOT NULL;
ALTER TABLE `Budget` MODIFY `userId` VARCHAR(191) NOT NULL;
ALTER TABLE `Goal` MODIFY `userId` VARCHAR(191) NOT NULL;
ALTER TABLE `Insight` MODIFY `userId` VARCHAR(191) NOT NULL;
ALTER TABLE `PayslipImport` MODIFY `userId` VARCHAR(191) NOT NULL;

-- Uniques antigos → novos compostos
ALTER TABLE `Budget` DROP INDEX `Budget_month_categoryId_key`;
ALTER TABLE `PayslipImport` DROP INDEX `PayslipImport_month_key`;

CREATE UNIQUE INDEX `Profile_userId_key` ON `Profile`(`userId`);
CREATE UNIQUE INDEX `Budget_userId_month_categoryId_key` ON `Budget`(`userId`, `month`, `categoryId`);
CREATE UNIQUE INDEX `PayslipImport_userId_month_key` ON `PayslipImport`(`userId`, `month`);

CREATE INDEX `Account_userId_idx` ON `Account`(`userId`);
CREATE INDEX `Account_userId_active_idx` ON `Account`(`userId`, `active`);
CREATE INDEX `RecurringRule_userId_idx` ON `RecurringRule`(`userId`);
CREATE INDEX `RecurringRule_userId_active_idx` ON `RecurringRule`(`userId`, `active`);
CREATE INDEX `Transaction_userId_idx` ON `Transaction`(`userId`);
CREATE INDEX `Transaction_userId_date_idx` ON `Transaction`(`userId`, `date`);
CREATE INDEX `Budget_userId_month_idx` ON `Budget`(`userId`, `month`);
CREATE INDEX `Goal_userId_idx` ON `Goal`(`userId`);
CREATE INDEX `Insight_userId_idx` ON `Insight`(`userId`);
CREATE INDEX `Insight_userId_status_idx` ON `Insight`(`userId`, `status`);
CREATE INDEX `PayslipImport_userId_idx` ON `PayslipImport`(`userId`);

ALTER TABLE `Profile` ADD CONSTRAINT `Profile_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `Account` ADD CONSTRAINT `Account_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `RecurringRule` ADD CONSTRAINT `RecurringRule_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `Transaction` ADD CONSTRAINT `Transaction_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `Budget` ADD CONSTRAINT `Budget_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `Goal` ADD CONSTRAINT `Goal_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `Insight` ADD CONSTRAINT `Insight_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE `PayslipImport` ADD CONSTRAINT `PayslipImport_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `User`(`id`) ON DELETE CASCADE ON UPDATE CASCADE;