CREATE DATABASE IF NOT EXISTS drivepro CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE drivepro;

CREATE TABLE 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;

CREATE TABLE 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;

CREATE TABLE 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;

CREATE TABLE 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;

CREATE TABLE 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;

CREATE TABLE 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;

INSERT INTO roles (name,slug,description,is_system,created_at,updated_at) VALUES
('Administrador','administrador','Acesso completo ao sistema',1,NOW(),NOW()),
('Gestor','gestor','Gestão de usuários e conteúdo institucional',1,NOW(),NOW()),
('Usuário','usuario','Acesso padrão aos próprios arquivos e compartilhamentos',1,NOW(),NOW());

INSERT INTO permissions (module,name,slug,description,created_at) VALUES
('Administração','Acessar administração','admin.access','Acessar áreas administrativas',NOW()),
('Usuários','Visualizar usuários','users.view','Consultar usuários cadastrados',NOW()),
('Usuários','Criar usuários','users.create','Cadastrar novos usuários',NOW()),
('Usuários','Editar usuários','users.edit','Alterar dados e situação de usuários',NOW()),
('Usuários','Excluir usuários','users.delete','Remover logicamente usuários',NOW()),
('Perfis','Visualizar perfis','roles.view','Consultar perfis e permissões',NOW()),
('Perfis','Gerenciar perfis','roles.manage','Criar e editar perfis de acesso',NOW()),
('Arquivos','Visualizar arquivos','files.view','Acessar arquivos permitidos',NOW()),
('Arquivos','Enviar arquivos','files.upload','Enviar novos arquivos',NOW()),
('Arquivos','Excluir arquivos','files.delete','Mover arquivos para a lixeira',NOW()),
('Arquivos','Excluir arquivos de usuários','admin.files.delete','Excluir definitivamente arquivos e pastas pertencentes a qualquer usuário',NOW()),
('Compartilhamentos','Criar compartilhamentos','shares.create','Compartilhar arquivos e pastas',NOW()),
('Auditoria','Visualizar auditoria','audit.view','Consultar trilha de auditoria',NOW());

INSERT INTO role_permissions(role_id,permission_id)
SELECT 1,id FROM permissions;

INSERT INTO role_permissions(role_id,permission_id)
SELECT 2,id FROM permissions WHERE slug IN ('admin.access','users.view','users.create','users.edit','roles.view','files.view','files.upload','files.delete','shares.create','audit.view');

INSERT INTO role_permissions(role_id,permission_id)
SELECT 3,id FROM permissions WHERE slug IN ('files.view','files.upload','files.delete','shares.create');
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 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');

-- 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;