-- FreelanceHub Database Schema
-- MySQL 8.0+


-- =====================================================
-- TABELLA: clients
-- Clienti del freelance
-- =====================================================
CREATE TABLE `clients` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(150) NOT NULL,
    `email` VARCHAR(255) NULL,
    `phone` VARCHAR(50) NULL,
    `company` VARCHAR(150) NULL,
    `address` TEXT NULL,
    `notes` TEXT NULL,
    `color` VARCHAR(7) DEFAULT '#3B82F6',
    `priority_level` ENUM('low', 'normal', 'high', 'critical') DEFAULT 'normal',
    `hourly_rate` DECIMAL(10,2) NULL COMMENT 'Override della tariffa utente',
    `is_active` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_active` (`user_id`, `is_active`),
    INDEX `idx_priority` (`priority_level`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABELLA: projects
-- Progetti per cliente
-- =====================================================
CREATE TABLE `projects` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `client_id` INT UNSIGNED NULL,
    `name` VARCHAR(200) NOT NULL,
    `description` TEXT NULL,
    `color` VARCHAR(7) DEFAULT '#10B981',
    `status` ENUM('planning', 'active', 'on_hold', 'completed', 'cancelled') DEFAULT 'planning',
    `start_date` DATE NULL,
    `due_date` DATE NULL,
    `estimated_hours` DECIMAL(8,2) NULL,
    `budget` DECIMAL(12,2) NULL,
    `is_billable` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE SET NULL,
    INDEX `idx_user_status` (`user_id`, `status`),
    INDEX `idx_client` (`client_id`),
    INDEX `idx_due_date` (`due_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABELLA: tasks
