CREATE TABLE `activity_logs` (
	`id` int AUTO_INCREMENT NOT NULL,
	`client_id` int NOT NULL,
	`deal_id` int,
	`document_id` int,
	`event_type` enum('client_created','client_updated','deal_added','deal_updated','deal_status_changed','document_uploaded','document_deleted','note_updated') NOT NULL,
	`description` text NOT NULL,
	`metadata` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `activity_logs_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `buyers` (
	`id` int AUTO_INCREMENT NOT NULL,
	`deal_id` int NOT NULL,
	`full_name` varchar(255) NOT NULL,
	`passport_or_id` varchar(100),
	`birth_date` varchar(20),
	`phone` varchar(50),
	`email` varchar(320),
	`address` text,
	`nationality` varchar(100),
	`is_primary_buyer` boolean NOT NULL DEFAULT false,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	CONSTRAINT `buyers_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `clients` (
	`id` int AUTO_INCREMENT NOT NULL,
	`full_name` varchar(255) NOT NULL,
	`passport_or_id` varchar(100),
	`birth_date` varchar(20),
	`phone` varchar(50),
	`email` varchar(320),
	`address` text,
	`nationality` varchar(100),
	`role` enum('buyer','seller','both') NOT NULL DEFAULT 'buyer',
	`notes` text,
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp,
	CONSTRAINT `clients_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `deals` (
	`id` int AUTO_INCREMENT NOT NULL,
	`client_id` int NOT NULL,
	`buyer_client_id` int,
	`seller_client_id` int,
	`developer` varchar(255),
	`project_name` varchar(255),
	`unit_number` varchar(100),
	`sqft` varchar(50),
	`price` varchar(100),
	`booking_date` varchar(20),
	`agent_name` varchar(255),
	`lead_source` varchar(255),
	`status` enum('active','completed','cancelled','pending') NOT NULL DEFAULT 'active',
	`created_at` timestamp NOT NULL DEFAULT (now()),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	`deleted_at` timestamp,
	CONSTRAINT `deals_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `documents` (
	`id` int AUTO_INCREMENT NOT NULL,
	`client_id` int NOT NULL,
	`deal_id` int,
	`document_type` enum('booking_form','spa','passport','payment_plan','receipt','other') NOT NULL,
	`file_name` varchar(500) NOT NULL,
	`file_url` text NOT NULL,
	`file_key` text NOT NULL,
	`file_size` bigint,
	`mime_type` varchar(100),
	`uploaded_at` timestamp NOT NULL DEFAULT (now()),
	`deleted_at` timestamp,
	CONSTRAINT `documents_id` PRIMARY KEY(`id`)
);
--> statement-breakpoint
CREATE TABLE `notes` (
	`id` int AUTO_INCREMENT NOT NULL,
	`client_id` int NOT NULL,
	`content` text NOT NULL DEFAULT (''),
	`updated_at` timestamp NOT NULL DEFAULT (now()) ON UPDATE CURRENT_TIMESTAMP,
	CONSTRAINT `notes_id` PRIMARY KEY(`id`),
	CONSTRAINT `notes_client_id_unique` UNIQUE(`client_id`)
);