--
-- Host: 127.0.0.1    Database: directormaster
-- ------------------------------------------------------


--
-- Table structure for table `audit_log`
--

CREATE TABLE `audit_log` (
  `audit_id` int NOT NULL AUTO_INCREMENT,
  `event_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `user_id` int DEFAULT NULL,
  `object_type` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `object_id` int DEFAULT NULL,
  `action` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `before_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `after_json` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin,
  `ip_address` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`audit_id`),
  CONSTRAINT `audit_log_chk_1` CHECK (json_valid(`before_json`)),
  CONSTRAINT `audit_log_chk_2` CHECK (json_valid(`after_json`))
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_log`
--

INSERT INTO `audit_log` VALUES (1,'2025-12-14 17:35:06',1,'Entity',1,'CREATE',NULL,NULL,NULL),(2,'2025-12-14 18:15:44',1,'Entity',1,'UPDATE',NULL,NULL,NULL),(3,'2025-12-14 18:15:53',1,'Entity',1,'UPDATE',NULL,NULL,NULL),(4,'2025-12-14 18:16:06',1,'Entity',1,'UPDATE',NULL,NULL,NULL),(5,'2025-12-14 18:16:38',1,'Entity',1,'UPDATE',NULL,NULL,NULL),(6,'2025-12-15 08:47:09',1,'Mandate',1,'CREATE',NULL,NULL,NULL),(7,'2025-12-15 08:47:27',1,'Mandate',2,'CREATE',NULL,NULL,NULL);

--
-- Table structure for table `compliance_requirements`
--

CREATE TABLE `compliance_requirements` (
  `req_id` int NOT NULL AUTO_INCREMENT,
  `stakeholder_type` enum('Individual','Company','Fund') COLLATE utf8mb4_unicode_ci NOT NULL,
  `document_type_code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_mandatory` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`req_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `compliance_requirements`
--

INSERT INTO `compliance_requirements` VALUES (1,'Individual','KYC_ID',1),(2,'Individual','KYC_ADDR',1),(3,'Company','KYC_ARTICLES',1),(4,'Company','KYC_RBE',1),(5,'Company','KYC_UBO',1);

--
-- Table structure for table `contracts`
--

CREATE TABLE `contracts` (
  `contract_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int NOT NULL,
  `counterparty_stakeholder_id` int NOT NULL,
  `contract_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `notice_period_days` int DEFAULT NULL,
  `fee_model` enum('Fixed','Variable','NAV_Based','Hourly') COLLATE utf8mb4_unicode_ci DEFAULT 'Fixed',
  `invoicing_frequency` enum('Monthly','Quarterly','Annual') COLLATE utf8mb4_unicode_ci DEFAULT 'Annual',
  `currency` varchar(3) COLLATE utf8mb4_unicode_ci DEFAULT 'EUR',
  `amount` decimal(15,2) DEFAULT NULL,
  `status` enum('Draft','Active','Terminated','Expired') COLLATE utf8mb4_unicode_ci DEFAULT 'Draft',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`contract_id`),
  KEY `entity_id` (`entity_id`),
  KEY `counterparty_stakeholder_id` (`counterparty_stakeholder_id`),
  CONSTRAINT `contracts_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE,
  CONSTRAINT `contracts_ibfk_2` FOREIGN KEY (`counterparty_stakeholder_id`) REFERENCES `stakeholders` (`stakeholder_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `contracts`
--

INSERT INTO `contracts` VALUES (1,1,4,'Directorship agreement','2021-12-12',NULL,NULL,'Fixed','Annual','EUR',NULL,'Active','2025-12-15 08:48:31'),(2,1,5,'Engagement letter','2024-08-26',NULL,NULL,'Hourly','Annual','EUR',NULL,'Active','2025-12-15 10:59:54');

--
-- Table structure for table `corporate_actions`
--

