-- ================================
-- 1. Admin Table
-- ================================
CREATE TABLE admin (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(140) NOT NULL
);

-- Default Admin Insert
-- Password: admin123 (hashed with bcrypt - salt rounds: 10)
INSERT INTO admin (email, password)
VALUES ('admin@gmail.com', '$2b$10$8qTvL/BrFCeGgVL8i4Zx/e/DK8dN6zF3bK9xM2pQ5rS7tU1w4s2sG');


-- ================================
-- 2. Category Table
-- ================================
CREATE TABLE category (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);


-- ================================
-- 3. Employee Table
-- ================================
CREATE TABLE employee (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  address VARCHAR(255) NOT NULL,
  salary DECIMAL(10,2) NOT NULL,
  image VARCHAR(255),
  category_id INT,
  FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE SET NULL
);


-- ================================
-- 4. Clock Records Table (Attendance)
-- ================================
CREATE TABLE clock_records (
  id INT AUTO_INCREMENT PRIMARY KEY,
  employee_id INT,
  clock_in TIMESTAMP NOT NULL,
  clock_out TIMESTAMP NULL,
  location VARCHAR(255),
  work_from_type VARCHAR(50),
  FOREIGN KEY (employee_id) REFERENCES employee(id) ON DELETE CASCADE
);


-- ================================
-- 5. Office Locations Table
-- ================================
CREATE TABLE office_locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  latitude DOUBLE NOT NULL,
  longitude DOUBLE NOT NULL,
  address VARCHAR(255)
);


-- ================================
-- 6. Clients Table
-- ================================
CREATE TABLE clients (
  client_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  contact_person VARCHAR(150),
  email VARCHAR(150),
  phone VARCHAR(20),
  address TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);


-- ================================
-- 7. Projects Table
-- ================================
CREATE TABLE projects (
  project_id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  description TEXT,
  created_by INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  status VARCHAR(20) DEFAULT 'Not Started',
  completion_date TIMESTAMP NULL,
  start_date TIMESTAMP NULL,
  priority VARCHAR(20) DEFAULT 'Medium',
  client_id INT,
  FOREIGN KEY (created_by) REFERENCES admin(id) ON DELETE SET NULL,
  FOREIGN KEY (client_id) REFERENCES clients(client_id) ON DELETE SET NULL
);


-- ================================
-- 8. Tasks Table
-- ================================
CREATE TABLE tasks (
  task_id INT AUTO_INCREMENT PRIMARY KEY,
  description TEXT NOT NULL,
  deadline TIMESTAMP NULL,
  status VARCHAR(20) DEFAULT 'pending',
  project_id INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);


-- ================================
-- 9. Task Assignments Table
-- ================================
CREATE TABLE task_assignments (
  id INT AUTO_INCREMENT PRIMARY KEY,
  task_id INT,
  employee_id INT,
  assigned_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (task_id) REFERENCES tasks(task_id) ON DELETE CASCADE,
  FOREIGN KEY (employee_id) REFERENCES employee(id) ON DELETE CASCADE
);


-- ================================
-- 10. Notifications Table
-- ================================
CREATE TABLE notifications (
  notification_id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  message TEXT NOT NULL,
  is_read BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES employee(id) ON DELETE CASCADE
);


CREATE TABLE office_locations (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),
  latitude VARCHAR(255),
  longitude VARCHAR(255),
  address TEXT,
  created_at DATETIME
);
