-- Wood Cutting List Module - SQLite Version
-- Single tenant system
-- Money values are stored in integer pence
-- Dimensions are stored in millimetres
-- Area values are stored in square metres where noted
--
-- Notes:
-- SQLite does not enforce ENUM types directly, so CHECK constraints are used.
-- Foreign keys require: PRAGMA foreign_keys = ON;
-- Product and user tables are assumed to exist in the test database.

PRAGMA foreign_keys = ON;

-- =========================================================
-- Cutting List Header
-- =========================================================
CREATE TABLE IF NOT EXISTS wood_cutting_lists (
    id                          INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id                     INTEGER NOT NULL,
    root_cutting_list_id        INTEGER NULL,
    previous_cutting_list_id    INTEGER NULL,
    version_number              INTEGER NOT NULL DEFAULT 1,
    title                       TEXT NOT NULL,
    list_status                 TEXT NOT NULL DEFAULT 'saved' CHECK (list_status IN ('saved', 'converted', 'archived', 'deleted')),
    offcut_required             INTEGER NOT NULL DEFAULT 0 CHECK (offcut_required IN (0, 1)),
    notes                       TEXT NULL,
    converted_order_id          INTEGER NULL,
    is_deleted                  INTEGER NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)),
    created_at                  TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at                  TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES user_login(pk_user_id),
    FOREIGN KEY (root_cutting_list_id) REFERENCES wood_cutting_lists(id),
    FOREIGN KEY (previous_cutting_list_id) REFERENCES wood_cutting_lists(id)
);

-- =========================================================
-- Cutting List Parts
-- Stores requested finished parts.
-- Product/dimension/pricing values are live references.
-- Static price/thickness/dimension snapshots belong in the order module.
--
-- Edge banding IDs reference the existing edge banding table.
-- Add FK constraints for edgeband_*_id once the exact table name is confirmed.
-- =========================================================
CREATE TABLE IF NOT EXISTS wood_cutting_list_parts (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    cutting_list_id         INTEGER NOT NULL,
    product_id              INTEGER NOT NULL,
    product_dimensions_id   INTEGER NOT NULL,
    product_pricing_id      INTEGER NOT NULL,
    thickness_qty           INTEGER NOT NULL DEFAULT 1, 
    thickness_mm            INTEGER NOT NULL,
    length_mm               INTEGER NOT NULL,
    width_mm                INTEGER NOT NULL,
    quantity                INTEGER NOT NULL DEFAULT 1,
    part_description        TEXT NULL,
    edgeband_l1_id          INTEGER NULL,
    edgeband_l2_id          INTEGER NULL,
    edgeband_w1_id          INTEGER NULL,
    edgeband_w2_id          INTEGER NULL,
    corner_type             TEXT NULL CHECK (corner_type IS NULL OR corner_type IN ('square', 'radius')) DEFAULT 'square',
    grain_match             INTEGER NOT NULL DEFAULT 0 CHECK (grain_match IN (0, 1)),
    grain_match_group       TEXT NULL,
    allow_rotation          INTEGER NOT NULL DEFAULT 0 CHECK (allow_rotation IN (0, 1)),
    grain_direction         TEXT NULL CHECK (grain_direction IS NULL OR grain_direction IN ('none', 'length', 'width')),
    row_order               INTEGER NOT NULL DEFAULT 0,
    is_deleted              INTEGER NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)),
    created_at              TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at              TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cutting_list_id) REFERENCES wood_cutting_lists(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES wood_product(id),
    FOREIGN KEY (product_dimensions_id) REFERENCES wood_product_dimensions(id),
    FOREIGN KEY (product_pricing_id) REFERENCES wood_product_pricing(id)
);

