CREATE TABLE party_role_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(60) NOT NULL UNIQUE,
    name_ar VARCHAR(120) NOT NULL,
    name_en VARCHAR(120) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE parties (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    party_number VARCHAR(50) NOT NULL UNIQUE,
    party_kind VARCHAR(30) NOT NULL,
    name_ar VARCHAR(190) NOT NULL,
    name_en VARCHAR(190) NULL,
    short_name VARCHAR(100) NULL,
    website VARCHAR(255) NULL,
    notes TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_by BIGINT UNSIGNED NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    archived_by BIGINT UNSIGNED NULL,
    archived_at DATETIME NULL,
    KEY idx_parties_name (name_ar),
    KEY idx_parties_kind_status (party_kind, status),
    CONSTRAINT fk_parties_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
    CONSTRAINT fk_parties_archiver FOREIGN KEY (archived_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE party_roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT UNSIGNED NOT NULL,
    party_role_type_id BIGINT UNSIGNED NOT NULL,
    effective_from DATE NULL,
    effective_to DATE NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_party_role (party_id, party_role_type_id),
    CONSTRAINT fk_party_roles_party FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE,
    CONSTRAINT fk_party_roles_type FOREIGN KEY (party_role_type_id) REFERENCES party_role_types(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE party_identifiers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT UNSIGNED NOT NULL,
    identifier_type VARCHAR(60) NOT NULL,
    identifier_value VARCHAR(190) NOT NULL,
    country_code CHAR(2) NULL,
    issued_at DATE NULL,
    expires_at DATE NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_party_identifier (identifier_type, identifier_value),
    KEY idx_party_identifiers_party (party_id),
    CONSTRAINT fk_party_identifiers_party FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE party_contacts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    party_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    full_name VARCHAR(190) NOT NULL,
    job_title VARCHAR(150) NULL,
    email VARCHAR(190) NULL,
    phone VARCHAR(40) NULL,
    mobile VARCHAR(40) NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_party_contact_user (user_id),
    KEY idx_party_contacts_party (party_id, status),
    CONSTRAINT fk_party_contacts_party FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE,
    CONSTRAINT fk_party_contacts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE party_addresses (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT UNSIGNED NOT NULL,
    address_type VARCHAR(40) NOT NULL DEFAULT 'main',
    country_code CHAR(2) NOT NULL DEFAULT 'SA',
    region VARCHAR(100) NULL,
    city VARCHAR(100) NULL,
    district VARCHAR(100) NULL,
    street VARCHAR(190) NULL,
    building_number VARCHAR(30) NULL,
    postal_code VARCHAR(30) NULL,
    additional_number VARCHAR(30) NULL,
    address_text TEXT NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_party_addresses_party (party_id),
    CONSTRAINT fk_party_addresses_party FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE party_documents (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    party_id BIGINT UNSIGNED NOT NULL,
    file_id BIGINT UNSIGNED NOT NULL,
    document_type VARCHAR(80) NOT NULL,
    document_number VARCHAR(100) NULL,
    issued_at DATE NULL,
    expires_at DATE NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_party_documents_expiry (expires_at, status),
    CONSTRAINT fk_party_documents_party FOREIGN KEY (party_id) REFERENCES parties(id) ON DELETE CASCADE,
    CONSTRAINT fk_party_documents_file FOREIGN KEY (file_id) REFERENCES files(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE party_relationships (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    source_party_id BIGINT UNSIGNED NOT NULL,
    target_party_id BIGINT UNSIGNED NOT NULL,
    relationship_type VARCHAR(60) NOT NULL,
    effective_from DATE NULL,
    effective_to DATE NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_party_relationship (source_party_id, target_party_id, relationship_type),
    CONSTRAINT fk_party_rel_source FOREIGN KEY (source_party_id) REFERENCES parties(id) ON DELETE CASCADE,
    CONSTRAINT fk_party_rel_target FOREIGN KEY (target_party_id) REFERENCES parties(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE companies
    ADD COLUMN own_party_id BIGINT UNSIGNED NULL AFTER id,
    ADD CONSTRAINT fk_companies_own_party FOREIGN KEY (own_party_id) REFERENCES parties(id) ON DELETE SET NULL,
    ADD CONSTRAINT fk_companies_logo FOREIGN KEY (logo_file_id) REFERENCES files(id) ON DELETE SET NULL;

CREATE TABLE project_types (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(60) NOT NULL UNIQUE,
    name_ar VARCHAR(150) NOT NULL,
    name_en VARCHAR(150) NULL,
    description TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    sort_order INT NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE projects (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    project_number VARCHAR(60) NOT NULL UNIQUE,
    name_ar VARCHAR(190) NOT NULL,
    name_en VARCHAR(190) NULL,
    project_type_id BIGINT UNSIGNED NOT NULL,
    branch_id BIGINT UNSIGNED NOT NULL,
    project_manager_employee_id BIGINT UNSIGNED NULL,
    description TEXT NULL,
    scope_summary LONGTEXT NULL,
    original_start_date DATE NULL,
    original_finish_date DATE NULL,
    current_start_date DATE NULL,
    current_finish_date DATE NULL,
    actual_start_date DATE NULL,
    actual_finish_date DATE NULL,
    estimated_project_value DECIMAL(18,2) NULL,
    currency_code CHAR(3) NOT NULL DEFAULT 'SAR',
    current_status_code VARCHAR(80) NOT NULL DEFAULT 'draft',
    progress_method VARCHAR(50) NOT NULL DEFAULT 'not_configured',
    cached_planned_progress DECIMAL(8,4) NULL,
    cached_actual_progress DECIMAL(8,4) NULL,
    progress_calculated_at DATETIME NULL,
    workflow_instance_id BIGINT UNSIGNED NULL,
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    archived_by BIGINT UNSIGNED NULL,
    archived_at DATETIME NULL,
    archive_reason TEXT NULL,
    KEY idx_projects_status (current_status_code, archived_at),
    KEY idx_projects_branch (branch_id, archived_at),
    KEY idx_projects_manager (project_manager_employee_id, archived_at),
    KEY idx_projects_dates (current_start_date, current_finish_date),
    CONSTRAINT fk_projects_type FOREIGN KEY (project_type_id) REFERENCES project_types(id),
    CONSTRAINT fk_projects_branch FOREIGN KEY (branch_id) REFERENCES branches(id),
    CONSTRAINT fk_projects_manager FOREIGN KEY (project_manager_employee_id) REFERENCES employees(id) ON DELETE SET NULL,
    CONSTRAINT fk_projects_currency FOREIGN KEY (currency_code) REFERENCES currencies(code),
    CONSTRAINT fk_projects_workflow FOREIGN KEY (workflow_instance_id) REFERENCES workflow_instances(id) ON DELETE SET NULL,
    CONSTRAINT fk_projects_creator FOREIGN KEY (created_by) REFERENCES users(id),
    CONSTRAINT fk_projects_archiver FOREIGN KEY (archived_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE activity_logs
    ADD CONSTRAINT fk_activity_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL;

CREATE TABLE project_parties (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    party_id BIGINT UNSIGNED NOT NULL,
    party_role_type_id BIGINT UNSIGNED NOT NULL,
    contact_person_id BIGINT UNSIGNED NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    reference_number VARCHAR(100) NULL,
    effective_from DATE NULL,
    effective_to DATE NULL,
    notes TEXT NULL,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_party_role (project_id, party_id, party_role_type_id),
    KEY idx_project_parties_role (project_id, party_role_type_id, status),
    CONSTRAINT fk_project_parties_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_parties_party FOREIGN KEY (party_id) REFERENCES parties(id),
    CONSTRAINT fk_project_parties_role FOREIGN KEY (party_role_type_id) REFERENCES party_role_types(id),
    CONSTRAINT fk_project_parties_contact FOREIGN KEY (contact_person_id) REFERENCES party_contacts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_locations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_id CHAR(26) NOT NULL UNIQUE,
    project_id BIGINT UNSIGNED NOT NULL,
    location_type VARCHAR(50) NOT NULL DEFAULT 'site',
    name VARCHAR(190) NOT NULL,
    country_code CHAR(2) NOT NULL DEFAULT 'SA',
    region VARCHAR(100) NULL,
    city VARCHAR(100) NULL,
    district VARCHAR(100) NULL,
    street VARCHAR(190) NULL,
    postal_code VARCHAR(30) NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    is_primary TINYINT(1) NOT NULL DEFAULT 0,
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_project_locations_project (project_id, is_primary),
    CONSTRAINT fk_project_locations_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_team_members (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    employee_id BIGINT UNSIGNED NOT NULL,
    allocation_percentage DECIMAL(5,2) NULL,
    joined_at DATE NULL,
    left_at DATE NULL,
    is_primary_manager TINYINT(1) NOT NULL DEFAULT 0,
    can_view_financials TINYINT(1) NOT NULL DEFAULT 0,
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    notes TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_team_member (project_id, employee_id),
    KEY idx_project_team_status (project_id, status),
    CONSTRAINT fk_project_team_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_team_employee FOREIGN KEY (employee_id) REFERENCES employees(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_member_roles (
    project_team_member_id BIGINT UNSIGNED NOT NULL,
    role_id BIGINT UNSIGNED NOT NULL,
    assigned_by BIGINT UNSIGNED NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (project_team_member_id, role_id),
    CONSTRAINT fk_project_member_roles_member FOREIGN KEY (project_team_member_id) REFERENCES project_team_members(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_member_roles_role FOREIGN KEY (role_id) REFERENCES roles(id),
    CONSTRAINT fk_project_member_roles_assigner FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_member_permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_team_member_id BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    effect VARCHAR(10) NOT NULL DEFAULT 'allow',
    assigned_by BIGINT UNSIGNED NULL,
    assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_project_member_permission (project_team_member_id, permission_id),
    CONSTRAINT fk_project_member_perm_member FOREIGN KEY (project_team_member_id) REFERENCES project_team_members(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_member_perm_permission FOREIGN KEY (permission_id) REFERENCES permissions(id),
    CONSTRAINT fk_project_member_perm_assigner FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_tags (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(80) NOT NULL UNIQUE,
    color VARCHAR(20) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_tag_assignments (
    project_id BIGINT UNSIGNED NOT NULL,
    tag_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (project_id, tag_id),
    CONSTRAINT fk_project_tags_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_tags_tag FOREIGN KEY (tag_id) REFERENCES project_tags(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_notes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    note_text TEXT NOT NULL,
    security_level VARCHAR(30) NOT NULL DEFAULT 'project',
    created_by BIGINT UNSIGNED NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at DATETIME NULL,
    KEY idx_project_notes_project (project_id, created_at),
    CONSTRAINT fk_project_notes_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_notes_user FOREIGN KEY (created_by) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE project_status_history (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT UNSIGNED NOT NULL,
    from_status VARCHAR(80) NULL,
    to_status VARCHAR(80) NOT NULL,
    changed_by BIGINT UNSIGNED NULL,
    comment TEXT NULL,
    changed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_project_status_history (project_id, changed_at),
    CONSTRAINT fk_project_status_project FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    CONSTRAINT fk_project_status_user FOREIGN KEY (changed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;