cd "C:\Users\CSH\Desktop\Project\cctv_report"
node server.js

mysql

cctv_report

users
    user_id pk
    name
    email
    password (no bcrypt)

categories
    category_id pk
    user_id fk
    category_title

locations
    location_id pk
    category_id fk
    user_id fk
    location_title

cameras
    camera_id pk
    location_id fk
    category_id fk
    user_id fk
    camera_title

reports
    report_id pk
    camera_id fk
    location_id fk
    category_id fk
    user_id fk
    status enum Online,Offline,No Recording
    date timestamp, only day,month and year

public
    auth.html
    index.html
server.js

const port = process.env.PORT || 3002;

// MySQL connection
const db = mysql.createConnection({
    host: 'localhost',
    user: 'itsupport',
    password: 'P@ss1234',
    database: 'cctv_report',
    port: '3307'
});

app password
cctv
izify0665@gmail.com
qkwldoaoudvvmxct




category e.g.mihome,yihome...
    location e.g.location1,location2...
        camera list in table e.g.cam1,cam2.. with  date and status












-- Create new database
DROP DATABASE IF EXISTS `cctv_report`;
CREATE DATABASE `cctv_report` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE `cctv_report`;

-- Table: users
CREATE TABLE `users` (
  `user_id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: categories
CREATE TABLE `categories` (
  `category_id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NOT NULL,
  `category_title` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`category_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: locations
CREATE TABLE `locations` (
  `location_id` INT NOT NULL AUTO_INCREMENT,
  `category_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `location_title` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`location_id`),
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: cameras
CREATE TABLE `cameras` (
  `camera_id` INT NOT NULL AUTO_INCREMENT,
  `location_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `camera_title` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`camera_id`),
  FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: reports
CREATE TABLE `reports` (
  `report_id` INT NOT NULL AUTO_INCREMENT,
  `camera_id` INT NOT NULL,
  `location_id` INT NOT NULL,
  `category_id` INT NOT NULL,
  `user_id` INT NOT NULL,
  `status` ENUM('Online','Offline','No Recording') NOT NULL,
  `date` DATE NOT NULL,
  PRIMARY KEY (`report_id`),
  UNIQUE KEY `camera_date_unique` (`camera_id`, `date`),
  FOREIGN KEY (`camera_id`) REFERENCES `cameras` (`camera_id`) ON DELETE CASCADE,
  FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE CASCADE,
  FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table: password_reset_codes
CREATE TABLE `password_reset_codes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `email` VARCHAR(255) NOT NULL,
  `code` VARCHAR(6) NOT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `expires_at` TIMESTAMP NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;





-- Table structure for table `parts`
CREATE TABLE `parts` (
  `part_id` int NOT NULL AUTO_INCREMENT,
  `camera_id` int NOT NULL,
  `location_id` int NOT NULL,
  `category_id` int NOT NULL,
  `user_id` int NOT NULL,
  `part_name` varchar(255) NOT NULL,
  PRIMARY KEY (`part_id`),
  KEY `camera_id` (`camera_id`),
  KEY `location_id` (`location_id`),
  KEY `category_id` (`category_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `parts_ibfk_1` FOREIGN KEY (`camera_id`) REFERENCES `cameras` (`camera_id`) ON DELETE CASCADE,
  CONSTRAINT `parts_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE CASCADE,
  CONSTRAINT `parts_ibfk_3` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE,
  CONSTRAINT `parts_ibfk_4` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table structure for table `part_history`
CREATE TABLE `part_history` (
  `part_history_id` int NOT NULL AUTO_INCREMENT,
  `part_id` int NOT NULL,
  `camera_id` int NOT NULL,
  `location_id` int NOT NULL,
  `category_id` int NOT NULL,
  `user_id` int NOT NULL,
  `action` enum('Added','Replaced','Removed') NOT NULL DEFAULT 'Added',
  `added_date` date DEFAULT NULL,
  `replaced_date` date DEFAULT NULL,
  `removed_date` date DEFAULT NULL,
  `notes` text,
  PRIMARY KEY (`part_history_id`),
  KEY `part_id` (`part_id`),
  KEY `camera_id` (`camera_id`),
  KEY `location_id` (`location_id`),
  KEY `category_id` (`category_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `part_history_ibfk_1` FOREIGN KEY (`part_id`) REFERENCES `parts` (`part_id`) ON DELETE CASCADE,
  CONSTRAINT `part_history_ibfk_2` FOREIGN KEY (`camera_id`) REFERENCES `cameras` (`camera_id`) ON DELETE CASCADE,
  CONSTRAINT `part_history_ibfk_3` FOREIGN KEY (`location_id`) REFERENCES `locations` (`location_id`) ON DELETE CASCADE,
  CONSTRAINT `part_history_ibfk_4` FOREIGN KEY (`category_id`) REFERENCES `categories` (`category_id`) ON DELETE CASCADE,
  CONSTRAINT `part_history_ibfk_5` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;