-- ============================================================
-- 10_file_manager_module.sql
-- Tenant File Manager Module
-- Global per-folder filename uniqueness (final verified build)
-- ============================================================


-- 0. FOLDER TABLE
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_file_folders` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `folder_name` VARCHAR(255) NOT NULL,
  `parent_id` INT DEFAULT NULL,                       -- Self-referencing for nesting
  `created_by` INT DEFAULT NULL,                      -- FK → tenant_users.id
  `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `bookmarked` TINYINT(1) NOT NULL DEFAULT 0,
  `bookmarked_at` DATETIME NULL,
  `last_modified_on` DATETIME NULL DEFAULT NULL,
  `soft_deleted` TINYINT(1) NOT NULL DEFAULT 0,
  `soft_deleted_at` DATETIME DEFAULT NULL,
  `deleted_on` DATETIME NULL,
  `deleted_by` INT DEFAULT NULL,
  PRIMARY KEY (`id`),
  FOREIGN KEY (`parent_id`)
    REFERENCES `{{TABLE_PREFIX}}_file_folders`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`created_by`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



-- 1. FILE MANAGER TABLE  (Main file info)
-- ------------------------------------------------------------
-- ============================================================
-- 10_file_manager_module.sql
-- Tenant File Manager Module (collision-safe version)
-- ============================================================


-- 1️⃣ CREATE TABLE
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_file_manager` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_key` TEXT NOT NULL,                              -- Full S3 key
  `original_filename` VARCHAR(255) NOT NULL,             -- Original file name
  `mime_type` TEXT NOT NULL,
  `file_size` BIGINT NOT NULL,
  `upload_status` ENUM('initiated','in_progress','completed','failed')
        DEFAULT 'initiated',
  `bookmarked` TINYINT(1) NOT NULL DEFAULT '0',
  `bookmarked_at` DATETIME DEFAULT NULL,
  `upload_id` TEXT,
  `thumbnail_key` TEXT,
  `remarks` TEXT,
  `uploaded_by` INT NOT NULL,                            -- FK → tenant_users.id
  `folder_id` INT DEFAULT NULL,                          -- FK → file_folders.id
  `uploaded_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `created_at` DATETIME DEFAULT NULL,
  `last_modified_on` DATETIME DEFAULT NULL,
  `completed_at` DATETIME DEFAULT NULL,
  `soft_deleted` TINYINT(1) DEFAULT '0',
  `soft_deleted_at` DATETIME DEFAULT NULL,
  `deleted_on` DATETIME DEFAULT NULL,
  `deleted_by` INT DEFAULT NULL,
  `completed_active` TINYINT GENERATED ALWAYS AS (
      CASE
        WHEN `soft_deleted` = 0 AND `upload_status` = 'completed' THEN 1
        ELSE 0
      END
  ) STORED,
  PRIMARY KEY (`id`),
  KEY `{{TABLE_PREFIX}}_fm_uploaded_by_idx` (`uploaded_by`),
  KEY `{{TABLE_PREFIX}}_fm_folder_id_idx` (`folder_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



-- 2️⃣ ADD FOREIGN KEYS (unique, prefix-based constraint names)
ALTER TABLE `{{TABLE_PREFIX}}_file_manager`
  ADD CONSTRAINT `{{TABLE_PREFIX}}_fk_fm_uploaded_by`
    FOREIGN KEY (`uploaded_by`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users` (`id`) ON DELETE CASCADE,
  ADD CONSTRAINT `{{TABLE_PREFIX}}_fk_fm_folder_id`
    FOREIGN KEY (`folder_id`)
    REFERENCES `{{TABLE_PREFIX}}_file_folders` (`id`) ON DELETE SET NULL;




-- 2. FILE PARTS TABLE  (for multipart tracking)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_file_parts` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,                             -- FK → file_manager.id
  `part_number` INT NOT NULL,
  `etag` TEXT NOT NULL,
  `uploaded_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `file_id_idx` (`file_id`),
  CONSTRAINT `fk_parts_file`
    FOREIGN KEY (`file_id`)
    REFERENCES `{{TABLE_PREFIX}}_file_manager`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



-- 3. FILE PERMISSIONS TABLE  (for sharing access)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_file_permissions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,                             -- FK → file_manager.id
  `user_id` INT NOT NULL,                             -- FK → tenant_users.id
  `permission_type` ENUM('read','write') DEFAULT 'read',
  PRIMARY KEY (`id`),
  KEY `file_id_idx` (`file_id`),
  KEY `user_id_idx` (`user_id`),
  CONSTRAINT `fk_permission_file`
    FOREIGN KEY (`file_id`)
    REFERENCES `{{TABLE_PREFIX}}_file_manager`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_permission_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



-- 4. FILE TRASH TABLE  (track deletions)
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_file_trash` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `file_id` INT NOT NULL,                             -- FK → file_manager.id
  `trashed_by` INT NOT NULL,                          -- FK → tenant_users.id
  `trashed_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `reason` TEXT,
  PRIMARY KEY (`id`),
  KEY `file_id_idx` (`file_id`),
  CONSTRAINT `fk_trash_file`
    FOREIGN KEY (`file_id`)
    REFERENCES `{{TABLE_PREFIX}}_file_manager`(`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_trash_user`
    FOREIGN KEY (`trashed_by`)
    REFERENCES `{{TABLE_PREFIX}}_tenant_users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



-- 5. RECENT ITEMS TABLE
-- ------------------------------------------------------------
CREATE TABLE IF NOT EXISTS `{{TABLE_PREFIX}}_fm_recent_items` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `user_id` BIGINT NOT NULL,
  `item_type` ENUM('folder','file') NOT NULL,
  `item_id` BIGINT NOT NULL,
  `opened_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_item` (`user_id`,`item_type`,`item_id`),
  KEY `idx_user_type_opened` (`user_id`,`item_type`,`opened_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