-- =========================================================
-- Cutting List Layouts
-- Stores conservative internal layout estimates.
-- The third-party cutting software generates the final optimised output.
-- =========================================================
CREATE TABLE IF NOT EXISTS wood_cutting_list_layouts (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    cutting_list_id         INTEGER NOT NULL,
    layout_status           TEXT NOT NULL DEFAULT 'draft' CHECK (layout_status IN ('draft', 'calculated', 'failed', 'accepted', 'expired')),
    optimisation_method     TEXT NULL,
    sheet_count             INTEGER NOT NULL DEFAULT 0,
    total_area_mm2          INTEGER NULL,
    used_area_mm2           INTEGER NULL,
    waste_area_mm2          INTEGER NULL,
    waste_percent           REAL NULL,
    material_cost_pence     INTEGER NOT NULL DEFAULT 0,
    edging_cost_pence       INTEGER NOT NULL DEFAULT 0,
    glue_cost_pence         INTEGER NOT NULL DEFAULT 0,
    cutting_cost_pence      INTEGER NOT NULL DEFAULT 0,
    waste_cost_pence        INTEGER NOT NULL DEFAULT 0,
    total_cost_pence        INTEGER NOT NULL DEFAULT 0,
    is_deleted              INTEGER NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)),
    created_at              TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at              TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (cutting_list_id) REFERENCES wood_cutting_lists(id) ON DELETE CASCADE
);

-- =========================================================
-- Cutting List Layout Sheets
-- Represents each board/panel used in the internal layout estimate.
-- source_* fields are based on live product data at calculation time.
-- usable_* fields account for board edge trim.
-- =========================================================
CREATE TABLE IF NOT EXISTS wood_cutting_list_layout_sheets (
    id                          INTEGER PRIMARY KEY AUTOINCREMENT,
    layout_id                   INTEGER NOT NULL,
    product_id                  INTEGER NOT NULL,
    product_dimensions_id       INTEGER NOT NULL,
    product_pricing_id          INTEGER NOT NULL,
    sheet_number                INTEGER NOT NULL,
    source_sheet_length_mm      INTEGER NOT NULL,
    source_sheet_width_mm       INTEGER NOT NULL,
    source_sheet_thickness_mm   INTEGER NOT NULL,
    lamination_layers           INTEGER NOT NULL DEFAULT 1,
    final_thickness_mm          INTEGER NOT NULL,
    usable_sheet_length_mm      INTEGER NOT NULL,
    usable_sheet_width_mm       INTEGER NOT NULL,
    board_edge_trim_mm          INTEGER NOT NULL DEFAULT 0,
    is_deleted                  INTEGER NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)),
    created_at                  TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at                  TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (layout_id) REFERENCES wood_cutting_list_layouts(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES wood_products(id),
    FOREIGN KEY (product_dimensions_id) REFERENCES wood_product_dimensions(id),
    FOREIGN KEY (product_pricing_id) REFERENCES wood_product_pricing(id)
);

-- =========================================================
-- Cutting List Layout Sheet Parts
-- Stores where each part instance is placed on an internal layout sheet.
-- instance_number supports parts where quantity > 1.
-- =========================================================
CREATE TABLE IF NOT EXISTS wood_cutting_list_layout_sheet_parts (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    layout_sheet_id         INTEGER NOT NULL,
    cutting_list_part_id    INTEGER NOT NULL,
    instance_number         INTEGER NOT NULL DEFAULT 1,
    x_mm                    INTEGER NOT NULL,
    y_mm                    INTEGER NOT NULL,
    placed_length_mm        INTEGER NOT NULL,
    placed_width_mm         INTEGER NOT NULL,
    rotated                 INTEGER NOT NULL DEFAULT 0 CHECK (rotated IN (0, 1)),
    is_deleted              INTEGER NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)),
    created_at              TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at              TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (layout_sheet_id) REFERENCES wood_cutting_list_layout_sheets(id) ON DELETE CASCADE,
    FOREIGN KEY (cutting_list_part_id) REFERENCES wood_cutting_list_parts(id) ON DELETE CASCADE
);

