-- ==========================================================
-- Install tabel Penjadwalan Perawat Simple
-- Aman dijalankan berkali-kali karena memakai IF NOT EXISTS.
-- ==========================================================

CREATE TABLE IF NOT EXISTS nurse_schedule_periods (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    title VARCHAR(180) NOT NULL,
    unit_name VARCHAR(150) DEFAULT NULL,
    schedule_month TINYINT UNSIGNED NOT NULL,
    schedule_year SMALLINT UNSIGNED NOT NULL,
    token VARCHAR(80) NOT NULL,
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    unit_code_legend TEXT DEFAULT NULL,
    notes TEXT DEFAULT NULL,
    created_by BIGINT UNSIGNED DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_nurse_schedule_token (token),
    KEY idx_nurse_schedule_period (schedule_year, schedule_month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nurse_schedule_rows (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    period_id BIGINT UNSIGNED NOT NULL,
    nurse_name VARCHAR(180) NOT NULL,
    position_title VARCHAR(120) DEFAULT NULL,
    sort_order INT UNSIGNED NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_nurse_schedule_rows_period (period_id),
    CONSTRAINT fk_nurse_schedule_rows_period
        FOREIGN KEY (period_id) REFERENCES nurse_schedule_periods(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS nurse_schedule_cells (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    row_id BIGINT UNSIGNED NOT NULL,
    schedule_date DATE NOT NULL,
    shift_code VARCHAR(30) DEFAULT NULL,
    notes VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uk_nurse_schedule_cell (row_id, schedule_date),
    KEY idx_nurse_schedule_cells_date (schedule_date),
    CONSTRAINT fk_nurse_schedule_cells_row
        FOREIGN KEY (row_id) REFERENCES nurse_schedule_rows(id)
        ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


-- Jika tabel sudah pernah dibuat dari versi sebelumnya, tambahkan kolom keterangan kode unit.
-- Aman dijalankan berulang karena dicek lewat INFORMATION_SCHEMA.
SET @col_exists := (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'nurse_schedule_periods'
      AND COLUMN_NAME = 'unit_code_legend'
);
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE nurse_schedule_periods ADD COLUMN unit_code_legend TEXT DEFAULT NULL AFTER is_public',
    'SELECT 1'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
