-- Dubai Real Estate CRM - clean MySQL/MariaDB schema for cPanel
-- Import this file into a NEW empty database using phpMyAdmin.
SET NAMES utf8mb4;
SET time_zone = '+00:00';

CREATE TABLE IF NOT EXISTS `users` (
  `id` int NOT NULL AUTO_INCREMENT,
  `openId` varchar(64) NOT NULL,
  `name` text NULL,
  `email` varchar(320) NULL,
  `loginMethod` varchar(64) NULL,
  `role` enum('user','admin') NOT NULL DEFAULT 'user',
  `createdAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `lastSignedIn` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_openId_unique` (`openId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `clients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) NOT NULL,
  `passport_or_id` varchar(100) NULL,
  `birth_date` varchar(20) NULL,
  `phone` varchar(50) NULL,
  `email` varchar(320) NULL,
  `address` text NULL,
  `nationality` varchar(100) NULL,
  `role` enum('buyer','seller','both') NOT NULL DEFAULT 'buyer',
  `notes` text NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_clients_deleted` (`deleted_at`),
  KEY `idx_clients_name` (`full_name`),
  KEY `idx_clients_phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `deals` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int NOT NULL,
  `buyer_client_id` int NULL,
  `seller_client_id` int NULL,
  `developer` varchar(255) NULL,
  `project_name` varchar(255) NULL,
  `unit_number` varchar(100) NULL,
  `sqft` varchar(50) NULL,
  `price` varchar(100) NULL,
  `commission` varchar(100) NULL,
  `property_type` varchar(50) NULL,
  `emirate` varchar(100) NULL,
  `booking_date` varchar(20) NULL,
  `agent_name` varchar(255) NULL,
  `lead_source` varchar(255) NULL,
  `status` enum('active','completed','cancelled','pending') NOT NULL DEFAULT 'active',
  `commission_paid` enum('unpaid','paid','partial') NOT NULL DEFAULT 'unpaid',
  `commission_paid_amount` varchar(100) NULL,
  `dld_fee` decimal(15,2) NULL DEFAULT 0.00,
  `admin_fee` decimal(15,2) NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_deals_client` (`client_id`),
  KEY `idx_deals_deleted` (`deleted_at`),
  KEY `idx_deals_agent` (`agent_name`),
  KEY `idx_deals_developer` (`developer`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `buyers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deal_id` int NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `passport_or_id` varchar(100) NULL,
  `birth_date` varchar(20) NULL,
  `phone` varchar(50) NULL,
  `email` varchar(320) NULL,
  `address` text NULL,
  `nationality` varchar(100) NULL,
  `is_primary_buyer` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_buyers_deal` (`deal_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `documents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int NOT NULL,
  `deal_id` int NULL,
  `document_type` enum('eoi','oqood','booking','spa','kyc','name_screening','emirates_id','apartment_details','receipts','passport','other','booking_form','payment_plan','receipt') NOT NULL,
  `file_name` varchar(500) NOT NULL,
  `file_url` text NOT NULL,
  `file_key` text NOT NULL,
  `file_size` bigint NULL,
  `mime_type` varchar(100) NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_documents_client` (`client_id`),
  KEY `idx_documents_deal` (`deal_id`),
  KEY `idx_documents_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `notes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int NOT NULL,
  `content` text NOT NULL,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `notes_client_id_unique` (`client_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `activity_logs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int NOT NULL,
  `deal_id` int NULL,
  `document_id` int NULL,
  `event_type` enum('client_created','client_updated','deal_added','deal_updated','deal_status_changed','document_uploaded','document_deleted','note_updated','receipt_added','receipt_deleted') NOT NULL,
  `description` text NOT NULL,
  `metadata` text NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_activity_client` (`client_id`),
  KEY `idx_activity_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `developer_agreements` (
  `id` int NOT NULL AUTO_INCREMENT,
  `developer_name` varchar(255) NOT NULL,
  `agreement_date` varchar(20) NULL,
  `file_name` varchar(500) NULL,
  `file_url` text NULL,
  `file_key` text NULL,
  `file_size` bigint NULL,
  `mime_type` varchar(100) NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_agreements_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `employment_contracts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `employee_name` varchar(255) NOT NULL,
  `contract_date` varchar(20) NULL,
  `file_name` varchar(500) NULL,
  `file_url` text NULL,
  `file_key` text NULL,
  `file_size` bigint NULL,
  `mime_type` varchar(100) NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_contracts_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `calendar_tasks` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(500) NOT NULL,
  `description` text NULL,
  `task_date` varchar(20) NOT NULL,
  `task_time` varchar(10) NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_calendar_date` (`task_date`),
  KEY `idx_calendar_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `receipts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `deal_id` int NOT NULL,
  `client_id` int NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `receipt_date` varchar(20) NOT NULL,
  `notes` text NULL,
  `file_name` varchar(500) NULL,
  `file_key` text NULL,
  `file_url` text NULL,
  `file_size` bigint NULL,
  `mime_type` varchar(100) NULL,
  `document_id` int NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_receipts_deal` (`deal_id`),
  KEY `idx_receipts_client` (`client_id`),
  KEY `idx_receipts_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `trs_receipts` (
  `id` int NOT NULL AUTO_INCREMENT,
  `receipt_number` int NOT NULL,
  `receipt_date` varchar(20) NOT NULL,
  `receipt_year` int NOT NULL,
  `vendor` varchar(500) NOT NULL,
  `amount` decimal(15,2) NOT NULL,
  `has_vat` tinyint(1) NOT NULL DEFAULT 0,
  `vat_amount` decimal(15,2) NULL,
  `file_name` varchar(500) NULL,
  `file_key` text NULL,
  `file_url` text NULL,
  `file_size` bigint NULL,
  `mime_type` varchar(100) NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_trs_receipt_number` (`receipt_number`),
  KEY `idx_trs_receipt_year` (`receipt_year`),
  KEY `idx_trs_deleted` (`deleted_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;