-- PM app schema
-- Run once against your MySQL database to set up tables.

CREATE TABLE IF NOT EXISTS projects (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT NULL,
    color VARCHAR(7) NOT NULL DEFAULT '#378ADD',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS items (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    parent_id INT UNSIGNED NULL,
    title VARCHAR(255) NOT NULL,
    notes TEXT NULL,
    is_complete TINYINT(1) NOT NULL DEFAULT 0,
    status VARCHAR(20) NULL,              -- new / upcoming / in_progress / completed / on_hold / canceled (board columns; only set on top-level items)
    impact TINYINT UNSIGNED NULL,         -- 1-5 star rating, top-level items only
    effort TINYINT UNSIGNED NULL,         -- 1-5 star rating, top-level items only
    start_date DATE NULL,
    due_date DATE NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Optional: for gantt dependency arrows later. Not used yet.
CREATE TABLE IF NOT EXISTS item_dependencies (
    item_id INT UNSIGNED NOT NULL,
    depends_on_item_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (item_id, depends_on_item_id),
    FOREIGN KEY (item_id) REFERENCES items(id) ON DELETE CASCADE,
    FOREIGN KEY (depends_on_item_id) REFERENCES items(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Sample data matching the demo, so the first page has something to show
INSERT INTO projects (id, name, description, color) VALUES
    (1, 'Warehouse relocation', 'Move production floor to the new site', '#378ADD'),
    (2, 'Mums potential aged care facility', NULL, '#D4537E')
ON DUPLICATE KEY UPDATE name = VALUES(name);

INSERT INTO items (id, project_id, parent_id, title, is_complete, status, impact, effort, start_date, due_date, sort_order) VALUES
    (1, 1, NULL, 'Confirm new site lease', 1, 'completed', 5, 4, '2026-08-20', NULL, 1),
    (2, 1, NULL, 'Book removalist', 1, 'completed', 3, 2, '2026-08-22', NULL, 2),
    (21, 1, 2, 'Get 3 quotes', 1, NULL, NULL, NULL, '2026-08-22', '2026-08-25', 1),
    (22, 1, 2, 'Confirm booking date', 1, NULL, NULL, NULL, '2026-08-26', '2026-08-27', 2),
    (3, 1, NULL, 'Relabel bin locations', 0, 'in_progress', 2, 3, '2026-09-01', NULL, 3),
    (31, 1, 3, 'Print new bin labels', 1, NULL, NULL, NULL, '2026-09-01', '2026-09-01', 1),
    (32, 1, 3, 'Apply labels to shelving', 0, NULL, NULL, NULL, '2026-09-02', '2026-09-03', 2),
    (33, 1, 3, 'Update warehouse map doc', 0, NULL, NULL, NULL, '2026-09-04', '2026-09-04', 3),
    (4, 1, NULL, 'Migrate embroidery machines', 0, 'new', 5, 5, '2026-09-06', NULL, 4),
    (41, 1, 4, 'Disconnect and crate machines', 0, NULL, NULL, NULL, '2026-09-06', '2026-09-06', 1),
    (42, 1, 4, 'Transport to new site', 0, NULL, NULL, NULL, '2026-09-07', '2026-09-07', 2),
    (43, 1, 4, 'Recalibrate on arrival', 0, NULL, NULL, NULL, '2026-09-08', '2026-09-08', 3),
    (5, 1, NULL, 'Update supplier delivery address', 0, 'on_hold', 2, 1, '2026-09-04', '2026-09-05', 5),

    (6, 2, NULL, 'Call centrelink', 0, 'new', 4, 4, '2026-08-28', '2026-09-01', 1),
    (7, 2, NULL, 'Contact financial advisor', 0, 'new', NULL, NULL, '2026-08-28', '2026-09-01', 2),
    (8, 2, NULL, 'Notate mums income streams', 0, 'new', NULL, NULL, '2026-08-28', '2026-09-01', 3),
    (9, 2, NULL, 'Contact 6 care facilities for a tour', 0, 'new', NULL, NULL, '2026-08-28', '2026-09-01', 4)
ON DUPLICATE KEY UPDATE title = VALUES(title);