CREATE TABLE `contact_inquiries` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(255) NOT NULL,
	`email` varchar(320) NOT NULL,
	`subject` varchar(255),
	`message` text NOT NULL,
	`status` enum('new','read','replied') NOT NULL DEFAULT 'new',
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `contact_inquiries_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `documents` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`entityId` int,
	`orderId` int,
	`filingId` int,
	`fileName` varchar(255) NOT NULL,
	`fileType` varchar(64),
	`storageKey` varchar(512) NOT NULL,
	`storageUrl` varchar(1024) NOT NULL,
	`uploadedBy` enum('client','admin') NOT NULL DEFAULT 'admin',
	`description` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `documents_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `entities` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`businessName` varchar(255) NOT NULL,
	`entityType` enum('LLC','Corporation','LP','Nonprofit','Sole Proprietorship','Other') NOT NULL,
	`state` varchar(2) NOT NULL,
	`stateFileNumber` varchar(64),
	`formationDate` date,
	`registeredAgentName` varchar(255),
	`registeredAgentAddress` text,
	`contactEmail` varchar(320),
	`nextDueDate` date,
	`status` enum('active','pending','dissolved','suspended') NOT NULL DEFAULT 'active',
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `entities_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `filings` (
	`id` int AUTO_INCREMENT NOT NULL,
	`entityId` int NOT NULL,
	`filingYear` int NOT NULL,
	`status` enum('pending','in_progress','filed','confirmed','rejected') NOT NULL DEFAULT 'pending',
	`stateFee` decimal(10,2),
	`serviceFee` decimal(10,2),
	`dateFiled` date,
	`confirmationNumber` varchar(128),
	`notes` text,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `filings_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `orders` (
	`id` int AUTO_INCREMENT NOT NULL,
	`userId` int NOT NULL,
	`entityId` int,
	`serviceType` enum('annual_report','formation','ein_obtainment','operating_agreement','reinstatement','amendment','foreign_qualification','dba_registration','dissolution','document_retrieval','certificate_good_standing','other') NOT NULL,
	`status` enum('pending','in_review','in_progress','completed','rejected','canceled') NOT NULL DEFAULT 'pending',
	`priority` enum('normal','urgent') NOT NULL DEFAULT 'normal',
	`state` varchar(2),
	`description` text,
	`clientNotes` text,
	`adminNotes` text,
	`price` decimal(10,2),
	`stateFee` decimal(10,2),
	`stripePaymentIntentId` varchar(128),
	`paymentStatus` enum('unpaid','paid','refunded','failed') NOT NULL DEFAULT 'unpaid',
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `orders_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `states_db` (
	`id` int AUTO_INCREMENT NOT NULL,
	`stateName` varchar(64) NOT NULL,
	`stateAbbr` varchar(2) NOT NULL,
	`filingName` varchar(128),
	`entityType` varchar(64),
	`dueDateLogic` text,
	`onlineFee` decimal(10,2),
	`mailFee` decimal(10,2),
	`lateFee` decimal(10,2),
	`lateFeeNote` text,
	`dissolutionTrigger` text,
	`portalUrl` varchar(512),
	`mailAddress` text,
	`notes` text,
	`lastVerified` date,
	`createdAt` timestamp NOT NULL DEFAULT (now()),
	`updatedAt` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `states_db_id` PRIMARY KEY(`id`),
	CONSTRAINT `states_db_stateAbbr_unique` UNIQUE(`stateAbbr`)
);
--> statement-breakpoint
ALTER TABLE `users` ADD `stripeCustomerId` varchar(128);--> statement-breakpoint
ALTER TABLE `users` ADD `subscriptionTier` enum('remind_guide','full_filing','compliance_package');--> statement-breakpoint
ALTER TABLE `users` ADD `subscriptionStatus` enum('active','inactive','past_due','canceled') DEFAULT 'inactive';--> statement-breakpoint
ALTER TABLE `users` ADD `stripeSubscriptionId` varchar(128);--> statement-breakpoint
ALTER TABLE `users` ADD `onboardingCompleted` boolean DEFAULT false NOT NULL;--> statement-breakpoint
ALTER TABLE `users` ADD `notifyAt90Days` boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE `users` ADD `notifyAt30Days` boolean DEFAULT true NOT NULL;--> statement-breakpoint
ALTER TABLE `users` ADD `notifyAt7Days` boolean DEFAULT true NOT NULL;