CREATE TABLE IF NOT EXISTS teams (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  name VARCHAR(150) NOT NULL,
  description VARCHAR(500) NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_teams_law_firm (law_firm_id),
  KEY idx_teams_created_by (created_by_user_id),
  CONSTRAINT fk_teams_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_teams_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_memberships (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  membership_role VARCHAR(30) NOT NULL DEFAULT 'member',
  invited_by_user_id CHAR(36) NULL,
  joined_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_memberships_team_user (team_id, user_id),
  KEY idx_team_memberships_law_firm_user (law_firm_id, user_id),
  KEY idx_team_memberships_team_role (team_id, membership_role),
  KEY idx_team_memberships_invited_by (invited_by_user_id),
  CONSTRAINT fk_team_memberships_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_memberships_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_memberships_user
    FOREIGN KEY (user_id) REFERENCES users (id),
  CONSTRAINT fk_team_memberships_invited_by
    FOREIGN KEY (invited_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_invitations (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  invitee_email VARCHAR(255) NOT NULL,
  invitee_user_id CHAR(36) NULL,
  invited_by_user_id CHAR(36) NULL,
  status_code VARCHAR(30) NOT NULL DEFAULT 'pending',
  responded_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_invitations_team_email (team_id, invitee_email),
  KEY idx_team_invitations_law_firm_status (law_firm_id, status_code),
  KEY idx_team_invitations_invitee_status (invitee_user_id, status_code),
  KEY idx_team_invitations_invited_by (invited_by_user_id),
  CONSTRAINT fk_team_invitations_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_invitations_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_invitations_invitee_user
    FOREIGN KEY (invitee_user_id) REFERENCES users (id),
  CONSTRAINT fk_team_invitations_invited_by
    FOREIGN KEY (invited_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_agents (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  user_id CHAR(36) NOT NULL,
  agent_name VARCHAR(180) NOT NULL,
  system_prompt TEXT NULL,
  learning_summary TEXT NULL,
  memory_count INT NOT NULL DEFAULT 0,
  last_learning_at DATETIME NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uq_team_agents_team_user (team_id, user_id),
  KEY idx_team_agents_law_firm_user (law_firm_id, user_id),
  KEY idx_team_agents_team (team_id),
  CONSTRAINT fk_team_agents_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_agents_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_agents_user
    FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_agent_memories (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  team_id CHAR(36) NOT NULL,
  agent_id CHAR(36) NOT NULL,
  source_type VARCHAR(50) NOT NULL,
  source_entity_id CHAR(36) NULL,
  memory_text TEXT NOT NULL,
  created_by_user_id CHAR(36) NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_team_agent_memories_agent_created (agent_id, created_at),
  KEY idx_team_agent_memories_team_created (team_id, created_at),
  KEY idx_team_agent_memories_created_by (created_by_user_id),
  CONSTRAINT fk_team_agent_memories_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_team_agent_memories_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_team_agent_memories_agent
    FOREIGN KEY (agent_id) REFERENCES team_agents (id),
  CONSTRAINT fk_team_agent_memories_created_by
    FOREIGN KEY (created_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'team_id'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN team_id CHAR(36) NULL AFTER law_firm_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'assigned_reviewer_user_id'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN assigned_reviewer_user_id CHAR(36) NULL AFTER latest_final_validation_confidence'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'assigned_reviewer_agent_id'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN assigned_reviewer_agent_id CHAR(36) NULL AFTER assigned_reviewer_user_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'assigned_by_user_id'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN assigned_by_user_id CHAR(36) NULL AFTER assigned_reviewer_agent_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND COLUMN_NAME = 'assigned_at'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD COLUMN assigned_at DATETIME NULL AFTER assigned_by_user_id'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND INDEX_NAME = 'idx_document_review_items_team'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD KEY idx_document_review_items_team (team_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND INDEX_NAME = 'idx_document_review_items_reviewer'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD KEY idx_document_review_items_reviewer (assigned_reviewer_user_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.STATISTICS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND INDEX_NAME = 'idx_document_review_items_reviewer_agent'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD KEY idx_document_review_items_reviewer_agent (assigned_reviewer_agent_id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND CONSTRAINT_NAME = 'fk_document_review_items_team'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD CONSTRAINT fk_document_review_items_team FOREIGN KEY (team_id) REFERENCES teams (id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND CONSTRAINT_NAME = 'fk_document_review_items_assigned_reviewer'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD CONSTRAINT fk_document_review_items_assigned_reviewer FOREIGN KEY (assigned_reviewer_user_id) REFERENCES users (id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND CONSTRAINT_NAME = 'fk_document_review_items_assigned_reviewer_agent'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD CONSTRAINT fk_document_review_items_assigned_reviewer_agent FOREIGN KEY (assigned_reviewer_agent_id) REFERENCES team_agents (id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @ddl = (
  SELECT IF(
    EXISTS(
      SELECT 1
      FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
      WHERE TABLE_SCHEMA = DATABASE()
        AND TABLE_NAME = 'document_review_items'
        AND CONSTRAINT_NAME = 'fk_document_review_items_assigned_by'
    ),
    'SELECT 1',
    'ALTER TABLE document_review_items ADD CONSTRAINT fk_document_review_items_assigned_by FOREIGN KEY (assigned_by_user_id) REFERENCES users (id)'
  )
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

CREATE TABLE IF NOT EXISTS document_review_assignments (
  id CHAR(36) NOT NULL COMMENT 'Application-generated UUIDv7',
  law_firm_id CHAR(36) NOT NULL,
  item_id CHAR(36) NOT NULL,
  version_id CHAR(36) NULL,
  team_id CHAR(36) NOT NULL,
  assignment_type VARCHAR(30) NOT NULL DEFAULT 'transfer',
  from_reviewer_user_id CHAR(36) NULL,
  from_reviewer_agent_id CHAR(36) NULL,
  to_reviewer_user_id CHAR(36) NOT NULL,
  to_reviewer_agent_id CHAR(36) NULL,
  assigned_by_user_id CHAR(36) NULL,
  transfer_note TEXT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_document_review_assignments_item_created (item_id, created_at),
  KEY idx_document_review_assignments_version (version_id),
  KEY idx_document_review_assignments_to_reviewer (to_reviewer_user_id, created_at),
  CONSTRAINT fk_document_review_assignments_law_firm
    FOREIGN KEY (law_firm_id) REFERENCES law_firms (id),
  CONSTRAINT fk_document_review_assignments_item
    FOREIGN KEY (item_id) REFERENCES document_review_items (id),
  CONSTRAINT fk_document_review_assignments_version
    FOREIGN KEY (version_id) REFERENCES document_review_versions (id),
  CONSTRAINT fk_document_review_assignments_team
    FOREIGN KEY (team_id) REFERENCES teams (id),
  CONSTRAINT fk_document_review_assignments_from_reviewer
    FOREIGN KEY (from_reviewer_user_id) REFERENCES users (id),
  CONSTRAINT fk_document_review_assignments_from_agent
    FOREIGN KEY (from_reviewer_agent_id) REFERENCES team_agents (id),
  CONSTRAINT fk_document_review_assignments_to_reviewer
    FOREIGN KEY (to_reviewer_user_id) REFERENCES users (id),
  CONSTRAINT fk_document_review_assignments_to_agent
    FOREIGN KEY (to_reviewer_agent_id) REFERENCES team_agents (id),
  CONSTRAINT fk_document_review_assignments_assigned_by
    FOREIGN KEY (assigned_by_user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
