-- Migration: Add users and companies tables
-- Date: 2025-01-XX
-- Description: Adds user authentication tables (companies, users, user_projects) and updates projects table

-- ===== Companies =====
CREATE TABLE IF NOT EXISTS companies (
    id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== Users =====
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci PRIMARY KEY,
    company_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    is_verified BOOLEAN DEFAULT FALSE,
    verification_token VARCHAR(255) DEFAULT NULL,
    verification_sent_at TIMESTAMP NULL DEFAULT NULL,
    password_reset_token VARCHAR(255) DEFAULT NULL,
    password_reset_expires_at TIMESTAMP NULL DEFAULT NULL,
    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,
    INDEX idx_email (email),
    INDEX idx_company (company_id),
    INDEX idx_verification_token (verification_token),
    INDEX idx_password_reset_token (password_reset_token)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ===== User Projects (Many-to-Many relationship) =====
-- Note: project_id doesn't specify collation explicitly to match existing projects.id collation
CREATE TABLE IF NOT EXISTS user_projects (
    user_id VARCHAR(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    project_id VARCHAR(36) NOT NULL,
    role VARCHAR(50) DEFAULT 'member',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, project_id),
    INDEX idx_user (user_id),
    INDEX idx_project (project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add foreign keys after table creation
-- Note: We skip modifying projects.id collation to avoid conflicts with existing foreign keys
-- The foreign keys will use the existing collation of projects.id
ALTER TABLE user_projects 
ADD CONSTRAINT fk_user_projects_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

ALTER TABLE user_projects 
ADD CONSTRAINT fk_user_projects_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE;

-- ===== Add company_id to projects table =====
-- Try to add column, ignore error if it already exists
ALTER TABLE projects 
ADD COLUMN company_id VARCHAR(36) DEFAULT NULL AFTER template_project_id;

-- Try to add index, ignore error if it already exists  
ALTER TABLE projects 
ADD INDEX idx_company (company_id);

-- Try to add foreign key, ignore error if it already exists
ALTER TABLE projects 
ADD CONSTRAINT fk_projects_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL;