-- =========================================================
-- Cutting List Layout Cuts
-- Stores internal straight full-length/full-width cut sequence estimates.
-- This is not the final manufacturing plan.
-- The external cutting software produces the final optimised cutting output.
-- =========================================================
CREATE TABLE IF NOT EXISTS wood_cutting_list_layout_cuts (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    layout_sheet_id     INTEGER NOT NULL,
    cut_order           INTEGER NOT NULL,
    cut_type            TEXT NOT NULL CHECK (cut_type IN ('edge_trim', 'part_cut', 'waste_cut')),
    cut_direction       TEXT NOT NULL CHECK (cut_direction IN ('vertical', 'horizontal')),
    cut_position_mm     INTEGER  NOT NULL,
    cut_length_mm       INTEGER NOT NULL,
    blade_width_mm      INTEGER NOT NULL DEFAULT 0,
    source_x_mm         INTEGER NOT NULL,
    source_y_mm         INTEGER NOT NULL,
    source_length_mm    INTEGER NOT NULL,
    source_width_mm     INTEGER NOT NULL,
    result_a_length_mm  INTEGER NULL,
    result_a_width_mm   INTEGER NULL,
    result_b_length_mm  INTEGER NULL,
    result_b_width_mm   INTEGER NULL,
    is_deleted          INTEGER NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)),
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (layout_sheet_id) REFERENCES wood_cutting_list_layout_sheets(id) ON DELETE CASCADE
);

-- =========================================================
-- Cutting List Layout Laminations
-- Stores lamination/glue data where boards are laminated to increase thickness.
-- Lamination increases thickness only. It does not increase length or width.
-- Glue is charged from full source board area, rounded up to nearest m2.
-- =========================================================
CREATE TABLE IF NOT EXISTS wood_cutting_list_layout_laminations (
    id                              INTEGER PRIMARY KEY AUTOINCREMENT,
    layout_sheet_id                 INTEGER NOT NULL,
    source_product_id               INTEGER NOT NULL,
    source_product_dimensions_id    INTEGER NOT NULL,
    source_product_pricing_id       INTEGER NOT NULL,
    lamination_layers               INTEGER NOT NULL DEFAULT 1,
    glue_layers                     INTEGER NOT NULL DEFAULT 0,
    source_thickness_mm             INTEGER NOT NULL,
    final_thickness_mm              INTEGER NOT NULL,
    source_board_area_m2            INTEGER NOT NULL,
    chargeable_glue_area_m2         INTEGER NOT NULL DEFAULT 0,
    glue_cost_per_m2_pence          INTEGER NOT NULL DEFAULT 0,
    total_glue_cost_pence           INTEGER NOT NULL DEFAULT 0,
    notes                           TEXT NULL,
    is_deleted                      INTEGER NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)),
    created_at                      TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at                      TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (layout_sheet_id) REFERENCES wood_cutting_list_layout_sheets(id) ON DELETE CASCADE,
    FOREIGN KEY (source_product_id) REFERENCES wood_products(id),
    FOREIGN KEY (source_product_dimensions_id) REFERENCES wood_product_dimensions(id),
    FOREIGN KEY (source_product_pricing_id) REFERENCES wood_product_pricing(id)
);

CREATE TABLE wood_cutting_history (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    table_id  INTEGER NOT NULL,
    change_type TEXT NOT NULL CHECK (change_type IN ('create', 'update', 'delete', 'refund')),
    change_data TEXT NOT NULL,
    table_of_source TEXT NOT NULL, -- Name of the table where the change occurred
    changed_by INTEGER NOT NULL, -- User ID of the person who made the change
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- =========================================================
-- Cutting List Settings
-- Global settings for this single tenant module.
-- setting_value is stored as text and cast by PHP based on setting_type.
-- =========================================================
CREATE TABLE IF NOT EXISTS wood_cutting_list_settings (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    setting_key             TEXT NOT NULL UNIQUE,
    setting_value           TEXT NOT NULL,
    setting_type            TEXT NOT NULL DEFAULT 'string',
    unit                    TEXT NULL,
    setting_description     TEXT NULL,
    active                  INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1)),
    is_deleted              INTEGER NOT NULL DEFAULT 0 CHECK (is_deleted IN (0, 1)),
    created_at              TEXT DEFAULT CURRENT_TIMESTAMP,
    updated_at              TEXT DEFAULT CURRENT_TIMESTAMP
);

