-- Drop old subscription tables
DROP TABLE IF EXISTS `subscriptions`;
DROP TABLE IF EXISTS `user_quotas`;

-- Drop old columns from users
ALTER TABLE `users` DROP COLUMN IF EXISTS `subscriptionTier`;

-- Create new one-time payment tables
CREATE TABLE `domain_purchases` (
`id` int AUTO_INCREMENT NOT NULL,
`userId` int NOT NULL,
`domain` varchar(255) NOT NULL,
`tier` enum('free','tier_2_50','tier_5') NOT NULL DEFAULT 'free',
`stripePaymentIntentId` varchar(255),
`downloadCount` int NOT NULL DEFAULT 0,
`monthStart` timestamp NOT NULL DEFAULT (now()),
`createdAt` timestamp NOT NULL DEFAULT (now()),
`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT `domain_purchases_id` PRIMARY KEY(`id`)
);

CREATE TABLE `user_monthly_quotas` (
`id` int AUTO_INCREMENT NOT NULL,
`userId` int NOT NULL,
`domainsUsedThisMonth` int NOT NULL DEFAULT 0,
`monthStart` timestamp NOT NULL DEFAULT (now()),
`createdAt` timestamp NOT NULL DEFAULT (now()),
`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
CONSTRAINT `user_monthly_quotas_id` PRIMARY KEY(`id`),
CONSTRAINT `user_monthly_quotas_userId_unique` UNIQUE(`userId`)
);