
# ------------------------------------------------------------
# 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;

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'
  );