CREATE TABLE `corporate_actions` (
  `action_id` int NOT NULL AUTO_INCREMENT,
  `security_id` int NOT NULL,
  `type` enum('Dividend','Interest','Split','Merger','SpinOff') NOT NULL,
  `ex_date` date DEFAULT NULL,
  `record_date` date DEFAULT NULL,
  `pay_date` date NOT NULL,
  `amount_per_share` decimal(20,6) DEFAULT '0.000000',
  `ratio_from` decimal(20,6) DEFAULT '1.000000',
  `ratio_to` decimal(20,6) DEFAULT '1.000000',
  `currency` char(3) DEFAULT 'EUR',
  `description` text,
  `status` enum('Pending','Processed','Cancelled') DEFAULT 'Pending',
  `processed_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`action_id`),
  KEY `security_id` (`security_id`),
  CONSTRAINT `corporate_actions_ibfk_1` FOREIGN KEY (`security_id`) REFERENCES `securities` (`security_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `corporate_actions`
--


--
-- Table structure for table `decisions`
--

CREATE TABLE `decisions` (
  `decision_id` int NOT NULL AUTO_INCREMENT,
  `meeting_id` int NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `text_html` text COLLATE utf8mb4_unicode_ci,
  `status` enum('Draft','Approved','Rejected') COLLATE utf8mb4_unicode_ci DEFAULT 'Draft',
  `approved_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`decision_id`),
  KEY `meeting_id` (`meeting_id`),
  CONSTRAINT `decisions_ibfk_1` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`meeting_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `decisions`
--


--
-- Table structure for table `document_types`
--

CREATE TABLE `document_types` (
  `document_type_id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`document_type_id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `document_types`
--

INSERT INTO `document_types` VALUES (1,'ARTICLES','Articles of Incorporation'),(2,'AGREEMENT','Agreement / Contract'),(3,'MINUTES','Meeting Minutes'),(4,'RESOLUTION','Circular Resolution'),(5,'KYC','KYC Document'),(6,'INVOICE','Invoice'),(7,'ANNUAL_ACCOUNTS','Annual Accounts'),(8,'OTHER','Other');

--
-- Table structure for table `documents`
--

CREATE TABLE `documents` (
  `document_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int DEFAULT NULL,
  `related_object_type` enum('Entity','Stakeholder','Meeting','Contract','Compliance','Task','LegalCase','LegalAct','General','Security','Transaction','CorporateAction') COLLATE utf8mb4_unicode_ci DEFAULT 'General',
  `related_object_id` int DEFAULT NULL,
  `related_agenda_item_id` int DEFAULT NULL,
  `document_type_id` int DEFAULT NULL,
  `custom_type_label` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `document_date` date DEFAULT NULL,
  `expiry_date` date DEFAULT NULL,
  `version` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '1.0',
  `confidentiality` enum('Public','Internal','Restricted') COLLATE utf8mb4_unicode_ci DEFAULT 'Internal',
  `file_path` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `is_latest_approved` tinyint(1) DEFAULT '0',
  `uploaded_by_user_id` int DEFAULT NULL,
  `uploaded_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`document_id`),
  KEY `entity_id` (`entity_id`),
  KEY `document_type_id` (`document_type_id`),
  KEY `uploaded_by_user_id` (`uploaded_by_user_id`),
  KEY `fk_docs_agenda_item` (`related_agenda_item_id`),
  CONSTRAINT `documents_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE SET NULL,
  CONSTRAINT `documents_ibfk_2` FOREIGN KEY (`document_type_id`) REFERENCES `document_types` (`document_type_id`),
  CONSTRAINT `documents_ibfk_3` FOREIGN KEY (`uploaded_by_user_id`) REFERENCES `users` (`user_id`),
  CONSTRAINT `fk_docs_agenda_item` FOREIGN KEY (`related_agenda_item_id`) REFERENCES `meeting_agenda_items` (`agenda_item_id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `documents`
--

INSERT INTO `documents` VALUES (1,1,'Contract',1,NULL,2,NULL,'Contract: Directorship agreement','2021-12-12',NULL,'1.0','Internal','uploads/contracts/JC-CAPITAL-S-A-CONTRACT-directorship-agreement-20251215.pdf',0,1,'2025-12-15 08:48:31'),(3,NULL,'Security',1,NULL,NULL,NULL,'Indicative Financing Head of Terms',NULL,NULL,'1.0','Internal','uploads/investments/TEDA-1-SECURITY-indicative-financing-head-of-terms-20251215-1765789827.pdf',0,1,'2025-12-15 09:10:27'),(4,NULL,'Security',2,NULL,NULL,NULL,'Indicative Financing Head of Terms',NULL,NULL,'1.0','Internal','uploads/investments/TEDA-SB-CAPITAL-1-SECURITY-indicative-financing-head-of-terms-20251215.pdf',0,1,'2025-12-15 09:14:32'),(5,NULL,'Security',4,NULL,NULL,NULL,'Indicative Financing Head of Terms',NULL,NULL,'1.0','Internal','uploads/investments/TEDA-7-PHARMACIES-SECURITY-indicative-financing-head-of-terms-20251215.pdf',0,1,'2025-12-15 09:20:02'),(6,NULL,'Security',6,NULL,NULL,NULL,'Indicative Financing Head of Terms',NULL,NULL,'1.0','Internal','uploads/investments/TEDA-MERCHANT-SECURITY-indicative-financing-head-of-terms-20251215.pdf',0,1,'2025-12-15 09:29:30'),(7,NULL,'Security',5,NULL,NULL,NULL,'Indicative Financing Head of Terms',NULL,NULL,'1.0','Internal','uploads/investments/TEDA-DISAR-SECURITY-indicative-financing-head-of-terms-20251215.pdf',0,1,'2025-12-15 09:29:47'),(8,NULL,'Security',10,NULL,NULL,NULL,'Indicative Financing Head of Terms',NULL,NULL,'1.0','Internal','uploads/investments/TEST-SECURITY-indicative-financing-head-of-terms-20251215.pdf',0,1,'2025-12-15 10:03:12'),(9,1,'Contract',2,NULL,2,NULL,'Contract: Engagement letter','2024-08-26',NULL,'1.0','Internal','uploads/contracts/JC-CAPITAL-S-A-CONTRACT-engagement-letter-20251215.pdf',0,1,'2025-12-15 10:59:54');

--
-- Table structure for table `entities`
--

CREATE TABLE `entities` (
  `entity_id` int NOT NULL AUTO_INCREMENT,
  `entity_type_id` int DEFAULT NULL,
  `legal_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `short_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `incorporation_date` date DEFAULT NULL,
  `tax_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `share_capital` decimal(15,2) DEFAULT NULL,
  `currency` char(3) COLLATE utf8mb4_unicode_ci DEFAULT 'EUR',
  `financial_year_end` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT '31/12',
  `duration` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'Unlimited',
  `nace_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lei` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `vat_number` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `jurisdiction_country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registered_address` text COLLATE utf8mb4_unicode_ci,
  `corporate_purpose` text COLLATE utf8mb4_unicode_ci,
  `status` enum('Active','Liquidation','Closed') COLLATE utf8mb4_unicode_ci DEFAULT 'Active',
  `parent_entity_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_id`),
  KEY `entity_type_id` (`entity_type_id`),
  KEY `parent_entity_id` (`parent_entity_id`),
  CONSTRAINT `entities_ibfk_1` FOREIGN KEY (`entity_type_id`) REFERENCES `entity_types` (`entity_type_id`),
  CONSTRAINT `entities_ibfk_2` FOREIGN KEY (`parent_entity_id`) REFERENCES `entities` (`entity_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `entities`
--

INSERT INTO `entities` VALUES (1,3,'JC CAPITAL S.A.',NULL,'B196457','2015-04-17','',31000.00,'EUR','31/12','Unlimited','64.202',NULL,NULL,'Luxembourg','','La Société a pour objet l\'accomplissement de toutes les opérations se rapportant directement ou indirectement à la prise de participations dans des sociétés luxembourgeoises ou étrangères, sous quelque forme que ce soit, ainsi que l\'administration, la gestion, 1le contrôle et le développement de ces participation2s.345678La Société peut utiliser ses fonds pour constituer, administrer, développer et vendre ses portefeuille9s d\'actifs tels qu\'ils seront constitués au f10il du temps, acquérir, investir dans e11t vendre toute sorte de propriétés, corpore12lles ou incorporelles, mobilières 13ou immobilières, 14notamment, mais non limité à des portefeuilles de valeurs mobilières de toute origine, pour participer dans la création, l\'acquisition, le développement et le contrôle de toute entre15prise, pour acquérir, par voie d\'investissement, de souscription ou d\'option de valeurs mobilières pour en disposer par voie de vente, transfert, échange ou autrement et pour les développer.16La Société peut emprunter, sous quelque forme que ce soit, sauf par voie d\'offre publique. Elle peut procéder17, uniquement par voie de placement privé, à l\'émission de titres, obligations, bons de caisse et tous titres de dettes sous forme nominative et soumise à des restrictions de transfert. La Société peut accorder tous crédits, y compris le18s intérêts de prêts et/ou émissions de valeurs mobilières, à ses filiales ou ... $(*)$','Active',NULL,'2025-12-14 17:35:06','2025-12-14 18:16:38');

--
-- Table structure for table `entity_stakeholder_roles`
--

CREATE TABLE `entity_stakeholder_roles` (
  `entity_stakeholder_role_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int NOT NULL,
  `stakeholder_id` int NOT NULL,
  `role_id` int NOT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `appointing_body` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `signing_authority` enum('None','Single','Joint','Limited') COLLATE utf8mb4_unicode_ci DEFAULT 'None',
  `decision_powers` enum('Advisory','Decision','Execution') COLLATE utf8mb4_unicode_ci DEFAULT 'Advisory',
  `criticality` enum('Low','Medium','High') COLLATE utf8mb4_unicode_ci DEFAULT 'Low',
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`entity_stakeholder_role_id`),
  KEY `entity_id` (`entity_id`),
  KEY `stakeholder_id` (`stakeholder_id`),
  KEY `role_id` (`role_id`),
  CONSTRAINT `entity_stakeholder_roles_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE,
  CONSTRAINT `entity_stakeholder_roles_ibfk_2` FOREIGN KEY (`stakeholder_id`) REFERENCES `stakeholders` (`stakeholder_id`) ON DELETE CASCADE,
  CONSTRAINT `entity_stakeholder_roles_ibfk_3` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `entity_stakeholder_roles`
--

INSERT INTO `entity_stakeholder_roles` VALUES (1,1,1,1,'2021-10-09',NULL,NULL,'Joint','Advisory','Low',NULL,'2025-12-15 08:47:09'),(2,1,2,1,'2021-10-09',NULL,NULL,'Joint','Advisory','Low',NULL,'2025-12-15 08:47:27');

--
-- Table structure for table `entity_types`
--

CREATE TABLE `entity_types` (
  `entity_type_id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`entity_type_id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `entity_types`
--

INSERT INTO `entity_types` VALUES (1,'FUND','Investment Fund'),(2,'MANCO','Management Company'),(3,'SPV','Special Purpose Vehicle'),(4,'HOLDING','Holding Company'),(5,'GP','General Partner');

--
-- Table structure for table `invoice_categories`
--

CREATE TABLE `invoice_categories` (
  `category_id` int NOT NULL AUTO_INCREMENT,
  `label` varchar(100) NOT NULL,
  PRIMARY KEY (`category_id`),
  UNIQUE KEY `label` (`label`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `invoice_categories`
--

INSERT INTO `invoice_categories` VALUES (3,'Accounting Fees'),(2,'Audit Fees'),(9,'Director Fees'),(6,'Domiciliation'),(7,'IT Services'),(1,'Legal Fees'),(8,'Office Supplies'),(10,'Other'),(5,'Rent'),(4,'Tax Compliance');

--
-- Table structure for table `legal_acts`
--

CREATE TABLE `legal_acts` (
  `act_id` int NOT NULL AUTO_INCREMENT,
  `case_id` int NOT NULL,
  `act_date` date NOT NULL,
  `act_type` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `summary` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `document_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`act_id`),
  KEY `case_id` (`case_id`),
  KEY `document_id` (`document_id`),
  CONSTRAINT `legal_acts_ibfk_1` FOREIGN KEY (`case_id`) REFERENCES `legal_cases` (`case_id`),
  CONSTRAINT `legal_acts_ibfk_2` FOREIGN KEY (`document_id`) REFERENCES `documents` (`document_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `legal_acts`
--


--
-- Table structure for table `legal_cases`
--

CREATE TABLE `legal_cases` (
  `case_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int NOT NULL,
  `case_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `opponent_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `proceeding_status` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `court_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `current_lawyer_id` int DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `status` enum('Active','On Hold','Closed','Archived') COLLATE utf8mb4_unicode_ci DEFAULT 'Active',
  `status_date` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`case_id`),
  KEY `entity_id` (`entity_id`),
  KEY `current_lawyer_id` (`current_lawyer_id`),
  CONSTRAINT `legal_cases_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`),
  CONSTRAINT `legal_cases_ibfk_2` FOREIGN KEY (`current_lawyer_id`) REFERENCES `stakeholders` (`stakeholder_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `legal_cases`
--

INSERT INTO `legal_cases` VALUES (1,1,'R.G. no. 8963/2021 ','TEDA Merchant',NULL,'Court of Salerno',5,'On July 8, 2021, JC requested the Court of Salerno to issue a payment injunction against Teda for an amount equal to Euro 51,834,145.70, given by JC to Teda pursuant an Indicative Financing Heads of Terms (“FHoT”). On July 19, 2021, the Court of Salerno issued the relevant injunction.\r\nOn November 16, 2021, the payment injunction was challenged by Teda, which argued the FHoT actually dissimulated a de facto company or a “dominance agreement” pursuant to art. 2497-quinquies ICC: so, the payment injunction should be revoked (the “Teda’s Counter-claims”). JC insisted on his reimbursement claim (the “JC’s Claim”).\r\nThe Judge of the Salerno Proceedings, on October 4, 2022, ordered the separation of the proceedings and referred the Teda’s Counter-claims to the Special Business Division of the Court of Naples (“Salerno’s Decision about Jurisdiction”).\r\n','Active','2025-12-15','2025-12-15 10:50:14');

--
-- Table structure for table `meeting_agenda_items`
--

CREATE TABLE `meeting_agenda_items` (
  `agenda_item_id` int NOT NULL AUTO_INCREMENT,
  `meeting_id` int NOT NULL,
  `item_no` int NOT NULL,
  `order_index` int DEFAULT '0',
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `minutes_text` text COLLATE utf8mb4_unicode_ci,
  `decision_required` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`agenda_item_id`),
  KEY `meeting_id` (`meeting_id`),
  CONSTRAINT `meeting_agenda_items_ibfk_1` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`meeting_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `meeting_agenda_items`
--


--
-- Table structure for table `meeting_attendees`
--

CREATE TABLE `meeting_attendees` (
  `meeting_attendee_id` int NOT NULL AUTO_INCREMENT,
  `meeting_id` int NOT NULL,
  `stakeholder_id` int NOT NULL,
  `attendance_status` enum('Invited','Confirmed','Attended','Video','Absent','Excused','Apologies') COLLATE utf8mb4_unicode_ci DEFAULT 'Invited',
  `guest_role` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `guest_organization` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`meeting_attendee_id`),
  KEY `meeting_id` (`meeting_id`),
  KEY `stakeholder_id` (`stakeholder_id`),
  CONSTRAINT `meeting_attendees_ibfk_1` FOREIGN KEY (`meeting_id`) REFERENCES `meetings` (`meeting_id`) ON DELETE CASCADE,
  CONSTRAINT `meeting_attendees_ibfk_2` FOREIGN KEY (`stakeholder_id`) REFERENCES `stakeholders` (`stakeholder_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `meeting_attendees`
--


--
-- Table structure for table `meetings`
--

CREATE TABLE `meetings` (
  `meeting_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int NOT NULL,
  `meeting_date_time` datetime NOT NULL,
  `location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('Draft','Scheduled','Held','Approved') COLLATE utf8mb4_unicode_ci DEFAULT 'Draft',
  `chair_stakeholder_id` int DEFAULT NULL,
  `secretary_stakeholder_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`meeting_id`),
  KEY `entity_id` (`entity_id`),
  CONSTRAINT `meetings_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `meetings`
--


--
-- Table structure for table `risk_assessment_results`
--

CREATE TABLE `risk_assessment_results` (
  `result_id` int NOT NULL AUTO_INCREMENT,
  `assessment_id` int NOT NULL,
  `factor_id` int NOT NULL,
  `option_id` int NOT NULL,
  `question_snapshot` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `option_label_snapshot` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `generated_score` decimal(5,2) NOT NULL,
  PRIMARY KEY (`result_id`),
  KEY `assessment_id` (`assessment_id`),
  CONSTRAINT `risk_assessment_results_ibfk_1` FOREIGN KEY (`assessment_id`) REFERENCES `risk_assessments` (`assessment_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `risk_assessment_results`
--


--
-- Table structure for table `risk_assessments`
--

CREATE TABLE `risk_assessments` (
  `assessment_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int DEFAULT NULL,
  `stakeholder_id` int DEFAULT NULL,
  `model_id` int NOT NULL,
  `total_score` decimal(5,2) DEFAULT '0.00',
  `risk_level` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT 'Low',
  `assessed_at` datetime DEFAULT CURRENT_TIMESTAMP,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `status` enum('Active','Archived') COLLATE utf8mb4_unicode_ci DEFAULT 'Active',
  `previous_assessment_id` int DEFAULT NULL,
  PRIMARY KEY (`assessment_id`),
  KEY `entity_id` (`entity_id`),
  KEY `stakeholder_id` (`stakeholder_id`),
  KEY `model_id` (`model_id`),
  KEY `fk_prev_assessment` (`previous_assessment_id`),
  CONSTRAINT `fk_prev_assessment` FOREIGN KEY (`previous_assessment_id`) REFERENCES `risk_assessments` (`assessment_id`) ON DELETE SET NULL,
  CONSTRAINT `risk_assessments_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE,
  CONSTRAINT `risk_assessments_ibfk_2` FOREIGN KEY (`stakeholder_id`) REFERENCES `stakeholders` (`stakeholder_id`) ON DELETE CASCADE,
  CONSTRAINT `risk_assessments_ibfk_3` FOREIGN KEY (`model_id`) REFERENCES `risk_models` (`model_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `risk_assessments`
--


--
-- Table structure for table `risk_factor_options`
--

CREATE TABLE `risk_factor_options` (
  `option_id` int NOT NULL AUTO_INCREMENT,
  `factor_id` int NOT NULL,
  `label` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `score` decimal(5,2) DEFAULT '0.00',
  PRIMARY KEY (`option_id`),
  KEY `factor_id` (`factor_id`),
  CONSTRAINT `risk_factor_options_ibfk_1` FOREIGN KEY (`factor_id`) REFERENCES `risk_factors` (`factor_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `risk_factor_options`
--

INSERT INTO `risk_factor_options` VALUES (4,13,'EU / FATF low-risk country',0.00),(5,13,'FATF monitored (Grey List)',0.50),(6,13,'FATF high-risk (Black List)',1.00),(7,15,'Luxembourg / EU',0.00),(8,15,'Third country (Non-FATF monitored)',0.50),(9,15,'High-risk country (FATF HRC / Sanctions)',1.00),(10,16,'Employee / Standard regulated profession',0.00),(11,16,'Self-employed / Real estate / Non-regulated liberal profession',0.50),(12,16,'Cash-intensive / Cryptocurrencies / Lobbying / Inactive with high income',1.00),(13,17,'25–70 years (Stable profile)',0.00),(14,17,'18–24 years (Limited financial history)',0.50),(15,17,'<18 or >80 years (Legal risk or SoW inconsistency)',1.00),(16,18,'Physical meeting / Robust certified ID',0.00),(17,18,'Hybrid identification (Certified video / Third-party)',0.50),(18,18,'Fully remote (Absence of physical contact)',1.00),(19,19,'Salary / Pension / Clear and documented asset sales',0.00),(20,19,'Business income / Investments / Foreign inheritance (Documented)',0.50),(21,19,'Unknown / Opaque source / Disproportionate to profile',1.00);

--
-- Table structure for table `risk_factors`
--

CREATE TABLE `risk_factors` (
  `factor_id` int NOT NULL AUTO_INCREMENT,
  `model_id` int DEFAULT NULL,
  `category` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'General',
  `question` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `weight` int NOT NULL DEFAULT '1',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`factor_id`),
  KEY `model_id` (`model_id`),
  CONSTRAINT `risk_factors_ibfk_1` FOREIGN KEY (`model_id`) REFERENCES `risk_models` (`model_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `risk_factors`
--

INSERT INTO `risk_factors` VALUES (13,4,'A. Identity & Profile Risk','Nationality',4,'2025-12-13 19:26:43'),(15,4,'A. Identity & Profile Risk','Country of Residence',4,'2025-12-14 10:17:19'),(16,4,'A. Identity & Profile Risk','Occupation',3,'2025-12-14 10:18:43'),(17,4,'A. Identity & Profile Risk','Age Profile',1,'2025-12-14 10:20:33'),(18,4,'A. Identity & Profile Risk','Relationship (Non-Face-to-Face)',3,'2025-12-14 10:21:36'),(19,4,'B. Financial Risk','Source of Wealth (SoW)',5,'2025-12-14 10:23:36');

--
-- Table structure for table `risk_models`
--

CREATE TABLE `risk_models` (
  `model_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `score_low` decimal(5,2) DEFAULT '0.00',
  `score_medium` decimal(5,2) DEFAULT '0.50',
  `score_high` decimal(5,2) DEFAULT '1.00',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`model_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `risk_models`
--

INSERT INTO `risk_models` VALUES (4,'NATURAL PERSON','',0.00,0.50,1.00,'2025-12-13 19:12:15');

--
-- Table structure for table `risk_scoring_ranges`
--

CREATE TABLE `risk_scoring_ranges` (
  `range_id` int NOT NULL AUTO_INCREMENT,
  `model_id` int DEFAULT NULL,
  `label` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `min_score` decimal(5,2) NOT NULL,
  `max_score` decimal(5,2) NOT NULL,
  `color` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT 'gray',
  PRIMARY KEY (`range_id`),
  KEY `model_id` (`model_id`),
  CONSTRAINT `risk_scoring_ranges_ibfk_1` FOREIGN KEY (`model_id`) REFERENCES `risk_models` (`model_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `risk_scoring_ranges`
--

INSERT INTO `risk_scoring_ranges` VALUES (10,4,'Low',0.00,10.00,'green'),(11,4,'Medium',11.00,30.00,'orange'),(12,4,'High',31.00,999.00,'red'),(13,NULL,'Low',0.00,20.00,'green'),(14,NULL,'Medium',21.00,50.00,'orange'),(15,NULL,'High',51.00,999.99,'red');

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `role_id` int NOT NULL AUTO_INCREMENT,
  `code` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `label` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `category` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` VALUES (1,'DIRECTOR','Director','Governance'),(2,'MANAGER','Manager','Governance'),(3,'SHAREHOLDER','Shareholder','Ownership'),(4,'AUDITOR','Auditor','Provider'),(5,'DEPOSITARY','Depositary','Provider'),(6,'LAWYER','Legal Counsel','Provider'),(7,'BANK','Bank','Provider');

--
-- Table structure for table `securities`
--

CREATE TABLE `securities` (
  `security_id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text,
  `isin` varchar(50) DEFAULT NULL,
  `cusip` varchar(20) DEFAULT NULL,
  `sedol` varchar(20) DEFAULT NULL,
  `figi` varchar(50) DEFAULT NULL,
  `ticker` varchar(50) DEFAULT NULL,
  `type` enum('Equity_Quoted','Equity_Unquoted','Bond','Fund','RealEstate','Derivative') NOT NULL,
  `currency` char(3) DEFAULT 'EUR',
  `issuer_name` varchar(255) DEFAULT NULL,
  `lei` varchar(50) DEFAULT NULL,
  `status` enum('Active','Inactive','Watchlist','Archived') DEFAULT 'Active',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `primary_exchange` varchar(100) DEFAULT NULL,
  `shares_outstanding` decimal(20,2) DEFAULT NULL,
  `par_value` decimal(20,4) DEFAULT NULL,
  `issue_date` date DEFAULT NULL,
  `listing_date` date DEFAULT NULL,
  `maturity_date` date DEFAULT NULL,
  `coupon_rate` decimal(10,4) DEFAULT NULL,
  `coupon_frequency` varchar(50) DEFAULT NULL,
  `day_count_convention` varchar(50) DEFAULT NULL,
  `settlement_days` int DEFAULT NULL,
  `industry_sector_code` varchar(100) DEFAULT NULL,
  `credit_rating` varchar(20) DEFAULT NULL,
  `tax_code` varchar(50) DEFAULT NULL,
  `data_source` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`security_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `securities`
--

INSERT INTO `securities` VALUES (1,'TEDA *1','Loans to the Italian healthcare system Guaranteed by the Italian State','ZZIT78215873',NULL,NULL,NULL,'TEDA1','Bond','EUR','TEDA',NULL,'Active','2025-12-15 09:08:48',NULL,NULL,NULL,NULL,NULL,NULL,6.0000,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(2,'TEDA / SB CAPITAL #1','oans to the Italian healthcare system (SBC Comp 2) Guaranteed by the Italian State','ZZIT50443907',NULL,NULL,NULL,'TEDA / SB CAPITAL*1','Bond','EUR','TEDA',NULL,'Active','2025-12-15 09:14:12',NULL,NULL,NULL,NULL,NULL,NULL,6.0000,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(3,'TEDA / SB CAPITAL #2','Loans to the Italian healthcare system (SBC Comp 3) Guaranteed by the Italian State','ZZIT42394564',NULL,NULL,NULL,'TEDA / SB CAPITAL #2','Bond','EUR','TEDA',NULL,'Active','2025-12-15 09:16:20',NULL,NULL,NULL,NULL,NULL,NULL,6.0000,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(4,'TEDA 7 PHARMACIES','Loans to 7 independent pharmacies. (Not guaranteed by the Italian State)','ZZIT72332492',NULL,NULL,NULL,'TEDA 7 PHARMACIES','Bond','EUR','TEDA',NULL,'Active','2025-12-15 09:19:42',NULL,NULL,NULL,NULL,NULL,NULL,6.0000,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(5,'TEDA DISAR','Loans to 1 radiologist. (Not guaranteed by the Italian State) DISAR','ZZIT62106435',NULL,NULL,NULL,'TEDA DISAR','Bond','EUR','TEDA',NULL,'Active','2025-12-15 09:27:21',NULL,NULL,NULL,NULL,NULL,NULL,6.0000,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(6,'TEDA MERCHANT','Loans to two pharmacies. (Not guaranteed by the Italian State)','ZZIT91690276',NULL,NULL,NULL,'TEDA MERCHANT','Bond','EUR','TEDA',NULL,'Active','2025-12-15 09:29:03',NULL,NULL,NULL,NULL,NULL,NULL,6.0000,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(7,'S.O. CAPITAL #1','Loans to the Italian healthcare system Guaranteed by the Italian State','ZZIT92298335',NULL,NULL,NULL,'S.O. CAPITAL #1','Bond','EUR','SOCAPITAL',NULL,'Active','2025-12-15 09:41:12',NULL,NULL,NULL,NULL,NULL,NULL,7.4975,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(8,'S.O. CAPITAL #2 PINETA GRANDE','Loans to the Italian healthcare system PINETA GRANDE (Guaranteed by the Italian State)','ZZIT25765673',NULL,NULL,NULL,'S.O. CAPITAL #2 PINETA GRANDE','Bond','EUR','SOCAPITAL',NULL,'Active','2025-12-15 09:43:10',NULL,NULL,NULL,NULL,NULL,NULL,7.4975,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(9,'FARMACIA CONTE','Loans to 1 independent pharmacy. In procedure + Issue with high rate. (Not guaranteed by the Italian State)','ZZIT59827753',NULL,NULL,NULL,'FARMACIA CONTE','Bond','EUR','CONTE',NULL,'Active','2025-12-15 09:45:16',NULL,NULL,NULL,NULL,NULL,NULL,10.0000,'Annual',NULL,NULL,NULL,NULL,NULL,NULL),(10,'test','ceci est un test','ZZLU67545662',NULL,NULL,NULL,NULL,'Bond','EUR','ACCEND',NULL,'Active','2025-12-15 10:02:51',NULL,NULL,NULL,NULL,NULL,NULL,4.9996,'Annual',NULL,NULL,NULL,NULL,NULL,NULL);

--
-- Table structure for table `security_notes`
--

CREATE TABLE `security_notes` (
  `note_id` int NOT NULL AUTO_INCREMENT,
  `security_id` int NOT NULL,
  `note_date` date NOT NULL COMMENT 'Effective date of the note',
  `note_type` varchar(50) DEFAULT 'General',
  `note_content` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'System input timestamp',
  PRIMARY KEY (`note_id`),
  KEY `security_id` (`security_id`),
  CONSTRAINT `security_notes_ibfk_1` FOREIGN KEY (`security_id`) REFERENCES `securities` (`security_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `security_notes`
--

INSERT INTO `security_notes` VALUES (1,10,'2025-12-01','Alert','ne rembourse pas','2025-12-15 10:03:31');

--
-- Table structure for table `stakeholder_individual`
--

CREATE TABLE `stakeholder_individual` (
  `stakeholder_id` int NOT NULL,
  `civility` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `maiden_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `job_title` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `department` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `birth_date` date DEFAULT NULL,
  `preferred_language` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `nationality` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`stakeholder_id`),
  CONSTRAINT `stakeholder_individual_ibfk_1` FOREIGN KEY (`stakeholder_id`) REFERENCES `stakeholders` (`stakeholder_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stakeholder_individual`
--

INSERT INTO `stakeholder_individual` VALUES (1,'Mr.','Stéphane','LE ROY','','Gérant','','1968-11-26','French',NULL,NULL),(2,'Mr.','Bob','VINARNIC','','','','1958-06-06','French',NULL,NULL),(3,'Mr.','Luigi','PAGANI','','','','1963-03-07','English',NULL,NULL);

--
-- Table structure for table `stakeholder_legal_entity`
--

CREATE TABLE `stakeholder_legal_entity` (
  `stakeholder_id` int NOT NULL,
  `legal_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `trade_name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `legal_form` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `registration_number` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `jurisdiction_country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `website` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payment_terms` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tax_id` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `share_capital` decimal(15,2) DEFAULT NULL,
  `currency` char(3) COLLATE utf8mb4_unicode_ci DEFAULT 'EUR',
  PRIMARY KEY (`stakeholder_id`),
  CONSTRAINT `stakeholder_legal_entity_ibfk_1` FOREIGN KEY (`stakeholder_id`) REFERENCES `stakeholders` (`stakeholder_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stakeholder_legal_entity`
--

INSERT INTO `stakeholder_legal_entity` VALUES (4,'','','SARL-S','B252889','Luxembourg','www.accend.lu','','LU32885185',4000.00,'EUR'),(5,'PedersoliGatai','PGLEX','','13183470965','','https://pglex.it/en/','','IT13183470965',NULL,'EUR');

--
-- Table structure for table `stakeholder_relationships`
--

CREATE TABLE `stakeholder_relationships` (
  `relationship_id` int NOT NULL AUTO_INCREMENT,
  `person_id` int NOT NULL,
  `company_id` int NOT NULL,
  `job_title` varchar(100) DEFAULT NULL,
  `is_primary_contact` tinyint(1) DEFAULT '0',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`relationship_id`),
  KEY `person_id` (`person_id`),
  KEY `company_id` (`company_id`),
  CONSTRAINT `stakeholder_relationships_ibfk_1` FOREIGN KEY (`person_id`) REFERENCES `stakeholders` (`stakeholder_id`) ON DELETE CASCADE,
  CONSTRAINT `stakeholder_relationships_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `stakeholders` (`stakeholder_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `stakeholder_relationships`
--


--
-- Table structure for table `stakeholders`
--

CREATE TABLE `stakeholders` (
  `stakeholder_id` int NOT NULL AUTO_INCREMENT,
  `stakeholder_type` enum('Individual','LegalEntity') COLLATE utf8mb4_unicode_ci NOT NULL,
  `contact_status` enum('Active','Inactive','Prospect','Client','Supplier','Other') COLLATE utf8mb4_unicode_ci DEFAULT 'Active',
  `display_name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postal_address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `postal_code` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `phone` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` enum('Active','Archived') COLLATE utf8mb4_unicode_ci DEFAULT 'Active',
  PRIMARY KEY (`stakeholder_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `stakeholders`
--

INSERT INTO `stakeholders` VALUES (1,'Individual','Active','Stéphane LE ROY','Luxembourg','5 rue de l\'horizon','5960','ITZIG','stephane@accend.lu','621629767','','2025-12-14 17:35:35','2025-12-14 18:49:22','Active'),(2,'Individual','Active','Bob VINARNIC','Luxembourg','15, rue Glesener','1631','Luxembourg','bvinarnic@iridium.lu','661585820','','2025-12-14 17:35:59','2025-12-14 18:51:36','Active'),(3,'Individual','Active','Luigi PAGANI','Italy','Piazzale Medaglie d’oro, 3','20145','Milano','luigi.pagani@ellepi.eu','+39 334 8602634','','2025-12-14 18:54:49','2025-12-14 18:55:57','Active'),(4,'LegalEntity','Active','ACCEND Sàrl-s','Luxembourg','5 rue de l\'horizon','5960','ITZIG','stephane@accend.lu','621629767','','2025-12-15 08:45:28','2025-12-15 08:45:28','Active'),(5,'LegalEntity','Active','PGLEX','','','','','pedersoligattai@pec.pglex.it','','','2025-12-15 10:58:26','2025-12-15 10:58:26','Active');

--
-- Table structure for table `supplier_budgets`
--

CREATE TABLE `supplier_budgets` (
  `budget_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int NOT NULL,
  `supplier_id` int NOT NULL,
  `year` int NOT NULL,
  `category_id` int NOT NULL,
  `amount` decimal(15,2) DEFAULT '0.00',
  `currency` varchar(3) DEFAULT 'EUR',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`budget_id`),
  UNIQUE KEY `unique_budget` (`entity_id`,`supplier_id`,`year`,`category_id`),
  KEY `supplier_id` (`supplier_id`),
  KEY `category_id` (`category_id`),
  CONSTRAINT `supplier_budgets_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE,
  CONSTRAINT `supplier_budgets_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `stakeholders` (`stakeholder_id`) ON DELETE CASCADE,
  CONSTRAINT `supplier_budgets_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `invoice_categories` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `supplier_budgets`
--


--
-- Table structure for table `supplier_invoices`
--

CREATE TABLE `supplier_invoices` (
  `invoice_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int NOT NULL,
  `supplier_id` int NOT NULL,
  `category_id` int DEFAULT NULL,
  `invoice_number` varchar(100) DEFAULT NULL,
  `invoice_date` date DEFAULT NULL,
  `received_date` date DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `amount` decimal(15,2) DEFAULT NULL,
  `currency` varchar(3) DEFAULT 'EUR',
  `status` enum('Received','Approved','Paid','Rejected') DEFAULT 'Received',
  `payment_date` date DEFAULT NULL,
  `file_path` varchar(255) DEFAULT NULL,
  `created_by` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`invoice_id`),
  KEY `entity_id` (`entity_id`),
  KEY `supplier_id` (`supplier_id`),
  KEY `category_id` (`category_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `supplier_invoices_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE CASCADE,
  CONSTRAINT `supplier_invoices_ibfk_2` FOREIGN KEY (`supplier_id`) REFERENCES `stakeholders` (`stakeholder_id`) ON DELETE CASCADE,
  CONSTRAINT `supplier_invoices_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `invoice_categories` (`category_id`),
  CONSTRAINT `supplier_invoices_ibfk_4` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `supplier_invoices`
--


--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `task_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int DEFAULT NULL,
  `related_agenda_item_id` int DEFAULT NULL,
  `title` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `owner_user_id` int DEFAULT NULL,
  `due_date` date DEFAULT NULL,
  `status` enum('Open','In Progress','Done','Cancelled') COLLATE utf8mb4_unicode_ci DEFAULT 'Open',
  `priority` enum('Low','Medium','High') COLLATE utf8mb4_unicode_ci DEFAULT 'Medium',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`task_id`),
  KEY `entity_id` (`entity_id`),
  KEY `owner_user_id` (`owner_user_id`),
  KEY `fk_tasks_agenda_item` (`related_agenda_item_id`),
  CONSTRAINT `fk_tasks_agenda_item` FOREIGN KEY (`related_agenda_item_id`) REFERENCES `meeting_agenda_items` (`agenda_item_id`) ON DELETE SET NULL,
  CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `entities` (`entity_id`) ON DELETE SET NULL,
  CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`owner_user_id`) REFERENCES `users` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tasks`
--


--
-- Table structure for table `transactions`
--

CREATE TABLE `transactions` (
  `transaction_id` int NOT NULL AUTO_INCREMENT,
  `entity_id` int NOT NULL,
  `security_id` int NOT NULL,
  `type` enum('Buy','Sell','CapitalCall','Distribution','Interest','Subscription','Redemption') NOT NULL,
  `trade_date` date NOT NULL,
  `settlement_date` date DEFAULT NULL,
  `quantity` decimal(20,6) DEFAULT NULL,
  `price_per_unit` decimal(20,6) DEFAULT NULL,
  `total_amount` decimal(20,2) DEFAULT NULL,
  `transaction_fees` decimal(20,2) DEFAULT '0.00',
  `tax_amount` decimal(20,2) DEFAULT '0.00',
  `net_amount` decimal(20,2) DEFAULT '0.00',
  `currency` char(3) DEFAULT 'EUR',
  `exchange_rate` decimal(20,6) DEFAULT '1.000000',
  `counterparty` varchar(255) DEFAULT NULL,
  `notes` text,
  `status` enum('Pending','Settled','Cancelled') DEFAULT 'Settled',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`transaction_id`),
  KEY `security_id` (`security_id`),
  CONSTRAINT `transactions_ibfk_1` FOREIGN KEY (`security_id`) REFERENCES `securities` (`security_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `transactions`
--

INSERT INTO `transactions` VALUES (1,1,1,'Buy','2021-01-01','2021-01-03',29488000.000000,100.000000,29488000.00,0.00,0.00,29488000.00,'EUR',1.000000,'','dummy purchase of security','Settled','2025-12-15 10:21:35'),(2,1,2,'Buy','2021-01-01','2021-01-03',5771000.000000,100.000000,5771000.00,0.00,0.00,5771000.00,'EUR',1.000000,'','Dummy buy','Settled','2025-12-15 10:23:47'),(3,1,3,'Buy','2021-01-01','2021-01-03',2428000.000000,100.000000,2428000.00,0.00,0.00,2428000.00,'EUR',1.000000,'','','Settled','2025-12-15 10:24:52'),(4,1,4,'Buy','2021-01-01','2021-01-03',7140000.000000,100.000000,7140000.00,0.00,0.00,7140000.00,'EUR',1.000000,'','','Settled','2025-12-15 10:36:08'),(5,1,5,'Buy','2021-01-01','2021-01-03',1000000.000000,100.000000,1000000.00,0.00,0.00,1000000.00,'EUR',1.000000,'','','Settled','2025-12-15 10:37:27'),(6,1,6,'Buy','2021-01-01','2021-01-03',448000.000000,100.000000,448000.00,0.00,0.00,448000.00,'EUR',1.000000,'','','Settled','2025-12-15 10:38:01'),(7,1,7,'Buy','2021-01-01','2021-01-03',9449000.000000,100.000000,9449000.00,0.00,0.00,9449000.00,'EUR',1.000000,'','','Settled','2025-12-15 10:38:53'),(8,1,8,'Buy','2021-01-01','2021-01-03',12301000.000000,100.000000,12301000.00,0.00,0.00,12301000.00,'EUR',1.000000,'','','Settled','2025-12-15 10:39:22'),(9,1,9,'Buy','2021-01-01','2021-01-03',1500000.000000,100.000000,1500000.00,0.00,0.00,1500000.00,'EUR',1.000000,'','','Settled','2025-12-15 10:39:57');

--
-- Table structure for table `valuations`
--

CREATE TABLE `valuations` (
  `valuation_id` int NOT NULL AUTO_INCREMENT,
  `security_id` int NOT NULL,
  `valuation_date` date NOT NULL,
  `price` decimal(20,6) DEFAULT NULL,
  `currency` char(3) DEFAULT 'EUR',
  `source` varchar(100) DEFAULT NULL,
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`valuation_id`),
  KEY `security_id` (`security_id`),
  CONSTRAINT `valuations_ibfk_1` FOREIGN KEY (`security_id`) REFERENCES `securities` (`security_id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb3;

--
-- Dumping data for table `valuations`
--

INSERT INTO `valuations` VALUES (1,1,'2024-12-31',50.000000,'EUR','Internal Valuation','','2025-12-15 09:11:21'),(2,1,'2023-12-31',50.000000,'EUR','Internal Valuation','','2025-12-15 09:12:11'),(3,4,'2023-12-31',50.000000,'EUR','Internal Valuation','','2025-12-15 09:24:56'),(4,2,'2023-12-31',50.000000,'EUR','Internal Valuation','','2025-12-15 09:25:31'),(5,3,'2023-12-31',50.000000,'EUR','Internal Valuation','','2025-12-15 09:25:52'),(6,5,'2023-12-31',0.000000,'EUR','Internal Valuation','','2025-12-15 09:27:35'),(7,6,'2023-12-31',0.000000,'EUR','Internal Valuation','','2025-12-15 09:29:21'),(8,7,'2023-12-31',70.000000,'EUR','Internal Valuation','','2025-12-15 09:41:41'),(9,8,'2023-12-31',70.000000,'EUR','Internal Valuation','','2025-12-15 09:43:34'),(10,9,'2023-12-31',0.000000,'EUR','Internal Valuation','','2025-12-15 09:45:30'),(11,10,'2025-12-15',100.000000,'EUR','Internal Valuation','','2025-12-15 10:03:01');