-- Task interni del sistema
-- =====================================================
CREATE TABLE `tasks` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `project_id` INT UNSIGNED NULL,
    `client_id` INT UNSIGNED NULL,
    `parent_task_id` INT UNSIGNED NULL COMMENT 'Per subtask',
    `title` VARCHAR(300) NOT NULL,
    `description` TEXT NULL,
    `status` ENUM('backlog', 'todo', 'in_progress', 'review', 'completed', 'cancelled') DEFAULT 'todo',
    `priority` ENUM('lowest', 'low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    `due_date` DATE NULL,
    `due_time` TIME NULL,
    `start_date` DATE NULL,
    `estimated_minutes` INT UNSIGNED NULL,
    `actual_minutes` INT UNSIGNED DEFAULT 0,
    `is_recurring` TINYINT(1) DEFAULT 0,
    `recurrence_rule` VARCHAR(255) NULL COMMENT 'RRULE format',
    `tags` JSON NULL,
    `ai_priority_score` TINYINT UNSIGNED NULL COMMENT '0-100',
    `ai_suggested_deadline` DATE NULL,
    `completed_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`parent_task_id`) REFERENCES `tasks`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_status` (`user_id`, `status`),
    INDEX `idx_project` (`project_id`),
    INDEX `idx_client` (`client_id`),
    INDEX `idx_due_date` (`due_date`),
    INDEX `idx_priority` (`priority`),
    INDEX `idx_ai_score` (`ai_priority_score` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABELLA: time_entries
-- Registrazioni tempo lavoro
-- =====================================================
CREATE TABLE `time_entries` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `task_id` INT UNSIGNED NULL,
    `project_id` INT UNSIGNED NULL,
    `client_id` INT UNSIGNED NULL,
    `start_time` DATETIME NOT NULL,
    `end_time` DATETIME NULL,
    `duration_minutes` INT UNSIGNED NULL COMMENT 'Calcolato o inserito manualmente',
    `description` TEXT NULL,
    `is_billable` TINYINT(1) DEFAULT 1,
    `hourly_rate` DECIMAL(10,2) NULL,
    `is_running` TINYINT(1) DEFAULT 0 COMMENT 'Timer attivo',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`project_id`) REFERENCES `projects`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`client_id`) REFERENCES `clients`(`id`) ON DELETE SET NULL,
    INDEX `idx_user_date` (`user_id`, `start_time`),
    INDEX `idx_task` (`task_id`),
    INDEX `idx_client_billable` (`client_id`, `is_billable`),
    INDEX `idx_running` (`user_id`, `is_running`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABELLA: external_tasks
-- Task sincronizzati da fonti esterne
-- =====================================================
CREATE TABLE `external_tasks` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `integration_account_id` INT UNSIGNED NOT NULL,
    `local_task_id` INT UNSIGNED NULL COMMENT 'Link al task interno se mappato',
    `external_id` VARCHAR(255) NOT NULL COMMENT 'ID nel servizio esterno',
    `external_url` VARCHAR(500) NULL,
    `title` VARCHAR(300) NOT NULL,
    `description` TEXT NULL,
    `status` VARCHAR(50) NULL,
    `priority` VARCHAR(50) NULL,
    `due_date` DATE NULL,
    `assignee` VARCHAR(255) NULL,
    `project_name` VARCHAR(200) NULL COMMENT 'Nome progetto nel servizio esterno',
    `workspace_name` VARCHAR(200) NULL,
    `raw_data` JSON NULL COMMENT 'Dati grezzi dal servizio',
    `is_synced` TINYINT(1) DEFAULT 1,
    `last_synced_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`integration_account_id`) REFERENCES `integration_accounts`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`local_task_id`) REFERENCES `tasks`(`id`) ON DELETE SET NULL,
    UNIQUE KEY `uk_account_external` (`integration_account_id`, `external_id`),
    INDEX `idx_user` (`user_id`),
    INDEX `idx_local_task` (`local_task_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABELLA: calendar_events
-- Eventi calendario sincronizzati
-- =====================================================
CREATE TABLE `calendar_events` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `integration_account_id` INT UNSIGNED NULL,
    `task_id` INT UNSIGNED NULL COMMENT 'Se evento collegato a task',
    `external_id` VARCHAR(255) NULL,
    `title` VARCHAR(300) NOT NULL,
    `description` TEXT NULL,
    `location` VARCHAR(500) NULL,
    `start_datetime` DATETIME NOT NULL,
    `end_datetime` DATETIME NOT NULL,
    `is_all_day` TINYINT(1) DEFAULT 0,
    `is_recurring` TINYINT(1) DEFAULT 0,
    `recurrence_rule` VARCHAR(255) NULL,
    `color` VARCHAR(7) NULL,
    `reminder_minutes` INT NULL,
    `source` ENUM('local', 'google', 'outlook', 'asana', 'clickup') DEFAULT 'local',
    `is_synced` TINYINT(1) DEFAULT 1,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`integration_account_id`) REFERENCES `integration_accounts`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE SET NULL,
    INDEX `idx_user_date` (`user_id`, `start_datetime`),
    INDEX `idx_external` (`integration_account_id`, `external_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABELLA: ai_recommendations
-- Suggerimenti generati dall'IA
-- =====================================================
CREATE TABLE `ai_recommendations` (
    `id` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `task_id` INT UNSIGNED NULL,
    `type` ENUM('priority', 'deadline', 'time_allocation', 'workload', 'general') NOT NULL,
    `title` VARCHAR(200) NOT NULL,
    `description` TEXT NOT NULL,
    `priority_score` TINYINT UNSIGNED NULL,
    `suggested_action` JSON NULL,
    `reasoning` TEXT NULL COMMENT 'Spiegazione IA',
    `status` ENUM('pending', 'accepted', 'dismissed', 'expired') DEFAULT 'pending',
    `expires_at` TIMESTAMP NULL,
    `accepted_at` TIMESTAMP NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`task_id`) REFERENCES `tasks`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_status` (`user_id`, `status`),
    INDEX `idx_task` (`task_id`),
    INDEX `idx_type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- TABELLA: activity_log
-- Log attività per analytics e IA
-- =====================================================
CREATE TABLE `activity_log` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT UNSIGNED NOT NULL,
    `entity_type` VARCHAR(50) NOT NULL COMMENT 'task, project, client, etc',
    `entity_id` INT UNSIGNED NOT NULL,
    `action` VARCHAR(50) NOT NULL COMMENT 'created, updated, completed, etc',
    `changes` JSON NULL,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
    INDEX `idx_user_date` (`user_id`, `created_at`),
    INDEX `idx_entity` (`entity_type`, `entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

