brainstorming and first bytes of code

1 files changed, 109 insertions, 145 deletions

diff --git a/public/app/models/_info.md b/public/app/models/_info.md
index 5504523..0c19c02 100644
--- a/public/app/models/_info.md
+++ b/public/app/models/_info.md
@@ -1,150 +1,114 @@
 
 # info about the structrure of the models
 
-According to the former impementation (atcom) the project will need to handle 200+ tables.
-The former project includes 209 tables with 5 or more rows (and 325 tables totaly)
-
-
-
-## Database schema
-
-Main tables and recomended constraints:
-
-```
-SET NAMES utf8;
-SET time_zone = '+00:00';
-SET foreign_key_checks = 0;
-SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
-
-SET NAMES utf8mb4;
-
-DROP TABLE IF EXISTS `course`;
-CREATE TABLE `course` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `parent_id` int(11) NOT NULL COMMENT 'if 0 then this is a root course',
-  `label` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `lesson`;
-CREATE TABLE `lesson` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `course_id` int(11) NOT NULL,
-  `title` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
-  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
-  `published` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 = unpublished, 1 = published',
-  PRIMARY KEY (`id`),
-  KEY `course_id` (`course_id`),
-  CONSTRAINT `lesson_ibfk_1` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`),
-  CONSTRAINT `lesson_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `course` (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `lesson_media`;
-CREATE TABLE `lesson_media` (
-  `lesson_id` int(11) NOT NULL,
-  `media_id` int(11) NOT NULL,
-  PRIMARY KEY (`lesson_id`,`media_id`),
-  KEY `media_id` (`media_id`),
-  CONSTRAINT `lesson_media_ibfk_1` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`),
-  CONSTRAINT `lesson_media_ibfk_2` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`),
-  CONSTRAINT `lesson_media_ibfk_3` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`),
-  CONSTRAINT `lesson_media_ibfk_4` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`),
-  CONSTRAINT `lesson_media_ibfk_5` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`) ON DELETE NO ACTION,
-  CONSTRAINT `lesson_media_ibfk_6` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE NO ACTION,
-  CONSTRAINT `lesson_media_ibfk_7` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`) ON DELETE NO ACTION,
-  CONSTRAINT `lesson_media_ibfk_8` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE NO ACTION
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `lesson_privilege`;
-CREATE TABLE `lesson_privilege` (
-  `lesson_id` int(11) NOT NULL,
-  `privilege_id` int(11) NOT NULL COMMENT 'minimum privilege required to access the lesson',
-  KEY `lesson_id` (`lesson_id`),
-  KEY `privilege_id` (`privilege_id`),
-  CONSTRAINT `lesson_privilege_ibfk_1` FOREIGN KEY (`lesson_id`) REFERENCES `lesson` (`id`),
-  CONSTRAINT `lesson_privilege_ibfk_2` FOREIGN KEY (`privilege_id`) REFERENCES `privilege` (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `media`;
-CREATE TABLE `media` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `label` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
-  `type` varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL,
-  `path` varchar(320) COLLATE utf8mb4_unicode_ci NOT NULL,
-  `referable` tinyint(4) NOT NULL DEFAULT '1' COMMENT '0 = hidden, 1 = referable',
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `page`;
-CREATE TABLE `page` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `title` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
-  `body` text COLLATE utf8mb4_unicode_ci NOT NULL,
-  `published` tinyint(4) NOT NULL DEFAULT '0' COMMENT '0 = unpublished; 1 = published',
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `page_media`;
-CREATE TABLE `page_media` (
-  `page_id` int(11) NOT NULL,
-  `media_id` int(11) NOT NULL,
-  KEY `page_id` (`page_id`),
-  KEY `media_id` (`media_id`),
-  CONSTRAINT `page_media_ibfk_1` FOREIGN KEY (`page_id`) REFERENCES `page` (`id`) ON DELETE NO ACTION,
-  CONSTRAINT `page_media_ibfk_2` FOREIGN KEY (`media_id`) REFERENCES `media` (`id`) ON DELETE NO ACTION
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `privilege`;
-CREATE TABLE `privilege` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `alias` varchar(8) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT 'keep it simple; use latin',
-  `label` varchar(140) COLLATE utf8mb4_unicode_ci NOT NULL,
-  PRIMARY KEY (`id`),
-  UNIQUE KEY `alias` (`alias`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `privilege_inherit`;
-CREATE TABLE `privilege_inherit` (
-  `higher_id` int(11) NOT NULL COMMENT 'higher priviledges inherit (include) lower ones',
-  `lower_id` int(11) NOT NULL,
-  PRIMARY KEY (`higher_id`,`lower_id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `user`;
-CREATE TABLE `user` (
-  `id` int(11) NOT NULL AUTO_INCREMENT,
-  `first_name` int(11) NOT NULL,
-  `last_name` int(11) NOT NULL,
-  `email` int(11) NOT NULL,
-  `expiration` int(11) NOT NULL COMMENT 'account expiration date; 0 = never',
-  `password` int(11) NOT NULL,
-  `salt` int(11) NOT NULL,
-  `otp` int(11) NOT NULL COMMENT 'one time password for reset password',
-  `otp_expiration` int(11) NOT NULL,
-  `active` int(11) NOT NULL COMMENT 'account flag; 1=active, 0=inactive',
-  PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-
-DROP TABLE IF EXISTS `user_privilege`;
-CREATE TABLE `user_privilege` (
-  `user_id` int(11) NOT NULL,
-  `privilege_id` int(11) NOT NULL,
-  PRIMARY KEY (`user_id`,`privilege_id`),
-  KEY `privilege_id` (`privilege_id`),
-  CONSTRAINT `user_privilege_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
-  CONSTRAINT `user_privilege_ibfk_2` FOREIGN KEY (`privilege_id`) REFERENCES `privilege` (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
-
-```
+## user fields; insert / update sql queries
+
+INSERT INTO user
+( id,
+  prefix,
+  first_name,
+  last_name,
+  email,
+  father_name,
+  registration_number,
+  sector_id,
+  specialty,
+  belonging_school,
+  working_shcool,
+  position_type_id,
+  phone,
+  password,
+  expiration,
+  otp,
+  otp_expiration,
+  activation,
+  creation,
+active) VALUES (:id,
+  :prefix,
+  :first_name,
+  :last_name,
+  :email,
+  :father_name,
+  :registration_number,
+  :sector_id,
+  :specialty,
+  :belonging_school,
+  :working_shcool,
+  :position_type_id,
+  :phone,
+  :password,
+  :expiration,
+  :otp,
+  :otp_expiration,
+  :activation,
+  :creation,
+:active )
+
+
+
+UPDATE user
+SET prefix = :prefix,
+first_name = :first_name,
+last_name = :last_name,
+email = :email,
+father_name = :father_name,
+registration_number = :registration_number,
+sector_id = :sector_id,
+specialty = :specialty,
+belonging_school = :belonging_school,
+working_shcool = :working_shcool,
+position_type_id = :position_type_id,
+phone = :phone,
+password = :password,
+expiration = :expiration,
+otp = :otp,
+otp_expiration = :otp_expiration,
+activation = :activation,
+creation = :creation,
+active = :active,
+
+
+## record_types
+
+document forms -> json
+
+request = {
+  user_id -> user: {
+    ...
+  }
+  date: ...
+  body: ...
+  media: [ ... array of media-id(s) ]
+}
+
+penalty = {
+  user_id -> user: {
+    ...
+  },
+  date:
+  time:
+  subject: 
+  of_student:
+  place:
+  president:
+  members: [ list ]
+  reason:
+  apology:
+  decision:
+}
+
+
+## records
+
+id
+user_id
+type
+source_json
+
+
+## record_media
+
+record_id
+media_id