LevensLoop/levensloop.sql at Master · PCPal-nl/LevensLoop · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
-- 1. Tabel voor de gebruikers
CREATE TABLE IF NOT EXISTS `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `date_of_birth` date DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_access_at` timestamp NULL DEFAULT NULL,
  `registration_ip` varchar(45) DEFAULT NULL,
  `last_access_ip` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 2. Tabel voor de gebeurtenissen (aangepast met user_id)
CREATE TABLE IF NOT EXISTS `gebeurtenissen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `datum` date NOT NULL,
  `titel` varchar(90) NOT NULL,
  `details` text,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `datum` (`datum`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_gebeurtenissen_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 3. Tabel voor de verbanden (aangepast met user_id)
CREATE TABLE IF NOT EXISTS `verbanden` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `gebeurtenis_id_begin` int(11) NOT NULL,
  `gebeurtenis_id_eind` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `link_desc` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_begin_eind` (`gebeurtenis_id_begin`,`gebeurtenis_id_eind`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_verbanden_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gebeurtenis_begin` FOREIGN KEY (`gebeurtenis_id_begin`) REFERENCES `gebeurtenissen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_gebeurtenis_eind` FOREIGN KEY (`gebeurtenis_id_eind`) REFERENCES `gebeurtenissen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 4. Tabel voor de beeldgebeurtenissen
CREATE TABLE IF NOT EXISTS `BeeldGebeurtenissen` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `bestandsnaam` varchar(255) NOT NULL,
  `beschrijving` text,
  `opname_datum` datetime DEFAULT NULL,
  `locatie_lat` decimal(10,8) DEFAULT NULL,
  `locatie_lon` decimal(11,8) DEFAULT NULL,
  `dim_breedte` int(11) DEFAULT NULL,
  `dim_hoogte` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_beeldgebeurtenissen_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 5. Aanpassing aan gebeurtenissen tabel voor koppeling met beeldgebeurtenissen
ALTER TABLE `gebeurtenissen`
ADD COLUMN `beeld_gebeurtenis_id` INT(11) NULL DEFAULT NULL,
ADD CONSTRAINT `fk_gebeurtenissen_beeld` FOREIGN KEY (`beeld_gebeurtenis_id`) REFERENCES `BeeldGebeurtenissen` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;CREATE TABLE documenten (
  id INT AUTO_INCREMENT PRIMARY KEY,
  bestandsnaam VARCHAR(255) NOT NULL,
  bestand_pad VARCHAR(1024) NOT NULL,
  mime_type VARCHAR(100),
  samenvatting TEXT,
  upload_datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE gebeurtenissen
ADD COLUMN document_id INT(11) NULL DEFAULT NULL,
ADD CONSTRAINT fk_document_id
    FOREIGN KEY (document_id) REFERENCES documenten(id)
    ON DELETE SET NULL ON UPDATE CASCADE;
-- 6. Tabel voor urls
CREATE TABLE IF NOT EXISTS `urls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `url` varchar(2048) NOT NULL,
  `external_id` varchar(255) DEFAULT NULL,
  `original_title` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_urls_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 7. Aanpassing aan gebeurtenissen tabel voor koppeling met urls
ALTER TABLE `gebeurtenissen`
ADD COLUMN `url_id` INT(11) NULL DEFAULT NULL,
ADD CONSTRAINT `fk_gebeurtenissen_url` FOREIGN KEY (`url_id`) REFERENCES `urls` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- 8. Table for Audio Files
CREATE TABLE IF NOT EXISTS `audio_files` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `bestandsnaam` varchar(255) NOT NULL,
  `bestand_pad` varchar(1024) NOT NULL,
  `mime_type` varchar(100) DEFAULT NULL,
  `transcriptie` text,
  `upload_datum` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_audio_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 9. Adjustment to events table for linking with audio files
ALTER TABLE `gebeurtenissen`
ADD COLUMN `audio_id` INT(11) NULL DEFAULT NULL,
ADD CONSTRAINT `fk_gebeurtenissen_audio` FOREIGN KEY (`audio_id`) REFERENCES `audio_files` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

-- 10. Table for unified user files (Secure Media System)
CREATE TABLE IF NOT EXISTS `user_files` (
  `id` char(36) NOT NULL,
  `user_id` int(11) NOT NULL,
  `item_id` int(11) NOT NULL,
  `original_filename` varchar(255) NOT NULL,
  `storage_filename` char(36) NOT NULL,
  `mime_type` varchar(100) NOT NULL,
  `file_size` bigint(20) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `item_id` (`item_id`),
  CONSTRAINT `fk_files_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_files_item` FOREIGN KEY (`item_id`) REFERENCES `gebeurtenissen` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- 11. Table for rt_history (Timesheet)
CREATE TABLE IF NOT EXISTS `rt_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `log_date` datetime DEFAULT NULL,
  `duration_seconds` int(11) DEFAULT NULL,
  `activity` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `productivity` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `fk_rt_history_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;