CREATE TABLE `annotations` (
	`id` int AUTO_INCREMENT NOT NULL,
	`model_id` int NOT NULL,
	`viewpoint_id` int,
	`type` enum('pin','line','measure') NOT NULL,
	`points` json NOT NULL,
	`normal` json,
	`color` varchar(20) NOT NULL DEFAULT '#ef4444',
	`label` varchar(255),
	`status` enum('open','resolved') NOT NULL DEFAULT 'open',
	`created_by_user_id` int,
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `annotations_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `client_sessions` (
	`id` int AUTO_INCREMENT NOT NULL,
	`share_link_id` int NOT NULL,
	`display_name` varchar(100) NOT NULL,
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `client_sessions_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `clients` (
	`id` int AUTO_INCREMENT NOT NULL,
	`company_id` int NOT NULL,
	`name` varchar(150) NOT NULL,
	`contact_name` varchar(150),
	`email` varchar(190),
	`phone` varchar(40),
	`notes` text,
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `clients_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `comments` (
	`id` int AUTO_INCREMENT NOT NULL,
	`annotation_id` int NOT NULL,
	`author_type` enum('staff','client') NOT NULL,
	`author_user_id` int,
	`author_client_session_id` int,
	`body` text NOT NULL,
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `comments_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `companies` (
	`id` int AUTO_INCREMENT NOT NULL,
	`name` varchar(150) NOT NULL,
	`logo_path` varchar(255),
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `companies_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `models` (
	`id` int AUTO_INCREMENT NOT NULL,
	`project_id` int NOT NULL,
	`version` int NOT NULL,
	`label` varchar(150),
	`file_path` varchar(255) NOT NULL,
	`file_size` int NOT NULL,
	`triangle_count` int,
	`texture_info` json,
	`is_active` boolean NOT NULL DEFAULT false,
	`uploaded_by` int,
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `models_id` PRIMARY KEY(`id`),
	CONSTRAINT `models_project_version_unique` UNIQUE(`project_id`,`version`)
);
--> statement-breakpoint
CREATE TABLE `projects` (
	`id` int AUTO_INCREMENT NOT NULL,
	`company_id` int NOT NULL,
	`client_id` int NOT NULL,
	`name` varchar(200) NOT NULL,
	`description` text,
	`status` enum('draft','presented','approved','archived') NOT NULL DEFAULT 'draft',
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	`updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `projects_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `share_links` (
	`id` int AUTO_INCREMENT NOT NULL,
	`project_id` int NOT NULL,
	`token` varchar(64) NOT NULL,
	`password_hash` varchar(255),
	`expires_at` datetime,
	`is_active` boolean NOT NULL DEFAULT true,
	`last_accessed_at` datetime,
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `share_links_id` PRIMARY KEY(`id`),
	CONSTRAINT `share_links_token_unique` UNIQUE(`token`)
);
--> statement-breakpoint
CREATE TABLE `users` (
	`id` int AUTO_INCREMENT NOT NULL,
	`company_id` int NOT NULL,
	`name` varchar(150) NOT NULL,
	`email` varchar(190) NOT NULL,
	`password_hash` varchar(255) NOT NULL,
	`role` enum('admin','staff') NOT NULL DEFAULT 'staff',
	`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT `users_id` PRIMARY KEY(`id`),
	CONSTRAINT `users_email_unique` UNIQUE(`email`)
);
--> statement-breakpoint
CREATE TABLE `viewpoints` (
	`id` int AUTO_INCREMENT NOT NULL,
	`model_id` int NOT NULL,
	`title` varchar(150) NOT NULL,
	`description` text,
	`camera_position` json NOT NULL,
	`camera_target` json NOT NULL,
	`sort_order` int NOT NULL DEFAULT 0,
	CONSTRAINT `viewpoints_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
ALTER TABLE `annotations` ADD CONSTRAINT `annotations_model_id_models_id_fk` FOREIGN KEY (`model_id`) REFERENCES `models`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `annotations` ADD CONSTRAINT `annotations_viewpoint_id_viewpoints_id_fk` FOREIGN KEY (`viewpoint_id`) REFERENCES `viewpoints`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `annotations` ADD CONSTRAINT `annotations_created_by_user_id_users_id_fk` FOREIGN KEY (`created_by_user_id`) REFERENCES `users`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `client_sessions` ADD CONSTRAINT `client_sessions_share_link_id_share_links_id_fk` FOREIGN KEY (`share_link_id`) REFERENCES `share_links`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `clients` ADD CONSTRAINT `clients_company_id_companies_id_fk` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `comments` ADD CONSTRAINT `comments_annotation_id_annotations_id_fk` FOREIGN KEY (`annotation_id`) REFERENCES `annotations`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `comments` ADD CONSTRAINT `comments_author_user_id_users_id_fk` FOREIGN KEY (`author_user_id`) REFERENCES `users`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `comments` ADD CONSTRAINT `comments_author_client_session_id_client_sessions_id_fk` FOREIGN KEY (`author_client_session_id`) REFERENCES `client_sessions`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `models` ADD CONSTRAINT `models_project_id_projects_id_fk` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `models` ADD CONSTRAINT `models_uploaded_by_users_id_fk` FOREIGN KEY (`uploaded_by`) REFERENCES `users`(`id`) ON DELETE set null ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `projects` ADD CONSTRAINT `projects_company_id_companies_id_fk` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `projects` ADD CONSTRAINT `projects_client_id_clients_id_fk` FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE restrict ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `share_links` ADD CONSTRAINT `share_links_project_id_projects_id_fk` FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `users` ADD CONSTRAINT `users_company_id_companies_id_fk` FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
ALTER TABLE `viewpoints` ADD CONSTRAINT `viewpoints_model_id_models_id_fk` FOREIGN KEY (`model_id`) REFERENCES `models`(`id`) ON DELETE cascade ON UPDATE no action;--> statement-breakpoint
CREATE INDEX `annotations_model_idx` ON `annotations` (`model_id`);--> statement-breakpoint
CREATE INDEX `annotations_viewpoint_idx` ON `annotations` (`viewpoint_id`);--> statement-breakpoint
CREATE INDEX `client_sessions_share_link_idx` ON `client_sessions` (`share_link_id`);--> statement-breakpoint
CREATE INDEX `clients_company_idx` ON `clients` (`company_id`);--> statement-breakpoint
CREATE INDEX `comments_annotation_idx` ON `comments` (`annotation_id`);--> statement-breakpoint
CREATE INDEX `projects_company_idx` ON `projects` (`company_id`);--> statement-breakpoint
CREATE INDEX `projects_client_idx` ON `projects` (`client_id`);--> statement-breakpoint
CREATE INDEX `share_links_project_idx` ON `share_links` (`project_id`);--> statement-breakpoint
CREATE INDEX `users_company_idx` ON `users` (`company_id`);--> statement-breakpoint
CREATE INDEX `viewpoints_model_idx` ON `viewpoints` (`model_id`);