-- ============================================================
-- CloudVault Full Schema
-- Version 1.0 - 2026
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

CREATE TABLE IF NOT EXISTS roles (
    id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description TEXT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS departments (
    id                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name                 VARCHAR(100) NOT NULL,
    parent_department_id INT UNSIGNED NULL,
    storage_quota        BIGINT UNSIGNED NOT NULL DEFAULT 53687091200,
    manager_id           BIGINT UNSIGNED NULL,
    created_at           TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_parent (parent_department_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS users (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid               CHAR(36) NOT NULL UNIQUE,
    email              VARCHAR(255) NOT NULL UNIQUE,
    password_hash      VARCHAR(255) NOT NULL,
    first_name         VARCHAR(100) NOT NULL DEFAULT '',
    last_name          VARCHAR(100) NOT NULL DEFAULT '',
    avatar             VARCHAR(500) NULL,
    status             ENUM('active','suspended','deleted','pending') DEFAULT 'pending',
    role_id            INT UNSIGNED NOT NULL DEFAULT 5,
    department_id      INT UNSIGNED NULL,
    storage_quota      BIGINT UNSIGNED NOT NULL DEFAULT 5368709120,
    storage_used       BIGINT UNSIGNED NOT NULL DEFAULT 0,
    two_factor_secret  VARCHAR(255) NULL,
    two_factor_enabled TINYINT(1) NOT NULL DEFAULT 0,
    two_factor_backup_codes VARCHAR(2000) NULL,
    email_verified_at  TIMESTAMP NULL,
    last_login_at      TIMESTAMP NULL,
    last_login_ip      VARCHAR(45) NULL,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at         TIMESTAMP NULL,
    INDEX idx_email      (email),
    INDEX idx_status     (status),
    INDEX idx_role       (role_id),
    INDEX idx_department (department_id),
    FOREIGN KEY (role_id) REFERENCES roles(id),
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS permissions (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL UNIQUE,
    category VARCHAR(50)  NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

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

CREATE TABLE IF NOT EXISTS `groups` (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(100) NOT NULL,
    department_id INT UNSIGNED NULL,
    description   TEXT,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_department (department_id),
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS group_members (
    group_id   INT UNSIGNED NOT NULL,
    user_id    BIGINT UNSIGNED NOT NULL,
    is_admin   TINYINT(1) NOT NULL DEFAULT 0,
    joined_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (group_id, user_id),
    FOREIGN KEY (group_id) REFERENCES `groups`(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)  REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS folders (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid          CHAR(36) NOT NULL UNIQUE,
    parent_id     BIGINT UNSIGNED NULL,
    name          VARCHAR(255) NOT NULL,
    owner_id      BIGINT UNSIGNED NOT NULL,
    department_id INT UNSIGNED NULL,
    group_id      INT UNSIGNED NULL,
    is_system     TINYINT(1) NOT NULL DEFAULT 0,
    description   TEXT NULL,
    color         VARCHAR(7) NULL,
    is_starred    TINYINT(1) NOT NULL DEFAULT 0,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at    TIMESTAMP NULL,
    INDEX idx_parent     (parent_id),
    INDEX idx_owner      (owner_id),
    INDEX idx_department (department_id),
    INDEX idx_group      (group_id),
    FOREIGN KEY (parent_id)     REFERENCES folders(id) ON DELETE CASCADE,
    FOREIGN KEY (owner_id)      REFERENCES users(id),
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL,
    FOREIGN KEY (group_id)      REFERENCES `groups`(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS files (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid          CHAR(36) NOT NULL UNIQUE,
    folder_id     BIGINT UNSIGNED NULL,
    owner_id      BIGINT UNSIGNED NOT NULL,
    original_name VARCHAR(500) NOT NULL,
    storage_name  VARCHAR(255) NOT NULL,
    storage_path  VARCHAR(1000) NOT NULL,
    mime_type     VARCHAR(127) NOT NULL DEFAULT 'application/octet-stream',
    extension     VARCHAR(20) NOT NULL DEFAULT '',
    size          BIGINT UNSIGNED NOT NULL DEFAULT 0,
    sha256        CHAR(64) NOT NULL DEFAULT '',
    thumbnail_path VARCHAR(1000) NULL,
    description   TEXT NULL,
    status        ENUM('active','trash') NOT NULL DEFAULT 'active',
    version_count INT UNSIGNED NOT NULL DEFAULT 1,
    is_starred    TINYINT(1) NOT NULL DEFAULT 0,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at    TIMESTAMP NULL,
    INDEX idx_folder     (folder_id),
    INDEX idx_owner      (owner_id),
    INDEX idx_uuid       (uuid),
    INDEX idx_status     (status),
    INDEX idx_sha256     (sha256),
    INDEX idx_created    (created_at),
    FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE SET NULL,
    FOREIGN KEY (owner_id)  REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS file_tags (
    id      BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_id BIGINT UNSIGNED NOT NULL,
    tag     VARCHAR(100) NOT NULL,
    INDEX idx_file (file_id),
    INDEX idx_tag  (tag),
    FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS file_versions (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_id            BIGINT UNSIGNED NOT NULL,
    version_number     INT UNSIGNED NOT NULL,
    storage_name       VARCHAR(255) NOT NULL,
    storage_path       VARCHAR(1000) NOT NULL,
    size               BIGINT UNSIGNED NOT NULL,
    sha256             CHAR(64) NOT NULL,
    mime_type          VARCHAR(127) NOT NULL DEFAULT 'application/octet-stream',
    uploaded_by        BIGINT UNSIGNED NOT NULL,
    change_description TEXT,
    created_at         TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_file    (file_id),
    INDEX idx_version (file_id, version_number),
    FOREIGN KEY (file_id)     REFERENCES files(id) ON DELETE CASCADE,
    FOREIGN KEY (uploaded_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS folder_permissions (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    folder_id     BIGINT UNSIGNED NOT NULL,
    user_id       BIGINT UNSIGNED NULL,
    group_id      INT UNSIGNED NULL,
    department_id INT UNSIGNED NULL,
    can_view      TINYINT(1) NOT NULL DEFAULT 1,
    can_upload    TINYINT(1) NOT NULL DEFAULT 0,
    can_edit      TINYINT(1) NOT NULL DEFAULT 0,
    can_delete    TINYINT(1) NOT NULL DEFAULT 0,
    can_share     TINYINT(1) NOT NULL DEFAULT 0,
    granted_by    BIGINT UNSIGNED NULL,
    expires_at    TIMESTAMP NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_folder     (folder_id),
    INDEX idx_user       (user_id),
    INDEX idx_group      (group_id),
    INDEX idx_department (department_id),
    FOREIGN KEY (folder_id)     REFERENCES folders(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)       REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id)      REFERENCES `groups`(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by)    REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS file_permissions (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_id       BIGINT UNSIGNED NOT NULL,
    user_id       BIGINT UNSIGNED NULL,
    group_id      INT UNSIGNED NULL,
    department_id INT UNSIGNED NULL,
    can_view      TINYINT(1) NOT NULL DEFAULT 1,
    can_download  TINYINT(1) NOT NULL DEFAULT 0,
    can_edit      TINYINT(1) NOT NULL DEFAULT 0,
    can_share     TINYINT(1) NOT NULL DEFAULT 0,
    can_delete    TINYINT(1) NOT NULL DEFAULT 0,
    granted_by    BIGINT UNSIGNED NULL,
    expires_at    TIMESTAMP NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_file       (file_id),
    INDEX idx_user       (user_id),
    INDEX idx_group      (group_id),
    INDEX idx_department (department_id),
    UNIQUE KEY uk_file_user (file_id, user_id),
    FOREIGN KEY (file_id)       REFERENCES files(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)       REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (group_id)      REFERENCES `groups`(id) ON DELETE CASCADE,
    FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE,
    FOREIGN KEY (granted_by)    REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS share_links (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid           CHAR(36) NOT NULL UNIQUE,
    token          VARCHAR(128) NOT NULL UNIQUE,
    file_id        BIGINT UNSIGNED NULL,
    folder_id      BIGINT UNSIGNED NULL,
    created_by     BIGINT UNSIGNED NOT NULL,
    access_type    ENUM('public','password','users_only') DEFAULT 'public',
    password_hash  VARCHAR(255) NULL,
    can_download   TINYINT(1) NOT NULL DEFAULT 1,
    can_upload     TINYINT(1) NOT NULL DEFAULT 0,
    can_preview    TINYINT(1) NOT NULL DEFAULT 1,
    require_login  TINYINT(1) NOT NULL DEFAULT 0,
    expires_at     TIMESTAMP NULL,
    max_downloads  INT UNSIGNED NULL,
    download_count INT UNSIGNED NOT NULL DEFAULT 0,
    is_active      TINYINT(1) NOT NULL DEFAULT 1,
    last_accessed  TIMESTAMP NULL,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_token  (token),
    INDEX idx_file   (file_id),
    INDEX idx_folder (folder_id),
    FOREIGN KEY (file_id)    REFERENCES files(id) ON DELETE CASCADE,
    FOREIGN KEY (folder_id)  REFERENCES folders(id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS comments (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_id    BIGINT UNSIGNED NOT NULL,
    user_id    BIGINT UNSIGNED NOT NULL,
    parent_id  BIGINT UNSIGNED NULL,
    body       TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    INDEX idx_file   (file_id),
    INDEX idx_user   (user_id),
    INDEX idx_parent (parent_id),
    FOREIGN KEY (file_id)   REFERENCES files(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id)   REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS notifications (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NOT NULL,
    type       VARCHAR(50) NOT NULL,
    title      VARCHAR(255) NOT NULL,
    body       TEXT NOT NULL,
    data       JSON NULL,
    is_read    TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user    (user_id),
    INDEX idx_is_read (user_id, is_read),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS activity_logs (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT UNSIGNED NULL,
    action        VARCHAR(60) NOT NULL,
    resource_type VARCHAR(30) NOT NULL,
    resource_id   BIGINT UNSIGNED NULL,
    ip_address    VARCHAR(45) NULL,
    user_agent    TEXT NULL,
    details       JSON NULL,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user     (user_id),
    INDEX idx_action   (action),
    INDEX idx_resource (resource_type, resource_id),
    INDEX idx_created  (created_at),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_logs (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id    BIGINT UNSIGNED NOT NULL,
    action      VARCHAR(100) NOT NULL,
    target_type VARCHAR(50) NULL,
    target_id   BIGINT UNSIGNED NULL,
    old_values  JSON NULL,
    new_values  JSON NULL,
    ip_address  VARCHAR(45) NULL,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_admin   (admin_id),
    INDEX idx_action  (action),
    INDEX idx_created (created_at),
    FOREIGN KEY (admin_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS storage_quotas (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type     ENUM('user','group','department','system') NOT NULL,
    entity_id       BIGINT UNSIGNED NOT NULL,
    quota_bytes     BIGINT UNSIGNED NOT NULL,
    warn_threshold  TINYINT(3) UNSIGNED NOT NULL DEFAULT 80,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_entity (entity_type, entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS user_sessions (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED NOT NULL,
    session_id   VARCHAR(255) NOT NULL UNIQUE,
    ip_address   VARCHAR(45) NULL,
    user_agent   TEXT NULL,
    last_active  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user    (user_id),
    INDEX idx_session (session_id),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS login_attempts (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    ip_address  VARCHAR(45) NOT NULL,
    success     TINYINT(1) NOT NULL DEFAULT 0,
    attempted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email   (email),
    INDEX idx_ip      (ip_address),
    INDEX idx_attempt (email, ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS password_resets (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email       VARCHAR(255) NOT NULL,
    token       VARCHAR(255) NOT NULL UNIQUE,
    expires_at  TIMESTAMP NOT NULL,
    used        TINYINT(1) NOT NULL DEFAULT 0,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_email (email),
    INDEX idx_token (token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS trash (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    resource_type    ENUM('file','folder') NOT NULL,
    resource_id      BIGINT UNSIGNED NOT NULL,
    original_path    TEXT NOT NULL,
    deleted_by       BIGINT UNSIGNED NOT NULL,
    scheduled_delete TIMESTAMP NULL,
    created_at       TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_resource (resource_type, resource_id),
    FOREIGN KEY (deleted_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS settings (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    `key`       VARCHAR(100) NOT NULL UNIQUE,
    `value`     LONGTEXT NULL,
    `group`     VARCHAR(50) NOT NULL DEFAULT 'general',
    autoload    TINYINT(1) NOT NULL DEFAULT 1,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_group   (`group`),
    INDEX idx_autoload (autoload)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS api_tokens (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NOT NULL,
    name       VARCHAR(100) NOT NULL,
    token      VARCHAR(255) NOT NULL UNIQUE,
    last_used  TIMESTAMP NULL,
    expires_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user  (user_id),
    INDEX idx_token (token),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS file_shortcuts (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NOT NULL,
    file_id    BIGINT UNSIGNED NULL,
    folder_id  BIGINT UNSIGNED NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user   (user_id),
    INDEX idx_file   (file_id),
    INDEX idx_folder (folder_id),
    FOREIGN KEY (user_id)   REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (file_id)   REFERENCES files(id) ON DELETE CASCADE,
    FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS file_audio_overviews (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    file_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    status ENUM('pending','processing','ready','failed') NOT NULL DEFAULT 'pending',
    duration_seconds INT UNSIGNED NULL,
    voice VARCHAR(255) NULL,
    speed FLOAT NOT NULL DEFAULT 1.0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_file (file_id),
    INDEX idx_user (user_id),
    FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS storage_plans (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    quota_bytes     BIGINT UNSIGNED NOT NULL,
    price_monthly   DECIMAL(10,2) NULL,
    price_yearly    DECIMAL(10,2) NULL,
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS storage_requests (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         BIGINT UNSIGNED NOT NULL,
    requested_quota BIGINT UNSIGNED NOT NULL,
    reason          TEXT NULL,
    status          ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending',
    reviewed_by     BIGINT UNSIGNED NULL,
    reviewed_at     TIMESTAMP NULL,
    admin_note      TEXT NULL,
    created_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    INDEX idx_status (status),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (reviewed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS recent_activity (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NOT NULL,
    file_id    BIGINT UNSIGNED NULL,
    folder_id  BIGINT UNSIGNED NULL,
    action     VARCHAR(30) NOT NULL DEFAULT 'view',
    accessed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_user (user_id),
    UNIQUE KEY uk_user_file (user_id, file_id, action),
    FOREIGN KEY (user_id)   REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (file_id)   REFERENCES files(id) ON DELETE CASCADE,
    FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Default roles
INSERT IGNORE INTO roles (id, name, description) VALUES
(1, 'Super Administrator', 'Full system access'),
(2, 'Administrator', 'Admin all features'),
(3, 'Manager', 'Manage users and departments'),
(4, 'Power User', 'Advanced user'),
(5, 'User', 'Standard user');

-- Default permissions
INSERT IGNORE INTO permissions (name, category) VALUES
('admin.access',         'admin'),
('admin.users.manage',   'admin'),
('admin.departments.manage', 'admin'),
('admin.groups.manage',  'admin'),
('admin.settings.manage','admin'),
('admin.audit',          'admin'),
('files.upload',         'files'),
('files.download',       'files'),
('files.delete',         'files'),
('files.share',          'files'),
('folders.create',       'folders'),
('folders.manage',       'folders');

-- Role-permission mappings
INSERT IGNORE INTO role_permissions (role_id, permission_id) VALUES
(1, 1),  (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8), (1, 9), (1, 10), (1, 11), (1, 12),
(2, 1),  (2, 2), (2, 3), (2, 4), (2, 5), (2, 7), (2, 8), (2, 10), (2, 11), (2, 12),
(3, 7),  (3, 11), (3, 12),
(4, 7),  (4, 8), (4, 11),
(5, 7),  (5, 8);

-- Default storage plans
INSERT IGNORE INTO storage_plans (id, name, quota_bytes, price_monthly, price_yearly, sort_order) VALUES
(1, 'Free',           5368709120,   0.00,   0.00, 1),
(2, 'Starter',       16106127360,  4.99,  49.99, 2),
(3, 'Professional',  53687091200,  9.99,  99.99, 3),
(4, 'Business',     161061273600, 19.99, 199.99, 4),
(5, 'Enterprise',   536870912000, 49.99, 499.99, 5);

-- Default settings
INSERT IGNORE INTO settings (`key`, `value`, `group`) VALUES
('app_name',            'CloudVault',             'general'),
('app_tagline',         'Enterprise Document Cloud', 'general'),
('primary_color',       '#4a6cf7',                'branding'),
('secondary_color',     '#8b5cf6',                'branding'),
('org_name',            'CloudVault',             'general'),
('max_upload_size',     '104857600',              'storage'),
('allowed_extensions',  'pdf,doc,docx,xls,xlsx,ppt,pptx,txt,csv,rtf,odt,jpg,jpeg,png,gif,webp,mp4,webm,mp3,wav,zip', 'storage'),
('trash_auto_delete_days','30',                   'storage'),
('session_timeout',     '480',                    'security'),
('require_2fa',         '0',                      'security'),
('allow_registration',  '0',                      'auth'),
('smtp_host',           '',                       'email'),
('smtp_port',           '587',                    'email'),
('smtp_user',           '',                       'email'),
('smtp_pass',           '',                       'email'),
('smtp_from',           'noreply@cloudvault.local', 'email');

SET FOREIGN_KEY_CHECKS = 1;