
# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: contracts
# ------------------------------------------------------------

CREATE TABLE `contracts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contractNumber` varchar(50) NOT NULL,
  `contractDate` date NOT NULL,
  `secondParty` varchar(200) NOT NULL,
  `location` text NOT NULL,
  `ownerName` varchar(100) NOT NULL,
  `ownerNationalID` varchar(20) NOT NULL,
  `ownerPhoneNumber` varchar(20) NOT NULL,
  `camerasType` varchar(100) NOT NULL,
  `camerasResolution` varchar(50) NOT NULL,
  `innerCamerasNumber` int(11) NOT NULL DEFAULT 0,
  `outerCamerasNumber` int(11) NOT NULL DEFAULT 0,
  `storageCapacity` text NOT NULL,
  `startsFrom` date NOT NULL,
  `endsAt` date NOT NULL,
  `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attachments`)),
  `notes` text DEFAULT NULL,
  `status` enum('active', 'expired', 'cancelled') DEFAULT 'active',
  `createdBy` int(11) NOT NULL,
  `updatedBy` int(11) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `contractNumber` (`contractNumber`),
  UNIQUE KEY `contractNumber_2` (`contractNumber`),
  UNIQUE KEY `contractNumber_3` (`contractNumber`),
  UNIQUE KEY `contractNumber_4` (`contractNumber`),
  UNIQUE KEY `contractNumber_5` (`contractNumber`),
  UNIQUE KEY `contractNumber_6` (`contractNumber`),
  UNIQUE KEY `contractNumber_7` (`contractNumber`),
  UNIQUE KEY `contractNumber_8` (`contractNumber`),
  UNIQUE KEY `contractNumber_9` (`contractNumber`),
  UNIQUE KEY `contractNumber_10` (`contractNumber`),
  UNIQUE KEY `contractNumber_11` (`contractNumber`),
  UNIQUE KEY `contractNumber_12` (`contractNumber`),
  UNIQUE KEY `contractNumber_13` (`contractNumber`),
  UNIQUE KEY `contractNumber_14` (`contractNumber`),
  UNIQUE KEY `contractNumber_15` (`contractNumber`),
  UNIQUE KEY `contractNumber_16` (`contractNumber`),
  UNIQUE KEY `contractNumber_17` (`contractNumber`),
  UNIQUE KEY `contractNumber_18` (`contractNumber`),
  UNIQUE KEY `contractNumber_19` (`contractNumber`),
  UNIQUE KEY `contractNumber_20` (`contractNumber`),
  UNIQUE KEY `contractNumber_21` (`contractNumber`),
  UNIQUE KEY `contractNumber_22` (`contractNumber`),
  UNIQUE KEY `contractNumber_23` (`contractNumber`),
  UNIQUE KEY `contractNumber_24` (`contractNumber`),
  UNIQUE KEY `contractNumber_25` (`contractNumber`),
  UNIQUE KEY `contractNumber_26` (`contractNumber`),
  UNIQUE KEY `contractNumber_27` (`contractNumber`),
  UNIQUE KEY `contractNumber_28` (`contractNumber`),
  KEY `contracts_contract_number` (`contractNumber`),
  KEY `contracts_contract_date` (`contractDate`),
  KEY `contracts_second_party` (`secondParty`),
  KEY `contracts_status` (`status`),
  KEY `contracts_starts_from_ends_at` (`startsFrom`, `endsAt`),
  KEY `createdBy` (`createdBy`),
  KEY `updatedBy` (`updatedBy`),
  CONSTRAINT `contracts_ibfk_1` FOREIGN KEY (`createdBy`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `contracts_ibfk_2` FOREIGN KEY (`updatedBy`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 2 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: scenes
# ------------------------------------------------------------

CREATE TABLE `scenes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `contractId` int(11) NOT NULL,
  `date` date NOT NULL,
  `action` varchar(100) NOT NULL,
  `applicantName` varchar(100) NOT NULL,
  `applicantPhone` varchar(20) NOT NULL,
  `attachments` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`attachments`)),
  `notes` text DEFAULT NULL,
  `status` enum('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
  `technician` varchar(100) DEFAULT NULL,
  `startTime` time DEFAULT NULL,
  `endTime` time DEFAULT NULL,
  `workDescription` text DEFAULT NULL,
  `createdBy` int(11) NOT NULL,
  `updatedBy` int(11) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `scenes_contract_id` (`contractId`),
  KEY `scenes_date` (`date`),
  KEY `scenes_action` (`action`),
  KEY `scenes_status` (`status`),
  KEY `scenes_applicant_name` (`applicantName`),
  KEY `createdBy` (`createdBy`),
  KEY `updatedBy` (`updatedBy`),
  CONSTRAINT `scenes_ibfk_67` FOREIGN KEY (`contractId`) REFERENCES `contracts` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `scenes_ibfk_68` FOREIGN KEY (`createdBy`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `scenes_ibfk_69` FOREIGN KEY (`updatedBy`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 3 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: settings
# ------------------------------------------------------------

CREATE TABLE `settings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key` varchar(100) NOT NULL,
  `value` text NOT NULL,
  `type` enum('string', 'number', 'boolean', 'json') DEFAULT 'string',
  `description` text DEFAULT NULL,
  `category` varchar(50) NOT NULL DEFAULT 'general',
  `updatedBy` int(11) DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `key` (`key`),
  UNIQUE KEY `key_2` (`key`),
  UNIQUE KEY `key_3` (`key`),
  UNIQUE KEY `key_4` (`key`),
  UNIQUE KEY `key_5` (`key`),
  UNIQUE KEY `key_6` (`key`),
  UNIQUE KEY `key_7` (`key`),
  UNIQUE KEY `key_8` (`key`),
  UNIQUE KEY `key_9` (`key`),
  UNIQUE KEY `key_10` (`key`),
  UNIQUE KEY `key_11` (`key`),
  UNIQUE KEY `key_12` (`key`),
  UNIQUE KEY `key_13` (`key`),
  UNIQUE KEY `key_14` (`key`),
  UNIQUE KEY `key_15` (`key`),
  UNIQUE KEY `key_16` (`key`),
  UNIQUE KEY `key_17` (`key`),
  UNIQUE KEY `key_18` (`key`),
  UNIQUE KEY `key_19` (`key`),
  UNIQUE KEY `key_20` (`key`),
  UNIQUE KEY `key_21` (`key`),
  UNIQUE KEY `key_22` (`key`),
  UNIQUE KEY `key_23` (`key`),
  KEY `settings_key` (`key`),
  KEY `settings_category` (`category`),
  KEY `updatedBy` (`updatedBy`),
  CONSTRAINT `settings_ibfk_1` FOREIGN KEY (`updatedBy`) REFERENCES `users` (`id`) ON DELETE
  SET
  NULL ON UPDATE CASCADE
) ENGINE = InnoDB AUTO_INCREMENT = 12 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

# ------------------------------------------------------------
# SCHEMA DUMP FOR TABLE: users
# ------------------------------------------------------------

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userName` varchar(50) NOT NULL,
  `fullName` varchar(100) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `password` varchar(255) NOT NULL,
  `permissions` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`permissions`)),
  `userType` enum('admin', 'dataEntry') NOT NULL DEFAULT 'dataEntry',
  `isActive` tinyint(1) DEFAULT 1,
  `lastLogin` datetime DEFAULT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userName` (`userName`),
  UNIQUE KEY `userName_2` (`userName`),
  UNIQUE KEY `userName_3` (`userName`),
  UNIQUE KEY `userName_4` (`userName`),
  UNIQUE KEY `userName_5` (`userName`),
  UNIQUE KEY `userName_6` (`userName`),
  UNIQUE KEY `userName_7` (`userName`),
  UNIQUE KEY `userName_8` (`userName`),
  UNIQUE KEY `userName_9` (`userName`),
  UNIQUE KEY `userName_10` (`userName`),
  UNIQUE KEY `userName_11` (`userName`),
  UNIQUE KEY `userName_12` (`userName`),
  UNIQUE KEY `userName_13` (`userName`),
  UNIQUE KEY `userName_14` (`userName`),
  UNIQUE KEY `userName_15` (`userName`),
  UNIQUE KEY `userName_16` (`userName`),
  UNIQUE KEY `userName_17` (`userName`),
  UNIQUE KEY `userName_18` (`userName`),
  UNIQUE KEY `userName_19` (`userName`),
  UNIQUE KEY `userName_20` (`userName`),
  UNIQUE KEY `userName_21` (`userName`),
  UNIQUE KEY `userName_22` (`userName`),
  UNIQUE KEY `userName_23` (`userName`),
  UNIQUE KEY `userName_24` (`userName`),
  UNIQUE KEY `userName_25` (`userName`),
  UNIQUE KEY `userName_26` (`userName`),
  UNIQUE KEY `userName_27` (`userName`),
  UNIQUE KEY `userName_28` (`userName`)
) ENGINE = InnoDB AUTO_INCREMENT = 4 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_general_ci;

INSERT INTO
  `contracts` (
    `id`,
    `contractNumber`,
    `contractDate`,
    `secondParty`,
    `location`,
    `ownerName`,
    `ownerNationalID`,
    `ownerPhoneNumber`,
    `camerasType`,
    `camerasResolution`,
    `innerCamerasNumber`,
    `outerCamerasNumber`,
    `storageCapacity`,
    `startsFrom`,
    `endsAt`,
    `attachments`,
    `notes`,
    `status`,
    `createdBy`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    1,
    '1',
    '2025-10-08',
    'test',
    'test',
    'owner',
    '00000000000',
    '0000000000',
    'test',
    '1080',
    2,
    2,
    '2TB',
    '2020-10-10',
    '2022-10-10',
    '[]',
    NULL,
    'expired',
    1,
    1,
    '2025-10-08 12:10:25',
    '2025-10-08 13:41:30'
  );

INSERT INTO
  `scenes` (
    `id`,
    `contractId`,
    `date`,
    `action`,
    `applicantName`,
    `applicantPhone`,
    `attachments`,
    `notes`,
    `status`,
    `technician`,
    `startTime`,
    `endTime`,
    `workDescription`,
    `createdBy`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    1,
    1,
    '2025-10-08',
    'صيانة',
    'test',
    '0000000000',
    '[]',
    '',
    'pending',
    'test',
    '13:30:00',
    '13:35:00',
    '',
    1,
    1,
    '2025-10-08 12:32:29',
    '2025-10-08 13:22:28'
  );
INSERT INTO
  `scenes` (
    `id`,
    `contractId`,
    `date`,
    `action`,
    `applicantName`,
    `applicantPhone`,
    `attachments`,
    `notes`,
    `status`,
    `technician`,
    `startTime`,
    `endTime`,
    `workDescription`,
    `createdBy`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    2,
    1,
    '2025-10-08',
    'صيانة',
    'owner',
    '0000000000',
    '[]',
    '',
    'pending',
    'test',
    '14:05:00',
    '14:10:00',
    '',
    1,
    1,
    '2025-10-08 13:05:52',
    '2025-10-08 13:43:48'
  );

INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    1,
    'next_contract_number',
    '2',
    'number',
    'رقم العقد التالي',
    'contracts',
    1,
    '2025-10-07 23:40:05',
    '2025-10-08 12:10:25'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    2,
    'contract_number_prefix',
    'C-',
    'string',
    'بادئة رقم العقد',
    'contracts',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    3,
    'print_header',
    'نظام إدارة عقود الصيانة',
    'string',
    'رأس الصفحة للطباعة',
    'print',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    4,
    'print_footer',
    'جميع الحقوق محفوظة',
    'string',
    'تذييل الصفحة للطباعة',
    'print',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    5,
    'print_margins',
    '{\"top\":\"20mm\",\"bottom\":\"20mm\",\"left\":\"15mm\",\"right\":\"15mm\"}',
    'json',
    'هوامش الصفحة للطباعة',
    'print',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    6,
    'backup_enabled',
    'true',
    'boolean',
    'تفعيل النسخ الاحتياطي التلقائي',
    'backup',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    7,
    'backup_schedule',
    '0 2 * * *',
    'string',
    'جدولة النسخ الاحتياطي (cron format)',
    'backup',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    8,
    'backup_retention_days',
    '30',
    'number',
    'عدد أيام الاحتفاظ بالنسخ الاحتياطية',
    'backup',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    9,
    'system_name',
    'نظام إدارة عقود الصيانة',
    'string',
    'اسم النظام',
    'system',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    10,
    'max_file_size',
    '10485760',
    'number',
    'الحد الأقصى لحجم الملف (بايت)',
    'system',
    NULL,
    '2025-10-07 23:40:05',
    '2025-10-07 23:40:05'
  );
INSERT INTO
  `settings` (
    `id`,
    `key`,
    `value`,
    `type`,
    `description`,
    `category`,
    `updatedBy`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    11,
    'allowed_file_types',
    '[\"jpg\",\"jpeg\",\"png\",\"gif\",\"pdf\",\"doc\",\"docx\",\"xls\",\"xlsx\"]',
    'json',
    'أنواع الملفات المسموحة',
    'system',
    NULL,
    '2025-10-07 23:40:06',
    '2025-10-07 23:40:06'
  );

INSERT INTO
  `users` (
    `id`,
    `userName`,
    `fullName`,
    `phone`,
    `password`,
    `permissions`,
    `userType`,
    `isActive`,
    `lastLogin`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    1,
    'admin',
    'مدير النظام',
    '1234567890',
    '$2a$12$TFw4W9F7Y75PtSiUPhxpD.Nh6AK6kekj5J/UYOSZA/qHLvzkJzowK',
    '{\"contracts\":{\"add\":true,\"edit\":true,\"view\":true,\"delete\":true,\"print\":true},\"scenes\":{\"add\":true,\"edit\":true,\"view\":true,\"delete\":true,\"print\":true}}',
    'admin',
    1,
    '2025-10-08 16:05:14',
    '2025-10-07 23:40:02',
    '2025-10-08 16:05:14'
  );
INSERT INTO
  `users` (
    `id`,
    `userName`,
    `fullName`,
    `phone`,
    `password`,
    `permissions`,
    `userType`,
    `isActive`,
    `lastLogin`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    2,
    'user1',
    'user ',
    '00000000000',
    '$2a$12$jQPRK572Fnb.Bj0pvrsLH.uA6SmZnEjtGV2IUgX83RtTbWCT4R0TC',
    '{\"contracts\":{\"add\":true,\"edit\":false,\"view\":true,\"delete\":false,\"print\":true},\"scenes\":{\"add\":true,\"edit\":false,\"view\":true,\"delete\":false,\"print\":true},\"reports\":{\"add\":false,\"edit\":false,\"view\":true,\"delete\":false,\"print\":false},\"users\":{\"add\":false,\"edit\":false,\"view\":false,\"delete\":false,\"print\":false}}',
    'dataEntry',
    1,
    '2025-10-08 16:03:25',
    '2025-10-08 15:56:40',
    '2025-10-08 16:03:25'
  );
INSERT INTO
  `users` (
    `id`,
    `userName`,
    `fullName`,
    `phone`,
    `password`,
    `permissions`,
    `userType`,
    `isActive`,
    `lastLogin`,
    `createdAt`,
    `updatedAt`
  )
VALUES
  (
    3,
    'user2',
    'user',
    '00000000000',
    '$2a$12$8OxHIUvCkDHnGYQThR/gzOXhxaZY8BIqHX1Pyw7k4rROwwD5pbyJC',
    '{\"contracts\":{\"add\":true,\"edit\":false,\"view\":true,\"delete\":false,\"print\":true},\"scenes\":{\"add\":true,\"edit\":false,\"view\":true,\"delete\":false,\"print\":true},\"reports\":{\"add\":false,\"edit\":false,\"view\":true,\"delete\":false,\"print\":true},\"users\":{\"add\":false,\"edit\":false,\"view\":false,\"delete\":false,\"print\":false}}',
    'dataEntry',
    1,
    NULL,
    '2025-10-08 15:58:06',
    '2025-10-08 15:58:06'
  );

