# PMS Phase 1 — Database Schema (all tables)

একটাই জায়গায় Phase 1-এর **সব table-এর column** রাখা হয়েছে। Migration আলাদা BE card-এ হবে — কিন্তু column এই ফাইল থেকেই নিতে হবে।

| | |
| --- | --- |
| **Task** | `PMS-SCHEMA-1` |
| **Lead** | Ashraful |
| **Review** | Munna (team / assignment) · Sharif (masters + clients) |
| **আগে দরকার** | `PMS-0.1` (কোন table কার · ERD) |
| **Status** | Draft — team agree করলে “locked” |

**সহজ নিয়ম:** একটা table = একজন owner। Owner-ই সেই table-এর migration লেখে। এই ফাইলে **full CREATE TABLE DDL** আছে।

---

## Tables কীভাবে জোড়া (quick map)

```text
companies
users / employees / designations   (existing platform)
project_categories                 (existing)
clients                            (Phase 1 — define here if module missing)
    │
    ▼
projects
    │
    ├── project_types
    ├── project_categories
    └── project_service_links ──► pms_services
                                      │
                                      ▼
                                 service_teams
                                      │
                                      ▼
                                 service_team_members
                                      │
projects ──► contracts ◄── project_service_links
                │
                ├── contract_status_histories (optional)
                ├── contract_attachments
                ├── contract_commission_shares
                ├── contract_team_assignments ──► service_teams (optional)
                ├── contract_members ──► designations (+ Act As)
                └── commission_earning_records ──► commission_rules

team_divisions (BD/PD catalog)
pms_contract_role_maps (optional)
commission_types (enum preferred; optional table)
commission_rules
```

---

## কে কোন table-এর migration লিখবে

| Table | Owner | কোন card-এ migration |
| --- | --- | --- |
| `clients` | Sharif (C) | PMS-1.5-BE / Client CRUD if new |
| `project_categories` | Already exists | PMS-1.0-BE verify only |
| `project_types` | Sharif | PMS-1.1-BE |
| `pms_services` | Sharif | PMS-1.2-BE |
| `team_divisions` | Sharif | PMS-1.3-BE |
| `pms_contract_role_maps` | Sharif | PMS-1.3-BE (optional) |
| `commission_types` | Sharif | PMS-1.4a-BE (enum preferred) |
| `commission_rules` | Sharif | PMS-1.4-BE |
| `projects` | Ashraful | PMS-2.1-BE |
| `project_service_links` | Ashraful | PMS-2.2-BE |
| `contracts` | Ashraful | PMS-2.3-BE |
| `contract_status_histories` | Ashraful | PMS-2.4-BE (optional) |
| `contract_commission_shares` | Ashraful | PMS-2.5-BE |
| `contract_attachments` | Ashraful | PMS-2.6-BE |
| `commission_earning_records` | Ashraful | PMS-5.1-BE |
| `service_teams` | Munna | PMS-3.1-BE |
| `service_team_members` | Munna | PMS-3.2-BE |
| `contract_team_assignments` | Munna | PMS-3.3-BE |
| `contract_members` | Munna | PMS-3.3-BE |
| `contract_member_histories` | Munna | PMS-3.5-BE (optional) |

---

## Full DDL (সব table)

নিচের SQL-ই source of truth। `PMS-SCHEMA-1` task card-এও একই DDL আছে।

Platform reuse (নতুন table নয়): `companies`, `users`, `employees`, `designations`.

