CREATE TABLE IF NOT EXISTS users(
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(120) NOT NULL,
 email VARCHAR(160) NOT NULL UNIQUE,
 password VARCHAR(255) NOT NULL,
 role ENUM('founder','manager','team_leader','employee') NOT NULL DEFAULT 'employee',
 reports_to INT NULL,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL,
 FOREIGN KEY (reports_to) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS clients(
 id INT AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(160) NOT NULL,
 pan VARCHAR(20) NULL,
 gstin VARCHAR(30) NULL,
 contact_person VARCHAR(120) NULL,
 phone VARCHAR(30) NULL,
 email VARCHAR(160) NULL,
 address TEXT NULL,
 created_by INT NOT NULL,
 created_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS projects(
 id INT AUTO_INCREMENT PRIMARY KEY,
 client_id INT NULL,
 name VARCHAR(180) NOT NULL,
 service_type ENUM('accounting','gst','income_tax','tds','audit','roc','payroll','consulting','other') NOT NULL DEFAULT 'other',
 financial_year VARCHAR(20) NULL,
 description TEXT NULL,
 created_by INT NOT NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS tasks(
 id INT AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(220) NOT NULL,
 description TEXT NULL,
 client_id INT NULL,
 project_id INT NULL,
 service_type ENUM('accounting','gst','income_tax','tds','audit','roc','payroll','consulting','other') NOT NULL DEFAULT 'other',
 compliance_type ENUM('monthly','quarterly','annual','one_time') NOT NULL DEFAULT 'one_time',
 period_label VARCHAR(80) NULL,
 assigned_to INT NOT NULL,
 created_by INT NOT NULL,
 status ENUM('todo','in_progress','review','completed') NOT NULL DEFAULT 'todo',
 priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium',
 due_date DATE NULL,
 completed_at DATETIME NULL,
 created_at DATETIME NOT NULL,
 updated_at DATETIME NULL,
 FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE SET NULL,
 FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL,
 FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE RESTRICT,
 FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS task_comments(
 id INT AUTO_INCREMENT PRIMARY KEY,
 task_id INT NOT NULL,
 user_id INT NOT NULL,
 comment TEXT NOT NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS task_attachments(
 id INT AUTO_INCREMENT PRIMARY KEY,
 task_id INT NOT NULL,
 user_id INT NOT NULL,
 filename VARCHAR(255) NOT NULL,
 original_name VARCHAR(255) NOT NULL,
 created_at DATETIME NOT NULL,
 FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE,
 FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS activity_logs(
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NULL,
 action VARCHAR(120) NOT NULL,
 details TEXT NULL,
 created_at DATETIME NOT NULL,
 INDEX(user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;