-- =====================================================
-- CEK RELASI PROYEK - PELANGGAN - PIC
-- =====================================================

SELECT 
    TABLE_NAME,
    COLUMN_NAME,
    COLUMN_TYPE,
    IS_NULLABLE,
    COLUMN_KEY
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME IN ('proyek', 'pelanggan', 'kontak_person')
AND COLUMN_NAME IN (
    'id_proyek',
    'id_pelanggan',
    'id_kontak_person'
)
ORDER BY TABLE_NAME, COLUMN_NAME;

-- Jika kolom proyek.id_kontak_person belum ada:
-- ALTER TABLE proyek ADD id_kontak_person BIGINT(20) UNSIGNED NULL AFTER id_pelanggan;
-- ALTER TABLE proyek ADD INDEX idx_proyek_kontak_person (id_kontak_person);

-- FK opsional. Jalankan hanya jika sudah siap dan semua data valid:
-- ALTER TABLE proyek
-- ADD CONSTRAINT fk_proyek_kontak
-- FOREIGN KEY (id_kontak_person)
-- REFERENCES kontak_person(id_kontak_person)
-- ON DELETE SET NULL ON UPDATE CASCADE;