CREATE TABLE `contact_analytics_events` (
	`id` text PRIMARY KEY NOT NULL,
	`event_name` text NOT NULL,
	`request_type` text,
	`path` text NOT NULL,
	`session_hash` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_contact_analytics_event` ON `contact_analytics_events` (`event_name`,`created_at`);--> statement-breakpoint
CREATE TABLE `contact_attachments` (
	`id` text PRIMARY KEY NOT NULL,
	`request_id` text NOT NULL,
	`object_key` text NOT NULL,
	`original_name` text NOT NULL,
	`mime_type` text NOT NULL,
	`size_bytes` integer NOT NULL,
	`scan_status` text DEFAULT 'pending_scan' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_contact_attachments_request` ON `contact_attachments` (`request_id`);--> statement-breakpoint
CREATE TABLE `contact_audit_logs` (
	`id` text PRIMARY KEY NOT NULL,
	`request_id` text,
	`actor_user_id` text NOT NULL,
	`action` text NOT NULL,
	`old_value_json` text,
	`new_value_json` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_contact_audit_request` ON `contact_audit_logs` (`request_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `contact_interactions` (
	`id` text PRIMARY KEY NOT NULL,
	`contact_id` text NOT NULL,
	`request_id` text,
	`actor_user_id` text,
	`kind` text NOT NULL,
	`visibility` text DEFAULT 'internal' NOT NULL,
	`content` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_contact_interactions_contact` ON `contact_interactions` (`contact_id`,`created_at`);--> statement-breakpoint
CREATE INDEX `idx_contact_interactions_request` ON `contact_interactions` (`request_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `contact_notifications` (
	`id` text PRIMARY KEY NOT NULL,
	`request_id` text NOT NULL,
	`team` text NOT NULL,
	`user_id` text,
	`title` text NOT NULL,
	`body` text NOT NULL,
	`action_url` text NOT NULL,
	`read_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_contact_notifications_team` ON `contact_notifications` (`team`,`read_at`,`created_at`);--> statement-breakpoint
CREATE TABLE `contact_permissions` (
	`id` text PRIMARY KEY NOT NULL,
	`workspace_id` text,
	`user_id` text NOT NULL,
	`permission` text NOT NULL,
	`granted` integer DEFAULT false NOT NULL,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_contact_permissions_user_permission` ON `contact_permissions` (`user_id`,`permission`,`workspace_id`);--> statement-breakpoint
CREATE TABLE `contact_rate_limits` (
	`key` text PRIMARY KEY NOT NULL,
	`window_start` text NOT NULL,
	`request_count` integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE TABLE `contact_requests` (
	`id` text PRIMARY KEY NOT NULL,
	`reference` text NOT NULL,
	`contact_id` text NOT NULL,
	`workspace_id` text,
	`type` text NOT NULL,
	`subject` text NOT NULL,
	`message` text NOT NULL,
	`status` text DEFAULT 'new' NOT NULL,
	`priority` text DEFAULT 'normal' NOT NULL,
	`assigned_team` text,
	`assigned_user_id` text,
	`source` text DEFAULT 'direct' NOT NULL,
	`industry` text,
	`company_size` text,
	`country` text,
	`goals_json` text DEFAULT '[]' NOT NULL,
	`details_json` text DEFAULT '{}' NOT NULL,
	`utm_json` text DEFAULT '{}' NOT NULL,
	`lead_score` integer DEFAULT 0 NOT NULL,
	`idempotency_key` text NOT NULL,
	`fingerprint_hash` text NOT NULL,
	`archived_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE no action
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_contact_requests_reference` ON `contact_requests` (`reference`);--> statement-breakpoint
CREATE UNIQUE INDEX `idx_contact_requests_idempotency` ON `contact_requests` (`idempotency_key`);--> statement-breakpoint
CREATE INDEX `idx_contact_requests_type_status` ON `contact_requests` (`type`,`status`,`created_at`);--> statement-breakpoint
CREATE INDEX `idx_contact_requests_contact` ON `contact_requests` (`contact_id`,`created_at`);--> statement-breakpoint
CREATE TABLE `contact_routing_rules` (
	`id` text PRIMARY KEY NOT NULL,
	`name` text NOT NULL,
	`request_type` text,
	`country` text,
	`industry` text,
	`company_size` text,
	`minimum_score` integer,
	`strategy` text DEFAULT 'team' NOT NULL,
	`team` text NOT NULL,
	`user_id` text,
	`active` integer DEFAULT true NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_contact_routing_rules_type` ON `contact_routing_rules` (`request_type`,`active`,`sort_order`);--> statement-breakpoint
CREATE TABLE `contact_settings` (
	`id` text PRIMARY KEY NOT NULL,
	`sales_email` text,
	`support_email` text,
	`partnership_email` text,
	`general_email` text,
	`phone` text,
	`whatsapp` text,
	`business_hours_json` text DEFAULT '{}' NOT NULL,
	`office_json` text DEFAULT '{}' NOT NULL,
	`social_json` text DEFAULT '{}' NOT NULL,
	`calendar_provider` text,
	`email_provider` text,
	`demo_duration_minutes` integer DEFAULT 30 NOT NULL,
	`max_files` integer DEFAULT 3 NOT NULL,
	`max_file_bytes` integer DEFAULT 5242880 NOT NULL,
	`allowed_file_types_json` text DEFAULT '["application/pdf","application/vnd.openxmlformats-officedocument.wordprocessingml.document","image/jpeg","image/png","image/webp"]' NOT NULL,
	`privacy_url` text,
	`terms_url` text,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE TABLE `contact_webhook_events` (
	`id` text PRIMARY KEY NOT NULL,
	`event_type` text NOT NULL,
	`request_id` text NOT NULL,
	`status` text DEFAULT 'pending' NOT NULL,
	`attempts` integer DEFAULT 0 NOT NULL,
	`next_attempt_at` text,
	`last_error` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`processed_at` text,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE INDEX `idx_contact_webhooks_status` ON `contact_webhook_events` (`status`,`next_attempt_at`);--> statement-breakpoint
CREATE TABLE `crm_contacts` (
	`id` text PRIMARY KEY NOT NULL,
	`full_name` text NOT NULL,
	`email` text,
	`normalized_email` text,
	`phone` text,
	`normalized_phone` text,
	`country` text,
	`company` text,
	`job_title` text,
	`marketing_consent` integer DEFAULT false NOT NULL,
	`consent_at` text,
	`consent_source` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE INDEX `idx_crm_contacts_email` ON `crm_contacts` (`normalized_email`);--> statement-breakpoint
CREATE INDEX `idx_crm_contacts_phone` ON `crm_contacts` (`normalized_phone`);--> statement-breakpoint
CREATE TABLE `demo_requests` (
	`id` text PRIMARY KEY NOT NULL,
	`request_id` text NOT NULL,
	`interests_json` text DEFAULT '[]' NOT NULL,
	`timezone` text,
	`requested_date` text,
	`requested_time` text,
	`duration_minutes` integer DEFAULT 30 NOT NULL,
	`meeting_type` text DEFAULT 'request' NOT NULL,
	`status` text DEFAULT 'requested' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_demo_requests_request` ON `demo_requests` (`request_id`);--> statement-breakpoint
CREATE TABLE `lead_scoring_rules` (
	`id` text PRIMARY KEY NOT NULL,
	`key` text NOT NULL,
	`label_ar` text NOT NULL,
	`label_en` text NOT NULL,
	`field` text NOT NULL,
	`operator` text NOT NULL,
	`value_json` text NOT NULL,
	`points` integer NOT NULL,
	`active` integer DEFAULT true NOT NULL,
	`sort_order` integer DEFAULT 0 NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_lead_scoring_rules_key` ON `lead_scoring_rules` (`key`);--> statement-breakpoint
CREATE TABLE `partnership_requests` (
	`id` text PRIMARY KEY NOT NULL,
	`request_id` text NOT NULL,
	`partnership_type` text NOT NULL,
	`company_description` text NOT NULL,
	`proposal` text NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_partnership_requests_request` ON `partnership_requests` (`request_id`);--> statement-breakpoint
CREATE TABLE `sales_leads` (
	`id` text PRIMARY KEY NOT NULL,
	`contact_id` text NOT NULL,
	`request_id` text NOT NULL,
	`stage` text DEFAULT 'new' NOT NULL,
	`score` integer DEFAULT 0 NOT NULL,
	`source` text DEFAULT 'website' NOT NULL,
	`owner_user_id` text,
	`next_follow_up_at` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`contact_id`) REFERENCES `crm_contacts`(`id`) ON UPDATE no action ON DELETE no action,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_sales_leads_request` ON `sales_leads` (`request_id`);--> statement-breakpoint
CREATE INDEX `idx_sales_leads_stage_score` ON `sales_leads` (`stage`,`score`);--> statement-breakpoint
CREATE TABLE `support_tickets` (
	`id` text PRIMARY KEY NOT NULL,
	`request_id` text NOT NULL,
	`workspace_id` text NOT NULL,
	`category` text NOT NULL,
	`module` text NOT NULL,
	`status` text DEFAULT 'new' NOT NULL,
	`priority` text DEFAULT 'normal' NOT NULL,
	`assigned_team` text DEFAULT 'support' NOT NULL,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	`updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL,
	FOREIGN KEY (`request_id`) REFERENCES `contact_requests`(`id`) ON UPDATE no action ON DELETE cascade,
	FOREIGN KEY (`workspace_id`) REFERENCES `workspaces`(`id`) ON UPDATE no action ON DELETE cascade
);
--> statement-breakpoint
CREATE UNIQUE INDEX `idx_support_tickets_request` ON `support_tickets` (`request_id`);--> statement-breakpoint
CREATE INDEX `idx_support_tickets_workspace_status` ON `support_tickets` (`workspace_id`,`status`);--> statement-breakpoint
PRAGMA optimize;