```sql
-- ============================================================
-- PMS Phase 1 — Full DDL (MySQL / MariaDB draft)
-- Task: PMS-SCHEMA-1
-- Note: existing platform tables (companies, users, employees,
--       designations) reuse only — not recreated here.
-- ============================================================

-- ------------------------------------------------------------
-- EXISTING (verify only — do not recreate) — PMS-1.0-BE
-- ------------------------------------------------------------
-- project_categories (already migrated)
/*
CREATE TABLE project_categories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(255) NOT NULL,
  parent_id BIGINT UNSIGNED NULL,
  status ENUM('active','inactive') NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY project_categories_company_parent_name_unique (company_id, parent_id, name),
  KEY project_categories_company_status_index (company_id, status),
  CONSTRAINT project_categories_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT project_categories_parent_id_foreign FOREIGN KEY (parent_id) REFERENCES project_categories (id) ON DELETE RESTRICT,
  CONSTRAINT project_categories_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT project_categories_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);
*/

-- ------------------------------------------------------------
-- 1. clients — Sharif / PMS-1.5-BE (create if Client module missing)
-- ------------------------------------------------------------
CREATE TABLE clients (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(200) NOT NULL,
  code VARCHAR(50) NULL,
  email VARCHAR(150) NULL,
  phone VARCHAR(50) NULL,
  address TEXT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active', -- active|inactive
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY clients_company_name_unique (company_id, name),
  KEY clients_company_status_index (company_id, status),
  CONSTRAINT clients_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT clients_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT clients_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 2. project_types — Sharif / PMS-1.1-BE
-- ------------------------------------------------------------
CREATE TABLE project_types (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NULL,
  description TEXT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active', -- active|inactive
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY project_types_company_name_unique (company_id, name),
  KEY project_types_company_status_index (company_id, status),
  CONSTRAINT project_types_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT project_types_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT project_types_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 3. pms_services — Sharif / PMS-1.2-BE
-- ------------------------------------------------------------
CREATE TABLE pms_services (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  code VARCHAR(50) NULL,
  description TEXT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active', -- active|inactive
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY pms_services_company_name_unique (company_id, name),
  KEY pms_services_company_status_index (company_id, status),
  CONSTRAINT pms_services_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT pms_services_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT pms_services_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 4. team_divisions — Sharif / PMS-1.3-BE
-- ------------------------------------------------------------
CREATE TABLE team_divisions (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  code VARCHAR(20) NOT NULL, -- BD|PD
  name VARCHAR(100) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY team_divisions_company_code_unique (company_id, code),
  CONSTRAINT team_divisions_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT team_divisions_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT team_divisions_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 5. pms_contract_role_maps (OPTIONAL) — Sharif / PMS-1.3-BE
-- ------------------------------------------------------------
CREATE TABLE pms_contract_role_maps (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  designation_id BIGINT UNSIGNED NOT NULL,
  division_code VARCHAR(10) NOT NULL, -- BD|PD
  label_override VARCHAR(100) NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_act_as_allowed TINYINT(1) NOT NULL DEFAULT 1,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY pms_contract_role_maps_unique (company_id, designation_id, division_code),
  CONSTRAINT pms_contract_role_maps_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT pms_contract_role_maps_designation_id_foreign FOREIGN KEY (designation_id) REFERENCES designations (id) ON DELETE RESTRICT,
  CONSTRAINT pms_contract_role_maps_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT pms_contract_role_maps_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 6. commission_types — Phase 1: NO separate table (enum on rules)
-- Values: one_time | recurring | monthly | contract_based | milestone_based
-- Optional read-only API lists these values (PMS-1.4a-BE)
-- ------------------------------------------------------------

-- ------------------------------------------------------------
-- 7. commission_rules — Sharif / PMS-1.4-BE
-- ------------------------------------------------------------
CREATE TABLE commission_rules (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  commission_type VARCHAR(40) NOT NULL, -- see §6 values
  calculation_base VARCHAR(40) NOT NULL, -- contract_value|team_pool|bd_pool|pd_pool
  percentage DECIMAL(8,4) NULL,
  fixed_amount DECIMAL(15,2) NULL,
  frequency VARCHAR(30) NULL,
  duration_value INT NULL,
  duration_type VARCHAR(30) NULL, -- months|lifetime|…
  team_division VARCHAR(10) NULL, -- BD|PD
  role_designation_id BIGINT UNSIGNED NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  effective_from DATE NULL,
  effective_to DATE NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY commission_rules_company_status_index (company_id, status),
  KEY commission_rules_company_division_index (company_id, team_division),
  CONSTRAINT commission_rules_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT commission_rules_role_designation_id_foreign FOREIGN KEY (role_designation_id) REFERENCES designations (id) ON DELETE SET NULL,
  CONSTRAINT commission_rules_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT commission_rules_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);
-- App rule: active row → exactly one of percentage / fixed_amount

-- ------------------------------------------------------------
-- 8. projects — Ashraful / PMS-2.1-BE
-- ------------------------------------------------------------
CREATE TABLE projects (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  client_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(200) NOT NULL,
  project_type_id BIGINT UNSIGNED NULL,
  project_category_id BIGINT UNSIGNED NULL,
  description TEXT NULL,
  start_date DATE NULL,
  expected_end_date DATE NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'draft', -- draft|active|on_hold|completed|cancelled
  owner_user_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY projects_company_status_index (company_id, status),
  KEY projects_company_client_index (company_id, client_id),
  KEY projects_company_type_index (company_id, project_type_id),
  KEY projects_company_category_index (company_id, project_category_id),
  CONSTRAINT projects_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT projects_client_id_foreign FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE RESTRICT,
  CONSTRAINT projects_project_type_id_foreign FOREIGN KEY (project_type_id) REFERENCES project_types (id) ON DELETE SET NULL,
  CONSTRAINT projects_project_category_id_foreign FOREIGN KEY (project_category_id) REFERENCES project_categories (id) ON DELETE SET NULL,
  CONSTRAINT projects_owner_user_id_foreign FOREIGN KEY (owner_user_id) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT projects_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT projects_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 9. project_service_links — Ashraful / PMS-2.2-BE
-- ------------------------------------------------------------
CREATE TABLE project_service_links (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  project_id BIGINT UNSIGNED NOT NULL,
  service_id BIGINT UNSIGNED NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active', -- active|inactive
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY project_service_links_project_service_unique (project_id, service_id),
  KEY project_service_links_company_project_index (company_id, project_id),
  CONSTRAINT project_service_links_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT project_service_links_project_id_foreign FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE,
  CONSTRAINT project_service_links_service_id_foreign FOREIGN KEY (service_id) REFERENCES pms_services (id) ON DELETE RESTRICT,
  CONSTRAINT project_service_links_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT project_service_links_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 10. service_teams — Munna / PMS-3.1-BE
-- ------------------------------------------------------------
CREATE TABLE service_teams (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  service_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(150) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY service_teams_company_service_name_unique (company_id, service_id, name),
  KEY service_teams_company_service_index (company_id, service_id),
  CONSTRAINT service_teams_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT service_teams_service_id_foreign FOREIGN KEY (service_id) REFERENCES pms_services (id) ON DELETE RESTRICT,
  CONSTRAINT service_teams_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT service_teams_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 11. service_team_members (permanent) — Munna / PMS-3.2-BE
-- No designation_id / is_act_as here
-- ------------------------------------------------------------
CREATE TABLE service_team_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  team_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL, -- default employees.id (lock in review)
  division VARCHAR(10) NOT NULL, -- BD|PD
  status VARCHAR(20) NOT NULL DEFAULT 'active', -- active|inactive
  joined_at DATE NULL,
  left_at DATE NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY service_team_members_company_team_index (company_id, team_id),
  KEY service_team_members_company_employee_index (company_id, employee_id),
  CONSTRAINT service_team_members_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT service_team_members_team_id_foreign FOREIGN KEY (team_id) REFERENCES service_teams (id) ON DELETE CASCADE,
  CONSTRAINT service_team_members_employee_id_foreign FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE RESTRICT,
  CONSTRAINT service_team_members_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT service_team_members_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);
-- App rule: one active row per (team_id, employee_id)

-- ------------------------------------------------------------
-- 12. contracts — Ashraful / PMS-2.3-BE
-- ------------------------------------------------------------
CREATE TABLE contracts (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_number VARCHAR(50) NOT NULL,
  project_id BIGINT UNSIGNED NOT NULL,
  project_service_link_id BIGINT UNSIGNED NOT NULL,
  client_id BIGINT UNSIGNED NOT NULL, -- must equal projects.client_id
  start_date DATE NULL,
  end_date DATE NULL,
  contract_value DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  currency VARCHAR(10) NOT NULL DEFAULT 'USD',
  allocated_hours DECIMAL(10,2) NULL,
  billing_type VARCHAR(50) NULL,
  payment_terms VARCHAR(100) NULL,
  renewal_type VARCHAR(50) NULL,
  status VARCHAR(30) NOT NULL DEFAULT 'draft',
  -- draft|pending_approval|active|expired|completed|cancelled|terminated|renewed
  notes TEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  UNIQUE KEY contracts_company_number_unique (company_id, contract_number),
  KEY contracts_company_status_index (company_id, status),
  KEY contracts_company_project_index (company_id, project_id),
  KEY contracts_project_service_link_index (project_service_link_id),
  CONSTRAINT contracts_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT contracts_project_id_foreign FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE RESTRICT,
  CONSTRAINT contracts_project_service_link_id_foreign FOREIGN KEY (project_service_link_id) REFERENCES project_service_links (id) ON DELETE RESTRICT,
  CONSTRAINT contracts_client_id_foreign FOREIGN KEY (client_id) REFERENCES clients (id) ON DELETE RESTRICT,
  CONSTRAINT contracts_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT contracts_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 13. contract_status_histories (OPTIONAL) — Ashraful / PMS-2.4-BE
-- ------------------------------------------------------------
CREATE TABLE contract_status_histories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NOT NULL,
  from_status VARCHAR(30) NULL,
  to_status VARCHAR(30) NOT NULL,
  changed_by BIGINT UNSIGNED NULL,
  note TEXT NULL,
  created_at TIMESTAMP NULL,
  KEY contract_status_histories_company_contract_index (company_id, contract_id),
  CONSTRAINT contract_status_histories_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT contract_status_histories_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES contracts (id) ON DELETE CASCADE,
  CONSTRAINT contract_status_histories_changed_by_foreign FOREIGN KEY (changed_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 14. contract_commission_shares — Ashraful / PMS-2.5-BE
-- ------------------------------------------------------------
CREATE TABLE contract_commission_shares (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NOT NULL,
  team_share_percent DECIMAL(8,4) NOT NULL, -- % of contract_value
  bd_share_percent DECIMAL(8,4) NOT NULL, -- % of team_pool
  pd_share_percent DECIMAL(8,4) NOT NULL, -- % of team_pool
  team_pool_amount DECIMAL(15,2) NOT NULL, -- snapshot
  bd_pool_amount DECIMAL(15,2) NOT NULL,
  pd_pool_amount DECIMAL(15,2) NOT NULL,
  currency VARCHAR(10) NOT NULL,
  effective_from DATE NOT NULL,
  effective_to DATE NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY contract_commission_shares_company_contract_index (company_id, contract_id),
  KEY contract_commission_shares_contract_effective_index (contract_id, effective_from),
  CONSTRAINT contract_commission_shares_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT contract_commission_shares_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES contracts (id) ON DELETE CASCADE,
  CONSTRAINT contract_commission_shares_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT contract_commission_shares_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);
-- App rule: bd_share_percent + pd_share_percent = 100

-- ------------------------------------------------------------
-- 15. contract_attachments — Ashraful / PMS-2.6-BE
-- ------------------------------------------------------------
CREATE TABLE contract_attachments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NOT NULL,
  file_path VARCHAR(255) NOT NULL,
  original_name VARCHAR(255) NOT NULL,
  mime VARCHAR(100) NULL,
  size BIGINT UNSIGNED NULL,
  uploaded_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY contract_attachments_company_contract_index (company_id, contract_id),
  CONSTRAINT contract_attachments_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT contract_attachments_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES contracts (id) ON DELETE CASCADE,
  CONSTRAINT contract_attachments_uploaded_by_foreign FOREIGN KEY (uploaded_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 16. contract_team_assignments — Munna / PMS-3.3-BE
-- ------------------------------------------------------------
CREATE TABLE contract_team_assignments (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NOT NULL,
  team_id BIGINT UNSIGNED NULL,
  division VARCHAR(10) NOT NULL, -- BD|PD
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY contract_team_assignments_company_contract_division_index (company_id, contract_id, division),
  CONSTRAINT contract_team_assignments_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT contract_team_assignments_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES contracts (id) ON DELETE CASCADE,
  CONSTRAINT contract_team_assignments_team_id_foreign FOREIGN KEY (team_id) REFERENCES service_teams (id) ON DELETE SET NULL,
  CONSTRAINT contract_team_assignments_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT contract_team_assignments_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 17. contract_members (role + Act As) — Munna / PMS-3.3-BE
-- ------------------------------------------------------------
CREATE TABLE contract_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NOT NULL,
  assignment_id BIGINT UNSIGNED NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  division VARCHAR(10) NOT NULL, -- BD|PD
  designation_id BIGINT UNSIGNED NOT NULL, -- contract role / Act As role
  is_act_as TINYINT(1) NOT NULL DEFAULT 0,
  status VARCHAR(20) NOT NULL DEFAULT 'active',
  assigned_at TIMESTAMP NULL,
  unassigned_at TIMESTAMP NULL,
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY contract_members_company_contract_index (company_id, contract_id),
  KEY contract_members_company_employee_index (company_id, employee_id),
  CONSTRAINT contract_members_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT contract_members_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES contracts (id) ON DELETE CASCADE,
  CONSTRAINT contract_members_assignment_id_foreign FOREIGN KEY (assignment_id) REFERENCES contract_team_assignments (id) ON DELETE SET NULL,
  CONSTRAINT contract_members_employee_id_foreign FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE RESTRICT,
  CONSTRAINT contract_members_designation_id_foreign FOREIGN KEY (designation_id) REFERENCES designations (id) ON DELETE RESTRICT,
  CONSTRAINT contract_members_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT contract_members_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 18. contract_member_histories (OPTIONAL) — Munna / PMS-3.5-BE
-- ------------------------------------------------------------
CREATE TABLE contract_member_histories (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_member_id BIGINT UNSIGNED NOT NULL,
  action VARCHAR(30) NOT NULL, -- assigned|unassigned|role_changed
  payload JSON NULL,
  acted_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  CONSTRAINT contract_member_histories_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT contract_member_histories_contract_member_id_foreign FOREIGN KEY (contract_member_id) REFERENCES contract_members (id) ON DELETE CASCADE,
  CONSTRAINT contract_member_histories_acted_by_foreign FOREIGN KEY (acted_by) REFERENCES users (id) ON DELETE SET NULL
);

-- ------------------------------------------------------------
-- 19. commission_earning_records (SHELL only) — Ashraful / PMS-5.1-BE
-- ------------------------------------------------------------
CREATE TABLE commission_earning_records (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  company_id BIGINT UNSIGNED NOT NULL,
  contract_id BIGINT UNSIGNED NOT NULL,
  employee_id BIGINT UNSIGNED NOT NULL,
  designation_id BIGINT UNSIGNED NULL,
  division VARCHAR(10) NULL, -- BD|PD
  rule_id BIGINT UNSIGNED NULL,
  calculation_base_type VARCHAR(40) NOT NULL,
  base_amount DECIMAL(15,2) NOT NULL,
  percentage DECIMAL(8,4) NULL,
  fixed_amount DECIMAL(15,2) NULL,
  earning_amount DECIMAL(15,2) NOT NULL,
  period_start DATE NULL,
  period_end DATE NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'draft', -- draft|finalized|cancelled
  finalized_at TIMESTAMP NULL,
  meta JSON NULL, -- frozen inputs snapshot
  created_by BIGINT UNSIGNED NULL,
  updated_by BIGINT UNSIGNED NULL,
  created_at TIMESTAMP NULL,
  updated_at TIMESTAMP NULL,
  KEY commission_earning_records_company_contract_index (company_id, contract_id),
  KEY commission_earning_records_company_employee_index (company_id, employee_id),
  KEY commission_earning_records_company_status_index (company_id, status),
  CONSTRAINT commission_earning_records_company_id_foreign FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE,
  CONSTRAINT commission_earning_records_contract_id_foreign FOREIGN KEY (contract_id) REFERENCES contracts (id) ON DELETE RESTRICT,
  CONSTRAINT commission_earning_records_employee_id_foreign FOREIGN KEY (employee_id) REFERENCES employees (id) ON DELETE RESTRICT,
  CONSTRAINT commission_earning_records_designation_id_foreign FOREIGN KEY (designation_id) REFERENCES designations (id) ON DELETE SET NULL,
  CONSTRAINT commission_earning_records_rule_id_foreign FOREIGN KEY (rule_id) REFERENCES commission_rules (id) ON DELETE SET NULL,
  CONSTRAINT commission_earning_records_created_by_foreign FOREIGN KEY (created_by) REFERENCES users (id) ON DELETE SET NULL,
  CONSTRAINT commission_earning_records_updated_by_foreign FOREIGN KEY (updated_by) REFERENCES users (id) ON DELETE SET NULL
);
```

---

## এই schema-তে নেই (Phase 2 / Commissioning)

Full commission calculation, PF maturity engine (আছে শুধু `provident_fund_role_rules`), EPS, settlement ledger, payment-এ office expense apply করা।  
`office_expense_rules` table আগে থেকেই আছে — পরে use করব।

---

## Done বলার আগে চেকলিস্ট (`PMS-SCHEMA-1`)

- [ ] Ashraful + Munna + Sharif উপরের DDL পড়ে রাজি  
- [ ] `clients`: এই DDL নাকি existing Client module — লিখে রাখা  
- [ ] `employee_id` কোথায় যাবে ঠিক (`employees` নাকি `users`)  
- [ ] Optional table রাখব নাকি বাদ: `contract_status_histories`, `pms_contract_role_maps`, `contract_member_histories`  
- [ ] Commission type = enum — সবাই একমত  
- [ ] Task card `PMS-SCHEMA-1`-এ একই DDL আছে