-- ============================================================
-- Migration: Company-centric billing & multi-company users
-- BillAccount and Department move from userId → companyId.
-- User ↔ Company becomes many-to-many via UserCompany junction.
-- Department gains smsCostTransactional / smsCostPromotional.
-- ============================================================

-- Step 1: Create UserCompany junction table
CREATE TABLE `usercompany` (
    `id`        INTEGER NOT NULL AUTO_INCREMENT,
    `uuid`      VARCHAR(191) NOT NULL,
    `userId`    INTEGER NOT NULL,
    `companyId` INTEGER NOT NULL,
    `createdAt` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),

    UNIQUE INDEX `usercompany_uuid_key`(`uuid`),
    UNIQUE INDEX `usercompany_userId_companyId_key`(`userId`, `companyId`),
    INDEX `usercompany_userId_idx`(`userId`),
    INDEX `usercompany_companyId_idx`(`companyId`),
    PRIMARY KEY (`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- AddForeignKey
ALTER TABLE `usercompany` ADD CONSTRAINT `usercompany_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `user`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
-- AddForeignKey
ALTER TABLE `usercompany` ADD CONSTRAINT `usercompany_companyId_fkey` FOREIGN KEY (`companyId`) REFERENCES `company`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;

-- Step 2: Backfill UserCompany from existing User.companyId
INSERT INTO `usercompany` (`uuid`, `userId`, `companyId`, `createdAt`)
SELECT UUID(), `id`, `companyId`, NOW()
FROM `user`
WHERE `companyId` IS NOT NULL;

-- Step 3: Add companyId (nullable) to BillAccount
ALTER TABLE `billaccount` ADD COLUMN `companyId` INTEGER NULL;

-- Step 4: Backfill BillAccount.companyId from User.companyId
UPDATE `billaccount` ba
    INNER JOIN `user` u ON u.id = ba.userId
    SET ba.companyId = u.companyId
    WHERE u.companyId IS NOT NULL;

-- Step 5: Make BillAccount.companyId NOT NULL
ALTER TABLE `billaccount` MODIFY COLUMN `companyId` INTEGER NOT NULL;

-- Step 6: Add FK and index for BillAccount.companyId
ALTER TABLE `billaccount` ADD CONSTRAINT `billaccount_companyId_fkey` FOREIGN KEY (`companyId`) REFERENCES `company`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
CREATE INDEX `billaccount_companyId_idx` ON `billaccount`(`companyId`);

-- Step 7: Drop old BillAccount userId FK, index, and column
ALTER TABLE `billaccount` DROP FOREIGN KEY `billaccount_userId_fkey`;
DROP INDEX `billaccount_userId_idx` ON `billaccount`;
ALTER TABLE `billaccount` DROP COLUMN `userId`;

-- Step 8: Add companyId (nullable) to Department
ALTER TABLE `department` ADD COLUMN `companyId` INTEGER NULL;

-- Step 9: Backfill Department.companyId from User.companyId
UPDATE `department` d
    INNER JOIN `user` u ON u.id = d.userId
    SET d.companyId = u.companyId
    WHERE u.companyId IS NOT NULL;

-- Step 10: Make Department.companyId NOT NULL
ALTER TABLE `department` MODIFY COLUMN `companyId` INTEGER NOT NULL;

-- Step 11: Add FK and index for Department.companyId
ALTER TABLE `department` ADD CONSTRAINT `department_companyId_fkey` FOREIGN KEY (`companyId`) REFERENCES `company`(`id`) ON DELETE RESTRICT ON UPDATE CASCADE;
CREATE INDEX `department_companyId_idx` ON `department`(`companyId`);

-- Step 12: Add smsCostTransactional and smsCostPromotional to Department
ALTER TABLE `department`
    ADD COLUMN `smsCostTransactional` DOUBLE NULL,
    ADD COLUMN `smsCostPromotional`   DOUBLE NULL;

-- Step 13: Drop old Department userId unique constraint, index, and column
DROP INDEX `department_userId_externalCompanyId_key` ON `department`;
DROP INDEX `department_userId_idx` ON `department`;
ALTER TABLE `department` DROP COLUMN `userId`;

-- Step 14: Add new unique constraint for Department (companyId, externalCompanyId)
CREATE UNIQUE INDEX `department_companyId_externalCompanyId_key` ON `department`(`companyId`, `externalCompanyId`);

-- Step 15: Drop User.companyId FK, index, and column
ALTER TABLE `user` DROP FOREIGN KEY `user_companyId_fkey`;
DROP INDEX `user_companyId_idx` ON `user`;
ALTER TABLE `user` DROP COLUMN `companyId`;