-- Pack 22.3 – Dynamic Project Status Manager

CREATE TABLE IF NOT EXISTS project_statuses (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    status_key VARCHAR(80) NOT NULL,
    label_el VARCHAR(150) NOT NULL,
    label_en VARCHAR(150) NOT NULL,
    sort_order INT NOT NULL DEFAULT 100,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    is_default TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_project_statuses_key (status_key),
    KEY idx_project_statuses_sort (sort_order),
    KEY idx_project_statuses_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO project_statuses (status_key, label_el, label_en, sort_order, is_active, is_default)
VALUES
('new', 'Νέο', 'New', 10, 1, 1),
('quotation', 'Προσφορά', 'Quotation', 20, 1, 0),
('approved', 'Εγκεκριμένο', 'Approved', 30, 1, 0),
('waiting_deposit', 'Αναμονή προκαταβολής', 'Waiting deposit', 40, 1, 0),
('waiting_materials', 'Αναμονή υλικών', 'Waiting materials', 50, 1, 0),
('scheduled', 'Σε προγραμματισμό', 'Scheduled', 60, 1, 0),
('in_progress', 'Σε εξέλιξη', 'In progress', 70, 1, 0),
('on_hold', 'Σε αναμονή', 'On hold', 80, 1, 0),
('pending_close', 'Προς ολοκλήρωση', 'Pending close', 90, 1, 0),
('completed', 'Ολοκληρωμένο', 'Completed', 100, 1, 0),
('cancelled', 'Ακυρωμένο', 'Cancelled', 110, 1, 0)
ON DUPLICATE KEY UPDATE
label_el = VALUES(label_el),
label_en = VALUES(label_en),
sort_order = VALUES(sort_order),
is_active = VALUES(is_active);