-- =========================================================
-- Seed Cutting List Settings
-- Uses INSERT OR IGNORE so the script can be run more than once.
-- =========================================================
INSERT OR IGNORE INTO wood_cutting_list_settings
(
    setting_key,
    setting_value,
    setting_type,
    unit,
    setting_description
)
VALUES
('saw_blade_width_mm', '3', 'decimal', 'mm', 'Width lost per cut'),
('board_edge_trim_mm', '5', 'decimal', 'mm', 'Amount trimmed from each outside edge for clean, straight, flat edges'),
('max_lamination_layers', '3', 'integer', NULL, 'Maximum number of boards that can be laminated together'),
('glue_cost_per_m2_pence', '450', 'integer', 'pence/m2', 'Glue cost per square metre of full source board area per glue layer'),
('round_glue_area_to_nearest_m2', '1', 'boolean', NULL, 'Whether glue chargeable area should round up to nearest full square metre'),
('round_banding_to_nearest_m_per_edge', '1', 'boolean', NULL, 'Whether banding should round up to nearest metre per selected edge');
-- Rules 
--    Minumin Delivery Fee
--    Price per mile per mile
--    Truck copacity in kg 
--    Number of Trucks
--    Maximun distance 
--    London Surgarge
--    Congestion Charge (london)


-- =========================================================
-- Optional Later Table: Cutting List Notifications
-- For v1, validation notifications can be returned in API responses only.
-- Uncomment/create later if notifications need to be stored, dismissed, or audited.
-- =========================================================
-- CREATE TABLE IF NOT EXISTS wood_cutting_list_notifications (
--     id INTEGER PRIMARY KEY AUTOINCREMENT,
--
--     cutting_list_id INTEGER NOT NULL,
--     cutting_list_part_id INTEGER NULL,
--     layout_id INTEGER NULL,
--
--     notification_level TEXT NOT NULL
--         CHECK (notification_level IN ('error', 'warning', 'info')),
--     notification_code TEXT NOT NULL,
--     notification_message TEXT NOT NULL,
--
--     dismissed INTEGER NOT NULL DEFAULT 0
--         CHECK (dismissed IN (0, 1)),
--     dismissed_at TEXT NULL,
--
--     created_at TEXT DEFAULT CURRENT_TIMESTAMP,
--
--     FOREIGN KEY (cutting_list_id) REFERENCES wood_cutting_lists(id) ON DELETE CASCADE,
--     FOREIGN KEY (cutting_list_part_id) REFERENCES wood_cutting_list_parts(id) ON DELETE CASCADE,
--     FOREIGN KEY (layout_id) REFERENCES wood_cutting_list_layouts(id) ON DELETE CASCADE
-- );

-- =========================================================
-- Optional Later Table: Cutting List Export Jobs
-- For tracking exports to the external cutting software.
-- =========================================================
-- CREATE TABLE IF NOT EXISTS wood_cutting_list_export_jobs (
--     id INTEGER PRIMARY KEY AUTOINCREMENT,
--
--     cutting_list_id INTEGER NOT NULL,
--     layout_id INTEGER NULL,
--
--     export_status TEXT NOT NULL DEFAULT 'pending'
--         CHECK (export_status IN ('pending', 'sent', 'failed', 'completed')),
--     export_payload TEXT NULL,
--     external_reference TEXT NULL,
--
--     created_at TEXT DEFAULT CURRENT_TIMESTAMP,
--     updated_at TEXT DEFAULT CURRENT_TIMESTAMP,
--
--     FOREIGN KEY (cutting_list_id) REFERENCES wood_cutting_lists(id) ON DELETE CASCADE,
--     FOREIGN KEY (layout_id) REFERENCES wood_cutting_list_layouts(id) ON DELETE SET NULL
-- );

