-- Run once on staging database only; existing partner profiles remain intact.
CREATE TABLE IF NOT EXISTS nl_partner_access (
 partner_id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
 password_hash VARCHAR(255) DEFAULT NULL,
 invite_hash CHAR(64) DEFAULT NULL,
 invite_expires DATETIME DEFAULT NULL,
 last_login DATETIME DEFAULT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY (partner_id) REFERENCES nl_partner_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS nl_partner_jobs (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 external_booking_ref VARCHAR(120) DEFAULT NULL,
 seat_class VARCHAR(30) NOT NULL,
 pickup_province VARCHAR(100) NOT NULL,
 pickup_ward VARCHAR(120) NOT NULL DEFAULT '',
 pickup_note VARCHAR(500) NOT NULL DEFAULT '',
 trip_note TEXT,
 departure_at DATETIME NOT NULL,
 status ENUM('open','accepted','cancelled','expired') NOT NULL DEFAULT 'open',
 accepted_partner_id BIGINT UNSIGNED DEFAULT NULL,
 accepted_at DATETIME DEFAULT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 UNIQUE KEY uq_external_booking (external_booking_ref),
 INDEX idx_job_status (status,created_at),
 FOREIGN KEY (accepted_partner_id) REFERENCES nl_partner_profiles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS nl_partner_offers (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 job_id BIGINT UNSIGNED NOT NULL,
 partner_id BIGINT UNSIGNED NOT NULL,
 batch_number INT NOT NULL,
 offered_at DATETIME NOT NULL,
 expires_at DATETIME NOT NULL,
 status ENUM('offered','accepted','expired','lost') NOT NULL DEFAULT 'offered',
 UNIQUE KEY uq_job_partner(job_id,partner_id),
 INDEX idx_job_batch(job_id,batch_number),
 INDEX idx_partner_offer(partner_id,status),
 FOREIGN KEY (job_id) REFERENCES nl_partner_jobs(id) ON DELETE CASCADE,
 FOREIGN KEY (partner_id) REFERENCES nl_partner_profiles(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;