-- Migration: Workspace Roles and Members
-- Creates tables for managing workspace members, roles, and permissions

-- =====================================================
-- 1. WORKSPACE ROLES TABLE
-- Defines roles available within each workspace
-- =====================================================
CREATE TABLE IF NOT EXISTS workspace_roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    name VARCHAR(50) NOT NULL,
    description VARCHAR(255) DEFAULT NULL,
    is_default TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    UNIQUE KEY unique_role_per_company (company_id, name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 2. ROLE PERMISSIONS TABLE
-- Defines what each role can access
-- =====================================================
CREATE TABLE IF NOT EXISTS role_permissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    role_id INT NOT NULL,
    permission_key VARCHAR(50) NOT NULL,
    allowed TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (role_id) REFERENCES workspace_roles(id) ON DELETE CASCADE,
    UNIQUE KEY unique_permission_per_role (role_id, permission_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 3. WORKSPACE MEMBERS TABLE
-- Links users to workspaces with their assigned role
-- =====================================================
CREATE TABLE IF NOT EXISTS workspace_members (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    user_id INT NOT NULL,
    role_id INT DEFAULT NULL,
    is_owner TINYINT(1) DEFAULT 0,
    invited_by INT DEFAULT NULL,
    invited_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    accepted_at TIMESTAMP NULL DEFAULT NULL,
    status ENUM('pending', 'active', 'suspended', 'removed') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES workspace_roles(id) ON DELETE SET NULL,
    FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE SET NULL,
    UNIQUE KEY unique_member_per_workspace (company_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 4. WORKSPACE INVITATIONS TABLE
-- Tracks pending invitations (for users not yet registered)
-- =====================================================
CREATE TABLE IF NOT EXISTS workspace_invitations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    company_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    role_id INT DEFAULT NULL,
    invited_by INT NOT NULL,
    token VARCHAR(64) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    accepted_at TIMESTAMP NULL DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE,
    FOREIGN KEY (role_id) REFERENCES workspace_roles(id) ON DELETE SET NULL,
    FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE CASCADE,
    UNIQUE KEY unique_invitation (company_id, email, token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================
-- 5. ADD owner_id TO COMPANIES TABLE
-- Track who owns each workspace
-- =====================================================
ALTER TABLE companies ADD COLUMN IF NOT EXISTS owner_id INT DEFAULT NULL AFTER id;
ALTER TABLE companies ADD CONSTRAINT fk_company_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL;

-- =====================================================
-- 6. DEFAULT PERMISSION KEYS REFERENCE
-- These are the permission keys used in the system:
-- =====================================================
-- dashboard_view        - Can view dashboard stats
-- clients_view          - Can view clients list
-- clients_create        - Can create new clients
-- clients_edit          - Can edit existing clients
-- clients_delete        - Can delete clients
-- proposals_view        - Can view proposals
-- proposals_create      - Can create proposals
-- proposals_edit        - Can edit proposals
-- proposals_delete      - Can delete proposals
-- contracts_view        - Can view contracts
-- contracts_create      - Can create contracts
-- contracts_edit        - Can edit contracts
-- contracts_delete      - Can delete contracts
-- invoices_view         - Can view invoices
-- invoices_create       - Can create invoices
-- invoices_edit         - Can edit invoices
-- invoices_delete       - Can delete invoices
-- templates_view        - Can view templates
-- templates_manage      - Can create/edit/delete templates
-- members_view          - Can view workspace members
-- members_manage        - Can invite/remove members (owner only by default)
-- roles_manage          - Can create/edit roles (owner only by default)
-- billing_view          - Can view billing (owner only)
-- settings_manage       - Can manage workspace settings (owner only)
