--
-- Host: localhost    Database: yachtmemory_app
-- ------------------------------------------------------


--
-- Table structure for table `dashboard_widgets`
--

CREATE TABLE `dashboard_widgets` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `widget_key` varchar(100) NOT NULL,
  `widget_name` varchar(150) NOT NULL,
  `description` text,
  `default_sort_order` int NOT NULL DEFAULT '100',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `widget_key` (`widget_key`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `dashboard_widgets`
--

INSERT INTO `dashboard_widgets` VALUES (1,'nav_environment','Navigation / Umwelt','Position, COG, SOG, Tiefe, Wind, Temperaturen',10,1,'2026-04-02 11:06:21'),(2,'diesel_generator','Dieselgenerator','Generatorstatus und Motorwerte',20,1,'2026-04-02 11:06:21'),(3,'batteries','Batterien','HV- und LV-Batteriedaten',30,1,'2026-04-02 11:06:21'),(4,'map','Karte / Position','Kartenansicht mit Schiffsposition',40,1,'2026-04-02 11:06:21'),(5,'anchorages','Ankerplätze','Liste und Karte der Anchorages',50,1,'2026-04-02 11:06:21'),(6,'systems','Systeme','Systeminformationen und technische Links',60,1,'2026-04-02 11:06:21'),(7,'settings','Einstellungen','Dashboard-Einstellungen und Wartung',70,1,'2026-04-02 11:06:21'),(8,'admin_users','Benutzerverwaltung','Administration von Benutzern und Rollen',80,1,'2026-04-02 11:06:21'),(9,'documents','Dokumente','Dokumentation und technische Unterlagen',90,1,'2026-04-11 10:17:03');

--
-- Table structure for table `mesh_peers`
--

CREATE TABLE `mesh_peers` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `vessel_id` int unsigned NOT NULL,
  `peer_name` varchar(120) COLLATE utf8mb4_unicode_ci NOT NULL,
  `public_key` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `assigned_ip` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` enum('pending','active','disabled','error') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending',
  `last_handshake_at` datetime DEFAULT NULL,
  `notes` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_mesh_vessel` (`vessel_id`),
  UNIQUE KEY `uq_mesh_ip` (`assigned_ip`),
  CONSTRAINT `fk_mesh_peers_vessel` FOREIGN KEY (`vessel_id`) REFERENCES `vessels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `mesh_peers`
--

INSERT INTO `mesh_peers` VALUES (1,1,'silent_sea',NULL,'10.99.0.2','pending',NULL,'Nachträglich für bestehendes Schiff angelegt. Public Key fehlt noch.','2026-04-29 14:22:09','2026-05-05 15:41:05'),(2,2,'santa_maria',NULL,'10.99.0.30','pending',NULL,'Nachträglich für bestehendes Schiff angelegt. Public Key fehlt noch.','2026-04-29 14:32:12',NULL),(3,3,'fastnet_lady',NULL,'10.99.0.31','pending',NULL,'Nachträglich für bestehendes Schiff angelegt. Public Key fehlt noch.','2026-04-29 14:32:56',NULL),(5,8,'santa2',NULL,'10.99.0.32','pending',NULL,'Nachträglich für bestehendes Schiff angelegt. Public Key fehlt noch.','2026-04-29 14:33:57',NULL),(6,7,'santa1','BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB=','10.99.0.33','pending',NULL,'Nachträglich für bestehendes Schiff angelegt. Public Key fehlt noch.','2026-04-29 14:35:48','2026-04-30 20:07:57'),(7,10,'keykeykey','AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA=','10.99.0.34','pending',NULL,'Public Key aus Provisioning-Anfrage #1 übernommen. Wartet auf Server-WG-Freigabe.','2026-04-30 21:36:05','2026-04-30 21:36:05');

--
-- Table structure for table `organization_users`
--

CREATE TABLE `organization_users` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `organization_id` int unsigned NOT NULL,
  `user_id` int unsigned NOT NULL,
  `org_role` enum('org_admin','org_manager','org_user') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'org_user',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_org_user` (`organization_id`,`user_id`),
  KEY `idx_org_users_user` (`user_id`),
  CONSTRAINT `fk_org_users_org` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_org_users_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `organization_users`
--

INSERT INTO `organization_users` VALUES (1,3,1,'org_admin',1,'2026-04-08 15:28:14'),(2,2,1,'org_admin',1,'2026-04-08 15:28:14'),(3,1,1,'org_admin',1,'2026-04-08 15:28:14'),(4,5,4,'org_admin',1,'2026-04-10 12:43:33'),(12,4,5,'org_manager',1,'2026-04-10 14:55:37'),(14,1,3,'org_user',1,'2026-04-11 07:21:11'),(15,1,6,'org_user',1,'2026-04-11 09:29:38');

--
-- Table structure for table `organizations`
--

CREATE TABLE `organizations` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `org_name` varchar(150) COLLATE utf8mb4_unicode_ci NOT NULL,
  `org_key` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
  `org_type` enum('private_owner','charter','fleet','service') COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'private_owner',
  `parent_organization_id` int unsigned DEFAULT NULL,
  `contact_email` varchar(190) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_org_key` (`org_key`),
  KEY `idx_organizations_parent` (`parent_organization_id`),
  CONSTRAINT `fk_organizations_parent` FOREIGN KEY (`parent_organization_id`) REFERENCES `organizations` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `organizations`
--

INSERT INTO `organizations` VALUES (1,'Silent Sea','silent_sea_org','private_owner',NULL,'mail@fjweber.de',1,'2026-04-08 15:28:14','2026-04-10 11:23:21'),(2,'Santa Maria','santa_maria_org','private_owner',NULL,NULL,1,'2026-04-08 15:28:14','2026-04-08 15:28:14'),(3,'Fastnet Lady','fastnet_lady_org','private_owner',NULL,NULL,1,'2026-04-08 15:28:14','2026-04-08 15:28:14'),(4,'SicilyCharter','SicilyCharter','charter',5,'sicilycharter@fjweber.de',1,'2026-04-10 11:21:21','2026-04-10 11:22:52'),(5,'MediCharter','MediCharter','fleet',NULL,'MediCharter@fjweber.de',1,'2026-04-10 11:22:14','2026-04-10 11:22:14'),(6,'MaltaCharter','maltacharter','charter',5,'maltacharter@fjweber.de',1,'2026-04-10 13:08:02','2026-04-10 13:08:02');

--
-- Table structure for table `product_modules`
--

CREATE TABLE `product_modules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `module_key` varchar(100) NOT NULL,
  `module_name` varchar(150) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `description` text,
  `is_core` tinyint(1) NOT NULL DEFAULT '0',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `module_key` (`module_key`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `product_modules`
--

INSERT INTO `product_modules` VALUES (1,'navigation_core','Navigation Core','core','Position, SOG, COG, Zeit und Basis-Navigationsdaten',1,1,'2026-03-24 22:18:16'),(2,'environment_core','Environment Core','core','Wind, Tiefe, Umweltdaten und grundlegende NMEA-Umfelddaten',1,1,'2026-03-24 22:18:16'),(3,'diesel_engine','Dieselmotor','engine','Motordaten aus NMEA / CAN fuer konventionelle Antriebe',0,1,'2026-03-24 22:18:16'),(4,'electric_twin_drive','Electric Twin Drive','engine','Erweiterte Daten fuer zwei elektrische Antriebe',0,1,'2026-03-24 22:18:16'),(5,'anchor_counter','Kettenzaehler','anchor','Yachtmemory Modul fuer Kettenlaenge',0,1,'2026-03-24 22:18:16'),(6,'anchor_force','Kettenkraftsensor','anchor','Yachtmemory Modul fuer Kettenzugkraft',0,1,'2026-03-24 22:18:16'),(7,'bilge_sensor','Bilgesensor','safety','Yachtmemory Bilge- und Wasseralarmmodul',0,1,'2026-03-24 22:18:16'),(8,'motion_sensor','Bewegungssensor','safety','Bewegungs- und Lageueberwachung',0,1,'2026-03-24 22:18:16'),(9,'autopilot','Autopilot','control','Yachtmemory Autopilotmodul',0,1,'2026-03-24 22:18:16');

--
-- Table structure for table `public_sharing_settings`
--

CREATE TABLE `public_sharing_settings` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `vessel_id` int unsigned NOT NULL,
  `show_overview` tinyint(1) NOT NULL DEFAULT '0',
  `show_position` tinyint(1) NOT NULL DEFAULT '0',
  `show_anchorages` tinyint(1) NOT NULL DEFAULT '0',
  `show_route` tinyint(1) NOT NULL DEFAULT '0',
  `show_weather` tinyint(1) NOT NULL DEFAULT '0',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_public_vessel` (`vessel_id`),
  CONSTRAINT `fk_pss_vessel` FOREIGN KEY (`vessel_id`) REFERENCES `vessels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `public_sharing_settings`
--

INSERT INTO `public_sharing_settings` VALUES (1,1,1,1,1,0,0,'2026-03-24 19:08:25'),(2,2,1,1,1,0,0,'2026-03-24 21:18:47');

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

CREATE TABLE `roles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `role_name` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `role_name` (`role_name`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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

INSERT INTO `roles` VALUES (1,'superadmin','Systemweite Vollrechte'),(2,'owner','Vollzugriff auf eigene Yacht'),(3,'crew','Operativer Zugriff auf eigene Yacht'),(4,'guest','Eingeschraenkter Zugriff auf freigegebene Daten'),(5,'public','Oeffentliche anonyme Ansicht');

--
-- Table structure for table `user_vessel_roles`
--

CREATE TABLE `user_vessel_roles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int unsigned NOT NULL,
  `vessel_id` int unsigned NOT NULL,
  `role_id` int unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_user_vessel_role` (`user_id`,`vessel_id`,`role_id`),
  KEY `fk_uvr_vessel` (`vessel_id`),
  KEY `fk_uvr_role` (`role_id`),
  CONSTRAINT `fk_uvr_role` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_uvr_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_uvr_vessel` FOREIGN KEY (`vessel_id`) REFERENCES `vessels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `user_vessel_roles`
--

INSERT INTO `user_vessel_roles` VALUES (3,3,1,2,'2026-04-01 16:24:57'),(10,1,2,1,'2026-04-08 10:10:32'),(11,1,1,1,'2026-04-08 10:10:32'),(12,1,3,1,'2026-04-08 10:10:32');

--
-- Table structure for table `vessel_modules`
--

CREATE TABLE `vessel_modules` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `vessel_id` int unsigned NOT NULL,
  `module_id` int unsigned NOT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT '1',
  `settings_json` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_vessel_module` (`vessel_id`,`module_id`),
  UNIQUE KEY `uq_vessel_module` (`vessel_id`,`module_id`),
  KEY `fk_vm_module` (`module_id`),
  CONSTRAINT `fk_vm_module` FOREIGN KEY (`module_id`) REFERENCES `product_modules` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vm_vessel` FOREIGN KEY (`vessel_id`) REFERENCES `vessels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `vessel_modules`
--

INSERT INTO `vessel_modules` VALUES (1,1,5,1,NULL,'2026-03-24 22:18:39','2026-04-15 18:56:28'),(2,1,6,1,NULL,'2026-03-24 22:18:39','2026-04-15 18:56:28'),(3,1,7,1,NULL,'2026-03-24 22:18:39','2026-04-15 18:56:28'),(4,1,3,1,NULL,'2026-03-24 22:18:39','2026-04-15 18:56:28'),(5,1,2,1,NULL,'2026-03-24 22:18:39','2026-04-15 18:56:28'),(6,1,1,1,NULL,'2026-03-24 22:18:39','2026-04-15 18:56:28'),(8,2,3,1,NULL,'2026-03-24 22:18:59','2026-03-24 22:18:59'),(9,2,2,1,NULL,'2026-03-24 22:18:59','2026-03-24 22:18:59'),(10,2,1,1,NULL,'2026-03-24 22:18:59','2026-03-24 22:18:59'),(11,3,5,1,NULL,'2026-04-15 18:53:43','2026-04-15 22:24:14'),(12,3,3,1,NULL,'2026-04-15 19:05:59','2026-04-15 22:24:14'),(13,3,6,1,NULL,'2026-04-15 22:24:14','2026-04-15 22:24:14');

--
-- Table structure for table `vessel_website_images`
--

CREATE TABLE `vessel_website_images` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `vessel_id` int unsigned NOT NULL,
  `filename` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `original_filename` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caption_de` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caption_en` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caption_it` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `caption_fr` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sort_order` int unsigned NOT NULL DEFAULT '100',
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_website_images_vessel` (`vessel_id`),
  CONSTRAINT `fk_website_images_vessel` FOREIGN KEY (`vessel_id`) REFERENCES `vessels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `vessel_website_images`
--

INSERT INTO `vessel_website_images` VALUES (8,1,'20260506_100542_175a3bda.jpg','20220818_161927.jpg','Bei Cassis, Südfrankreich','Near Cassis, South France','Vicino a Cassis','Près de Cassis',100,1,'2026-05-06 10:05:42'),(9,1,'20260506_100632_e9c81005.jpg','20230517_084454.jpg','Torre d\' Embarra, Spanien','Torre d\' Embarra, Spain','Torre d\' Embarra, Espania','Torre d\' Embarra, Espagne',100,1,'2026-05-06 10:06:33'),(10,1,'20260506_100715_ce2b2309.jpg','20220905_160850.jpg','Barcelona, wir sind die Kleinen','Barcelona, we are the small one','Barcelona, sono la piccola','Barcelona, nou somes le petit',100,1,'2026-05-06 10:07:15'),(11,1,'20260506_100809_5d577c5b.jpg','20240422_141942.jpg','Pesaro','Pesaro','Pesaro','Pesaro',100,1,'2026-05-06 10:08:10'),(12,1,'20260506_101007_28e0977f.jpg','20240422_150231.jpg','Pesaro','Pesaro','Pesaro','Pesaro',100,1,'2026-05-06 10:10:07'),(13,1,'20260506_101057_afd00d83.jpg','20230501_112208.jpg','Einer der E-Motore','electric engine','motore electrico','moteur électrique',100,1,'2026-05-06 10:10:57'),(14,1,'20260506_101127_11e64669.jpg','20230604_223020.jpg','Unser Wohnzimmer','Our Living Room','Il nostro soggiorno','Notre salon',100,1,'2026-05-06 10:11:28');

--
-- Table structure for table `vessel_website_profiles`
--

CREATE TABLE `vessel_website_profiles` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `vessel_id` int unsigned NOT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT '0',
  `website_title` varchar(180) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `owner_display_name` varchar(180) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_email` varchar(180) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `contact_phone` varchar(80) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `imprint_text_de` text COLLATE utf8mb4_unicode_ci,
  `imprint_text_en` text COLLATE utf8mb4_unicode_ci,
  `imprint_text_it` text COLLATE utf8mb4_unicode_ci,
  `imprint_text_fr` text COLLATE utf8mb4_unicode_ci,
  `privacy_text_de` text COLLATE utf8mb4_unicode_ci,
  `privacy_text_en` text COLLATE utf8mb4_unicode_ci,
  `privacy_text_it` text COLLATE utf8mb4_unicode_ci,
  `privacy_text_fr` text COLLATE utf8mb4_unicode_ci,
  `generated_path` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_generated_at` datetime DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_website_profile_vessel` (`vessel_id`),
  CONSTRAINT `fk_website_profile_vessel` FOREIGN KEY (`vessel_id`) REFERENCES `vessels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `vessel_website_profiles`
--

INSERT INTO `vessel_website_profiles` VALUES (1,10,0,'TEST-BOAT-001',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'2026-04-30 21:36:05',NULL);

--
-- Table structure for table `vessel_website_texts`
--

CREATE TABLE `vessel_website_texts` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `vessel_id` int unsigned NOT NULL,
  `lang` enum('de','en','it','fr') COLLATE utf8mb4_unicode_ci NOT NULL,
  `intro_title` varchar(180) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `intro_text` text COLLATE utf8mb4_unicode_ci,
  `vessel_text` text COLLATE utf8mb4_unicode_ci,
  `technology_text` text COLLATE utf8mb4_unicode_ci,
  `gallery_intro` text COLLATE utf8mb4_unicode_ci,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_vessel_website_text_lang` (`vessel_id`,`lang`),
  CONSTRAINT `fk_website_texts_vessel` FOREIGN KEY (`vessel_id`) REFERENCES `vessels` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `vessel_website_texts`
--

INSERT INTO `vessel_website_texts` VALUES (1,10,'de','TEST-BOAT-001',NULL,NULL,NULL,NULL,'2026-04-30 21:36:05',NULL),(2,10,'en','TEST-BOAT-001',NULL,NULL,NULL,NULL,'2026-04-30 21:36:05',NULL),(3,10,'it','TEST-BOAT-001',NULL,NULL,NULL,NULL,'2026-04-30 21:36:05',NULL),(4,10,'fr','TEST-BOAT-001',NULL,NULL,NULL,NULL,'2026-04-30 21:36:05',NULL);

--
-- Table structure for table `vessel_widget_visibility`
--

CREATE TABLE `vessel_widget_visibility` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `vessel_id` int unsigned NOT NULL,
  `widget_id` int unsigned NOT NULL,
  `is_enabled` tinyint(1) NOT NULL DEFAULT '1',
  `is_public` tinyint(1) NOT NULL DEFAULT '0',
  `sort_order` int NOT NULL DEFAULT '100',
  `allowed_roles_json` text,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_vessel_widget` (`vessel_id`,`widget_id`),
  KEY `fk_vwv_widget` (`widget_id`),
  CONSTRAINT `fk_vwv_vessel` FOREIGN KEY (`vessel_id`) REFERENCES `vessels` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_vwv_widget` FOREIGN KEY (`widget_id`) REFERENCES `dashboard_widgets` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=294 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `vessel_widget_visibility`
--

INSERT INTO `vessel_widget_visibility` VALUES (28,2,1,1,0,10,'[]','2026-04-02 12:10:53','2026-04-02 12:10:53'),(29,2,2,1,0,20,'[]','2026-04-02 12:10:53','2026-04-02 12:10:53'),(30,2,3,1,0,30,'[]','2026-04-02 12:10:53','2026-04-02 12:10:53'),(31,2,4,1,1,40,'[]','2026-04-02 12:10:53','2026-04-02 12:10:53'),(32,2,5,1,1,50,'[]','2026-04-02 12:10:53','2026-04-02 12:10:53'),(33,2,6,1,0,60,'[]','2026-04-02 12:10:53','2026-04-02 12:10:53'),(34,2,7,1,0,70,'[]','2026-04-02 12:10:53','2026-04-02 12:10:53'),(35,2,8,1,0,80,'[]','2026-04-02 12:10:53','2026-04-02 12:10:53'),(180,8,1,1,1,10,'[\"org_admin\",\"superadmin\"]','2026-04-11 09:25:53','2026-04-11 09:25:53'),(181,8,2,1,1,20,'[]','2026-04-11 09:25:53','2026-04-11 09:25:53'),(182,8,3,1,0,30,'[]','2026-04-11 09:25:53','2026-04-11 09:25:53'),(183,8,4,1,1,40,'[\"org_admin\",\"superadmin\"]','2026-04-11 09:25:53','2026-04-11 09:25:53'),(184,8,5,1,0,50,'[]','2026-04-11 09:25:53','2026-04-11 09:25:53'),(185,8,6,1,0,60,'[]','2026-04-11 09:25:53','2026-04-11 09:25:53'),(186,8,7,1,0,70,'[\"org_admin\",\"superadmin\"]','2026-04-11 09:25:53','2026-04-11 09:25:53'),(187,8,8,1,0,80,'[]','2026-04-11 09:25:53','2026-04-11 09:25:53'),(258,3,1,1,1,10,'[\"org_user\",\"org_manager\",\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(259,3,2,1,0,20,'[\"org_user\",\"org_manager\",\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(260,3,3,1,0,30,'[\"org_user\",\"org_manager\",\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(261,3,4,1,1,40,'[\"org_user\",\"org_manager\",\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(262,3,5,1,1,50,'[\"org_user\",\"org_manager\",\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(263,3,6,1,0,60,'[\"org_manager\",\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(264,3,7,1,0,70,'[\"org_manager\",\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(265,3,8,1,0,80,'[\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(266,3,9,1,0,90,'[\"org_manager\",\"org_admin\",\"superadmin\"]','2026-04-12 15:23:19','2026-04-12 15:23:19'),(285,1,1,1,1,10,'[\"org_user\",\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19'),(286,1,2,1,0,20,'[\"org_user\",\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19'),(287,1,3,1,0,30,'[\"org_user\",\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19'),(288,1,4,1,1,40,'[\"org_user\",\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19'),(289,1,5,1,1,50,'[\"org_user\",\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19'),(290,1,6,1,0,60,'[\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19'),(291,1,7,1,0,70,'[\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19'),(292,1,8,1,0,70,'[\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19'),(293,1,9,1,0,90,'[\"org_user\",\"org_admin\",\"superadmin\"]','2026-04-13 11:33:19','2026-04-13 11:33:19');

--
-- Table structure for table `vessels`
--

CREATE TABLE `vessels` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `organization_id` int unsigned NOT NULL,
  `vessel_key` varchar(100) NOT NULL,
  `vessel_name` varchar(150) NOT NULL,
  `description` text,
  `hull_type` enum('monohull','catamaran') DEFAULT NULL,
  `vessel_category` enum('sailing','motor') DEFAULT NULL,
  `propulsion_type` enum('diesel','electric','hybrid','other') DEFAULT NULL,
  `engine_count` tinyint unsigned DEFAULT NULL,
  `engine_power_kw` decimal(8,2) DEFAULT NULL,
  `battery_capacity_kwh` decimal(10,2) DEFAULT NULL,
  `solar_power_kw` decimal(8,2) DEFAULT NULL,
  `generator_power_kw` decimal(8,2) DEFAULT NULL,
  `telemetry_db_name` varchar(128) DEFAULT NULL,
  `public_site_url` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) NOT NULL DEFAULT '1',
  `onboarding_status` enum('draft','registered','mesh_pending','mesh_active','telemetry_active','public_active') NOT NULL DEFAULT 'draft',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `website_enabled` tinyint(1) DEFAULT '0',
  `website_hero_image_id` int unsigned DEFAULT NULL,
  `website_hero_position` varchar(40) NOT NULL DEFAULT 'center center',
  `website_title_de` varchar(200) DEFAULT NULL,
  `website_title_en` varchar(200) DEFAULT NULL,
  `website_title_it` varchar(200) DEFAULT NULL,
  `website_title_fr` varchar(200) DEFAULT NULL,
  `website_intro_de` text,
  `website_intro_en` text,
  `website_intro_it` text,
  `website_intro_fr` text,
  `website_owner_name` varchar(150) DEFAULT NULL,
  `website_contact_email` varchar(150) DEFAULT NULL,
  `website_contact_phone` varchar(80) DEFAULT NULL,
  `website_blog_url` varchar(255) DEFAULT NULL,
  `website_home_port` varchar(120) DEFAULT NULL,
  `website_year_built` varchar(20) DEFAULT NULL,
  `website_imprint` text,
  `website_privacy` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `vessel_key` (`vessel_key`),
  UNIQUE KEY `uq_vessels_org_vesselkey` (`organization_id`,`vessel_key`),
  KEY `idx_vessels_org` (`organization_id`),
  CONSTRAINT `fk_vessels_org` FOREIGN KEY (`organization_id`) REFERENCES `organizations` (`id`) ON DELETE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `vessels`
--

INSERT INTO `vessels` VALUES (1,1,'silent_sea','Silent-Sea','bla bla','catamaran','motor','electric',2,100.00,180.00,10.00,60.00,'SilentMessDB','https://www.silent-sea.de',1,'draft','2026-03-24 19:08:21','2026-05-22 11:52:47',1,NULL,'center center','Silent-Sea','Silent-Sea','Silent-Sea','Silent-Sea','Hi, hier die Webseite','Welcome to our website.','Chiao!!','Salut!','Fritz-Jochen Weber','mail@fjweber.de','',NULL,'Wir haben keinen Heimathafen','2018','Fritz-Jochen und Martina Weber\r\nWildbader Straße 7a\r\n68239 Mannheim\r\nDeutschland',''),(2,2,'santa_maria','Santa Maria','Test- und Demo-Schiff im Yachtmemory-System','monohull','sailing','diesel',NULL,NULL,NULL,NULL,NULL,'SantaMariaDB','https://www.santa-maria.example',1,'draft','2026-03-24 21:18:26','2026-04-29 14:15:28',0,NULL,'center center',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(3,3,'fastnet_lady','Fastnet Lady',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'FastnetMessDB55','https://www.fastnetlady.de',1,'draft','2026-04-08 07:55:42','2026-04-09 06:24:42',0,NULL,'center center',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(7,2,'santa1','Santa1',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'tlm_santa_maria_org_santa1',NULL,0,'draft','2026-04-10 09:42:01','2026-04-10 09:42:01',0,NULL,'center center',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(8,4,'santa5','Santa5',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'tlm_medicharter_santa5',NULL,1,'draft','2026-04-10 11:57:04','2026-04-10 13:40:18',0,NULL,'center center',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(10,5,'keykeykey','TEST-BOAT-001','Beschreibung hier','monohull','sailing','diesel',NULL,NULL,NULL,NULL,NULL,'TestBoat1DB','',1,'registered','2026-04-30 21:36:05','2026-04-30 21:36:05',0,NULL,'center center',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);


