CREATE TABLE IF NOT EXISTS roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  slug VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  is_system TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permissions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  module VARCHAR(80) NOT NULL,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
  role_id BIGINT UNSIGNED NOT NULL,
  permission_id BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY(role_id, permission_id),
  CONSTRAINT fk_role_permissions_role FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE CASCADE,
  CONSTRAINT fk_role_permissions_permission FOREIGN KEY(permission_id) REFERENCES permissions(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  avatar VARCHAR(255) NULL,
  quota_gb INT UNSIGNED NOT NULL DEFAULT 5,
  storage_folder VARCHAR(190) NOT NULL UNIQUE,
  role_id BIGINT UNSIGNED NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  email_verified_at DATETIME NULL,
  last_login_at DATETIME NULL,
  last_login_ip VARCHAR(45) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_users_role FOREIGN KEY(role_id) REFERENCES roles(id) ON DELETE SET NULL,
  INDEX idx_users_status (is_active, deleted_at),
  INDEX idx_users_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id BIGINT UNSIGNED NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_audit_user_created (user_id, created_at),
  INDEX idx_audit_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS login_attempts (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  attempt_key CHAR(64) NOT NULL,
  attempted_at DATETIME NOT NULL,
  INDEX idx_login_attempts_key_time (attempt_key, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS settings (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  setting_key VARCHAR(120) NOT NULL UNIQUE,
  setting_value LONGTEXT NULL,
  is_public TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE IF NOT EXISTS drive_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  owner_id BIGINT UNSIGNED NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  type ENUM('folder','file') NOT NULL,
  name VARCHAR(190) NOT NULL,
  stored_name VARCHAR(255) NULL,
  extension VARCHAR(20) NULL,
  mime_type VARCHAR(150) NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  checksum CHAR(64) NULL,
  ocr_status ENUM('none','pending','processing','completed','error') NOT NULL DEFAULT 'none',
  ocr_text MEDIUMTEXT NULL,
  ocr_error TEXT NULL,
  ocr_processed_at DATETIME NULL,
  is_favorite TINYINT(1) NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  deleted_at DATETIME NULL,
  CONSTRAINT fk_drive_owner FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_drive_parent FOREIGN KEY(parent_id) REFERENCES drive_items(id) ON DELETE CASCADE,
  INDEX idx_drive_owner_parent (owner_id,parent_id,deleted_at),
  INDEX idx_drive_owner_favorite (owner_id,is_favorite,deleted_at),
  INDEX idx_drive_name (name),
  FULLTEXT KEY ft_drive_items_ocr_text (ocr_text)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- iDrive Modulo 3: Compartilhamentos

CREATE TABLE IF NOT EXISTS shares (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id BIGINT UNSIGNED NOT NULL,
  owner_id BIGINT UNSIGNED NOT NULL,
  recipient_user_id BIGINT UNSIGNED NULL,
  share_type ENUM('user','link') NOT NULL DEFAULT 'user',
  token CHAR(64) NULL UNIQUE,
  permission_view TINYINT(1) NOT NULL DEFAULT 1,
  permission_download TINYINT(1) NOT NULL DEFAULT 1,
  permission_edit TINYINT(1) NOT NULL DEFAULT 0,
  permission_comment TINYINT(1) NOT NULL DEFAULT 0,
  permission_reshare TINYINT(1) NOT NULL DEFAULT 0,
  password_hash VARCHAR(255) NULL,
  expires_at DATETIME NULL,
  max_downloads INT UNSIGNED NULL,
  download_count INT UNSIGNED NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  revoked_at DATETIME NULL,
  CONSTRAINT fk_shares_item FOREIGN KEY(item_id) REFERENCES drive_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_shares_owner FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE,
  CONSTRAINT fk_shares_recipient FOREIGN KEY(recipient_user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_shares_recipient (recipient_user_id,is_active,expires_at),
  INDEX idx_shares_owner (owner_id,is_active,created_at),
  INDEX idx_shares_item (item_id,is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS share_access_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  share_id BIGINT UNSIGNED NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  action ENUM('view','download') NOT NULL,
  ip_address VARCHAR(45) NULL,
  user_agent VARCHAR(500) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_share_access_share FOREIGN KEY(share_id) REFERENCES shares(id) ON DELETE CASCADE,
  CONSTRAINT fk_share_access_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_share_access_share_created (share_id,created_at),
  INDEX idx_share_access_user_created (user_id,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO permissions(module,name,slug,description,created_at)
SELECT 'Compartilhamentos','Visualizar compartilhamentos','shares.view','Acessar itens compartilhados',NOW()
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE slug='shares.view');
INSERT INTO permissions(module,name,slug,description,created_at)
SELECT 'Compartilhamentos','Criar compartilhamentos','shares.create','Compartilhar arquivos e pastas',NOW()
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE slug='shares.create');
INSERT INTO permissions(module,name,slug,description,created_at)
SELECT 'Compartilhamentos','Revogar compartilhamentos','shares.revoke','Revogar compartilhamentos próprios',NOW()
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE slug='shares.revoke');
INSERT INTO permissions(module,name,slug,description,created_at)
SELECT 'Arquivos','Excluir arquivos de usuários','admin.files.delete','Excluir definitivamente arquivos e pastas pertencentes a qualquer usuário',NOW()
WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE slug='admin.files.delete');

INSERT IGNORE INTO role_permissions(role_id,permission_id)
SELECT r.id,p.id FROM roles r CROSS JOIN permissions p
WHERE r.slug='administrador' AND p.slug IN ('shares.view','shares.create','shares.revoke','admin.files.delete');

-- Item 5.6: Histórico de versões
CREATE TABLE IF NOT EXISTS file_versions (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id BIGINT UNSIGNED NOT NULL,
  owner_id BIGINT UNSIGNED NOT NULL,
  version_number INT UNSIGNED NOT NULL,
  original_name VARCHAR(190) NOT NULL,
  stored_name VARCHAR(255) NOT NULL,
  extension VARCHAR(20) NULL,
  mime_type VARCHAR(150) NOT NULL,
  size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0,
  checksum CHAR(64) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_file_versions_item FOREIGN KEY(item_id) REFERENCES drive_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_file_versions_owner FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_file_version_number(item_id,version_number),
  INDEX idx_file_versions_item_created(item_id,created_at),
  INDEX idx_file_versions_owner(owner_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- OCR automático de PDFs
CREATE TABLE IF NOT EXISTS ocr_jobs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  item_id BIGINT UNSIGNED NOT NULL,
  owner_id BIGINT UNSIGNED NOT NULL,
  status ENUM('pending','processing','completed','error') NOT NULL DEFAULT 'pending',
  attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
  last_error TEXT NULL,
  started_at DATETIME NULL,
  finished_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_ocr_jobs_item FOREIGN KEY(item_id) REFERENCES drive_items(id) ON DELETE CASCADE,
  CONSTRAINT fk_ocr_jobs_owner FOREIGN KEY(owner_id) REFERENCES users(id) ON DELETE CASCADE,
  UNIQUE KEY uq_ocr_jobs_item(item_id),
  INDEX idx_ocr_jobs_status_created(status,created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;