CREATE TABLE `financial_entities` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(20) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `tax_id` VARCHAR(50) NULL,
  `address` VARCHAR(255) NULL,
  `description` TEXT NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  KEY `financial_entities_type_name_index` (`type`, `name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `financial_documents` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(20) NOT NULL,
  `club_id` BIGINT UNSIGNED NOT NULL,
  `admin_id` INT UNSIGNED NULL,
  `entity_id` BIGINT UNSIGNED NULL,
  `description` TEXT NOT NULL,
  `typology` VARCHAR(80) NOT NULL,
  `invoice_date` DATE NOT NULL,
  `category_code` VARCHAR(30) NULL,
  `category_name` VARCHAR(255) NULL,
  `invoice_number` VARCHAR(191) NOT NULL,
  `document_path` VARCHAR(255) NULL,
  `total_amount` DECIMAL(12,2) NOT NULL,
  `due_date` DATE NULL,
  `is_settled` TINYINT(1) NOT NULL DEFAULT 0,
  `settled_at` TIMESTAMP NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `financial_documents_invoice_number_unique` (`invoice_number`),
  KEY `financial_documents_type_club_id_invoice_date_index` (`type`, `club_id`, `invoice_date`),
  KEY `financial_documents_club_id_due_date_index` (`club_id`, `due_date`),
  KEY `financial_documents_admin_id_foreign` (`admin_id`),
  KEY `financial_documents_entity_id_foreign` (`entity_id`),
  CONSTRAINT `financial_documents_club_id_foreign` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `financial_documents_admin_id_foreign` FOREIGN KEY (`admin_id`) REFERENCES `admins` (`id`) ON DELETE SET NULL,
  CONSTRAINT `financial_documents_entity_id_foreign` FOREIGN KEY (`entity_id`) REFERENCES `financial_entities` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `financial_document_vat_lines` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `financial_document_id` BIGINT UNSIGNED NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `vat_rate` DECIMAL(5,2) NOT NULL DEFAULT 0,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  KEY `financial_document_vat_lines_financial_document_id_foreign` (`financial_document_id`),
  CONSTRAINT `financial_document_vat_lines_financial_document_id_foreign` FOREIGN KEY (`financial_document_id`) REFERENCES `financial_documents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `financial_document_splits` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `financial_document_id` BIGINT UNSIGNED NOT NULL,
  `club_id` BIGINT UNSIGNED NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL,
  `created_at` TIMESTAMP NULL,
  `updated_at` TIMESTAMP NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `financial_splits_document_club_unique` (`financial_document_id`, `club_id`),
  KEY `financial_document_splits_club_id_foreign` (`club_id`),
  CONSTRAINT `financial_document_splits_financial_document_id_foreign` FOREIGN KEY (`financial_document_id`) REFERENCES `financial_documents` (`id`) ON DELETE CASCADE,
  CONSTRAINT `financial_document_splits_club_id_foreign` FOREIGN KEY (`club_id`) REFERENCES `clubs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;