USE inventory_db;

ALTER TABLE clients 
ADD COLUMN y_tunnus VARCHAR(255) NULL AFTER company_name,
ADD INDEX idx_y_tunnus (y_tunnus);

CREATE TABLE IF NOT EXISTS contact_persons (
    id INT(11) AUTO_INCREMENT PRIMARY KEY,
    client_id INT(11) NOT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NULL,
    phone VARCHAR(20) NULL,
    position VARCHAR(100) NULL,
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    INDEX idx_client_primary (client_id, is_primary),
    INDEX idx_name (last_name, first_name)
);