-- =========================================================
-- Indexes
-- =========================================================
CREATE INDEX IF NOT EXISTS idx_wcl_user_status_deleted
ON wood_cutting_lists (user_id, list_status, is_deleted);

CREATE INDEX IF NOT EXISTS idx_wcl_root
ON wood_cutting_lists (root_cutting_list_id);

CREATE INDEX IF NOT EXISTS idx_wcl_previous
ON wood_cutting_lists (previous_cutting_list_id);

CREATE INDEX IF NOT EXISTS idx_wcl_parts_list
ON wood_cutting_list_parts (cutting_list_id);

CREATE INDEX IF NOT EXISTS idx_wcl_parts_product_refs
ON wood_cutting_list_parts (product_id, product_dimensions_id, product_pricing_id);

CREATE INDEX IF NOT EXISTS idx_wcl_layouts_list_status
ON wood_cutting_list_layouts (cutting_list_id, layout_status);

CREATE INDEX IF NOT EXISTS idx_wcl_layout_sheets_layout
ON wood_cutting_list_layout_sheets (layout_id);

CREATE INDEX IF NOT EXISTS idx_wcl_layout_sheet_parts_sheet
ON wood_cutting_list_layout_sheet_parts (layout_sheet_id);

CREATE INDEX IF NOT EXISTS idx_wcl_layout_sheet_parts_part
ON wood_cutting_list_layout_sheet_parts (cutting_list_part_id);

CREATE INDEX IF NOT EXISTS idx_wcl_layout_cuts_sheet
ON wood_cutting_list_layout_cuts (layout_sheet_id);

CREATE INDEX IF NOT EXISTS idx_wcl_laminations_sheet
ON wood_cutting_list_layout_laminations (layout_sheet_id);

CREATE INDEX IF NOT EXISTS idx_wcl_settings_key_active
ON wood_cutting_list_settings (setting_key, active);

-- =========================================================
-- SQLite updated_at triggers
-- SQLite has no ON UPDATE CURRENT_TIMESTAMP, so triggers are used.
-- =========================================================
CREATE TRIGGER IF NOT EXISTS trg_wcl_updated_at
AFTER UPDATE ON wood_cutting_lists
FOR EACH ROW
WHEN NEW.updated_at = OLD.updated_at
BEGIN
    UPDATE wood_cutting_lists
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END;

CREATE TRIGGER IF NOT EXISTS trg_wcl_parts_updated_at
AFTER UPDATE ON wood_cutting_list_parts
FOR EACH ROW
WHEN NEW.updated_at = OLD.updated_at
BEGIN
    UPDATE wood_cutting_list_parts
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END;

CREATE TRIGGER IF NOT EXISTS trg_wcl_layouts_updated_at
AFTER UPDATE ON wood_cutting_list_layouts
FOR EACH ROW
WHEN NEW.updated_at = OLD.updated_at
BEGIN
    UPDATE wood_cutting_list_layouts
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END;

CREATE TRIGGER IF NOT EXISTS trg_wcl_settings_updated_at
AFTER UPDATE ON wood_cutting_list_settings
FOR EACH ROW
WHEN NEW.updated_at = OLD.updated_at
BEGIN
    UPDATE wood_cutting_list_settings
    SET updated_at = CURRENT_TIMESTAMP
    WHERE id = OLD.id;
END;


CREATE TRIGGER trg_wood_cutting_lists_soft_delete_parts
AFTER UPDATE OF is_deleted ON wood_cutting_lists
FOR EACH ROW
WHEN OLD.is_deleted = 0 AND NEW.is_deleted = 1
BEGIN
    UPDATE wood_cutting_list_parts
    SET
        is_deleted = 1,
        updated_at = CURRENT_TIMESTAMP
    WHERE cutting_list_id = NEW.id
      AND is_deleted = 0;
END;