-- Client Notifications Table
CREATE TABLE IF NOT EXISTS client_notifications (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    type ENUM('general', 'project', 'payment', 'ticket', 'system') DEFAULT 'general',
    priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
    is_read TINYINT(1) DEFAULT 0,
    created_at DATETIME NOT NULL,
    read_at DATETIME NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client_read (client_id, is_read),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Project Comments Table
CREATE TABLE IF NOT EXISTS project_comments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    project_id INT NOT NULL,
    user_type ENUM('client', 'staff') NOT NULL,
    user_id INT NOT NULL,
    comment TEXT NOT NULL,
    parent_id INT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    FOREIGN KEY (project_id) REFERENCES client_projects(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES project_comments(id) ON DELETE CASCADE,
    INDEX idx_project (project_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add project_id to client_tickets
ALTER TABLE client_tickets 
ADD COLUMN project_id INT NULL AFTER client_id;

ALTER TABLE client_tickets
ADD CONSTRAINT fk_ticket_project FOREIGN KEY (project_id) REFERENCES client_projects(id) ON DELETE SET NULL;
