CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    auth_token VARCHAR(255) NOT NULL UNIQUE,
    token_created_at DATETIME NOT NULL,
    token_expires_at DATETIME NOT NULL,
    is_board_member BOOLEAN NOT NULL DEFAULT FALSE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE questionnaire_responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    role VARCHAR(255) NOT NULL,
    role_function TEXT NOT NULL,
    tasks TEXT NOT NULL,
    devices JSON NOT NULL,
    pc_os JSON,
    tablet_os JSON,
    phone_os JSON,
    work_location ENUM(
        '100% Office',
        'Mostly Office',
        'Balanced',
        'Mostly Remote',
        '100% Remote'
    ) NOT NULL,
    email_access ENUM(
        'Outlook',
        'Webmail',
        'Mobile',
        'Outlook and Mobile',
        'Webmail and Mobile',
        'All 3 methods',
        'Outlook or Webmail only'
    ) NOT NULL,
    shared_mailboxes BOOLEAN NOT NULL,
    shared_mailbox_details TEXT,
    collaboration_tools JSON,
    email_issues JSON,
    weekly_meetings ENUM(
        '0',
        '1-2',
        '3-5',
        '6-10',
        'More than 10'
    ) NOT NULL,
    file_storage JSON NOT NULL,
    shared_files BOOLEAN NOT NULL,
    critical_files JSON,
    mfa BOOLEAN NOT NULL,
    mfa_types JSON,
    mfa_apps JSON,
    personal_device BOOLEAN NOT NULL,
    personal_device_types JSON,
    security_concerns JSON,
    it_challenges JSON,
    improvements TEXT,
    operations_improvement TEXT,
    -- Board member specific fields (nullable if user not board member)
    board_doc_access BOOLEAN,
    access_issues_details TEXT,
    board_collab_effectiveness ENUM(
        'Very Effective',
        'Effective',
        'Neutral',
        'Ineffective',
        'Very Ineffective'
    ),
    meeting_issues JSON,
    board_suggestions TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);