CREATE TABLE IF NOT EXISTS organisations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    nudge_email VARCHAR(255) UNIQUE NOT NULL,
    api_key VARCHAR(64) UNIQUE NOT NULL,
    active TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS meetings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    org_id INT NOT NULL,
    uid VARCHAR(255) NOT NULL,
    organizer_email VARCHAR(255) NOT NULL,
    summary VARCHAR(500),
    start_time DATETIME NOT NULL,
    end_time DATETIME,
    attendees JSON,
    nudge_sent TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_org_uid (org_id, uid),
    FOREIGN KEY (org_id) REFERENCES organisations(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS nudges (
    id INT AUTO_INCREMENT PRIMARY KEY,
    org_id INT NOT NULL,
    meeting_id INT NOT NULL,
    recipient_email VARCHAR(255) NOT NULL,
    dimension CHAR(1) NOT NULL,
    message_index INT NOT NULL,
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    response ENUM('included','not_applicable','remind_later') DEFAULT NULL,
    responded_at TIMESTAMP NULL,
    score INT DEFAULT 0,
    FOREIGN KEY (org_id) REFERENCES organisations(id),
    FOREIGN KEY (meeting_id) REFERENCES meetings(id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS leap_scores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    org_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    dimension CHAR(1) NOT NULL,
    score INT NOT NULL,
    recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (org_id) REFERENCES organisations(id),
    INDEX idx_org_email (org_id, email),
    INDEX idx_org_dimension (org_id, dimension)
) ENGINE=InnoDB;