SET NAMES utf8mb4;

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