-- RESTORE ORGANIZATION TABLES
-- Extracted from sql/create_organizations.sql

-- 1. Create Organizations Table
CREATE TABLE IF NOT EXISTS organizations (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    owner_id INT NOT NULL,
    logo_url VARCHAR(500) NULL,
    
    -- Stripe Billing
    stripe_customer_id VARCHAR(100) NULL,
    stripe_subscription_id VARCHAR(100) NULL,
    
    -- Seat Licensing
    license_seats INT DEFAULT 5,
    used_seats INT DEFAULT 1,
    plan_type ENUM('team', 'business', 'enterprise') DEFAULT 'team',
    
    -- Settings
    settings JSON NULL,
    is_active TINYINT(1) DEFAULT 1,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE RESTRICT,
    INDEX idx_org_slug (slug),
    INDEX idx_org_owner (owner_id)
);

-- 2. Create Organization Members Table (The one reporting error)
CREATE TABLE IF NOT EXISTS organization_members (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    user_id INT NOT NULL,
    role ENUM('owner', 'admin', 'member') DEFAULT 'member',
    joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    invited_by INT NULL,
    
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE SET NULL,
    
    UNIQUE KEY unique_org_user (organization_id, user_id),
    INDEX idx_member_org (organization_id),
    INDEX idx_member_user (user_id)
);

-- 3. Create Organization Invites Table
CREATE TABLE IF NOT EXISTS organization_invites (
    id INT PRIMARY KEY AUTO_INCREMENT,
    organization_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    token VARCHAR(100) UNIQUE NOT NULL,
    role ENUM('admin', 'member') DEFAULT 'member',
    invited_by INT NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    accepted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (organization_id) REFERENCES organizations(id) ON DELETE CASCADE,
    FOREIGN KEY (invited_by) REFERENCES users(id) ON DELETE CASCADE,
    
    INDEX idx_invite_token (token),
    INDEX idx_invite_email (email)
);