CREATE TABLE "activities" (
  "id" varchar(255) NOT NULL,
  "type" varchar(255) NOT NULL,
  "description" text,
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("id")
);

CREATE TABLE "assettypes" (
  "id" varchar(255) NOT NULL,
  "name" varchar(255) NOT NULL,
  "description" text,
  "category" varchar(255) DEFAULT NULL,
  "icon" varchar(255) DEFAULT NULL,
  "maintenanceRequired" tinyint(1) DEFAULT '0',
  "isActive" tinyint(1) DEFAULT '1',
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ("id")
);

CREATE TABLE "attachments" (
  "id" varchar(255) NOT NULL,
  "taskId" varchar(255) DEFAULT NULL,
  "name" varchar(500) NOT NULL,
  "url" varchar(1000) NOT NULL,
  "type" varchar(100) DEFAULT NULL,
  "size" bigint DEFAULT NULL,
  "uploaded_by" json DEFAULT NULL,
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("id"),
  KEY "idx_taskId" ("taskId"),
  CONSTRAINT "attachments_ibfk_1" FOREIGN KEY ("taskId") REFERENCES "tasks" ("id") ON DELETE CASCADE
);

CREATE TABLE "comments" (
  "id" varchar(255) NOT NULL,
  "taskId" varchar(255) DEFAULT NULL,
  "userId" varchar(255) DEFAULT NULL,
  "author" json DEFAULT NULL,
  "text" text NOT NULL,
  "timestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("id"),
  KEY "idx_taskId" ("taskId"),
  KEY "idx_userId" ("userId"),
  CONSTRAINT "comments_ibfk_1" FOREIGN KEY ("taskId") REFERENCES "tasks" ("id") ON DELETE CASCADE
);

CREATE TABLE "departments" (
  "id" varchar(255) NOT NULL,
  "name" varchar(255) NOT NULL,
  "description" text,
  "color" varchar(50) DEFAULT NULL,
  "isActive" tinyint(1) DEFAULT '1',
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ("id")
);

CREATE TABLE "locations" (
  "id" varchar(255) NOT NULL,
  "name" varchar(255) NOT NULL,
  "description" text,
  "address" text,
  "type" varchar(100) DEFAULT NULL,
  "capacity" varchar(100) DEFAULT NULL,
  "isActive" tinyint(1) DEFAULT '1',
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ("id")
);

CREATE TABLE "notifications" (
  "id" varchar(255) NOT NULL,
  "userId" varchar(255) NOT NULL,
  "title" varchar(500) NOT NULL,
  "message" text,
  "type" enum('task_assignment','task_reminder','comment','task_update','task_completion','proof_approval','proof_rejection') NOT NULL,
  "relatedId" varchar(255) DEFAULT NULL,
  "read" tinyint(1) DEFAULT '0',
  "companyid" varchar(255) NOT NULL,
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("id"),
  KEY "idx_userId" ("userId"),
  KEY "idx_companyid" ("companyid"),
  KEY "idx_read" ("read")
);

CREATE TABLE "subtasks" (
  "id" varchar(255) NOT NULL,
  "taskId" varchar(255) DEFAULT NULL,
  "title" varchar(500) NOT NULL,
  "completed" tinyint(1) DEFAULT '0',
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY ("id"),
  KEY "idx_taskId" ("taskId"),
  CONSTRAINT "subtasks_ibfk_1" FOREIGN KEY ("taskId") REFERENCES "tasks" ("id") ON DELETE CASCADE
);

CREATE TABLE "tasks" (
  "id" varchar(255) NOT NULL,
  "title" varchar(500) NOT NULL,
  "description" text,
  "location" varchar(255) DEFAULT NULL,
  "priority" enum('low','medium','high') NOT NULL,
  "status" enum('pending','in_progress','completed','cancelled','overdue') DEFAULT 'pending',
  "due_date" timestamp NULL DEFAULT NULL,
  "assignedTo" varchar(255) DEFAULT NULL,
  "assignedUsers" json DEFAULT NULL,
  "assignedBy" varchar(255) NOT NULL,
  "category" varchar(255) DEFAULT NULL,
  "department" varchar(255) DEFAULT NULL,
  "companyid" varchar(255) NOT NULL,
  "proof" json DEFAULT NULL,
  "proof_types" json DEFAULT NULL,
  "has_proof_required" tinyint(1) DEFAULT '0',
  "proof_submitted" tinyint(1) DEFAULT '0',
  "proof_notes" text,
  "proof_images" json DEFAULT NULL,
  "asset" json DEFAULT NULL,
  "tags" json DEFAULT NULL,
  "frequency" varchar(255) DEFAULT NULL,
  "messageCount" int DEFAULT '0',
  "attachmentCount" int DEFAULT '0',
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  "updated_at" timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ("id"),
  KEY "idx_assignedTo" ("assignedTo"),
  KEY "idx_assignedBy" ("assignedBy"),
  KEY "idx_companyid" ("companyid"),
  KEY "idx_status" ("status"),
  KEY "idx_priority" ("priority")
);

CREATE TABLE "users" (
  "id" varchar(255) NOT NULL,
  "email" varchar(255) NOT NULL,
  "password" varchar(255) NOT NULL,
  "firstName" varchar(255) NOT NULL,
  "lastName" varchar(255) NOT NULL,
  "role" enum('admin','manager','worker') NOT NULL,
  "phone" varchar(50) DEFAULT NULL,
  "avatar" varchar(500) DEFAULT NULL,
  "department" varchar(255) DEFAULT NULL,
  "companyid" varchar(255) NOT NULL,
  "createdAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  "updatedAt" timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY ("id"),
  UNIQUE KEY "email" ("email"),
  KEY "idx_email" ("email"),
  KEY "idx_companyid" ("companyid")
);

