schema.sql - master - unTonee/LekalaOrderManager

schema.sql
993 строки35 KB
Anton Kostrubin aka unTonee
fix: удаление дублей из БД + защита seed/reset от повторного запуска + уникальные индексы
29 июн 2026, 15:52
29 июн 2026, 15:5251bca39
-- ============================================================
-- СИСТЕМА ЗАКАЗОВ — SQL СХЕМА (SQLite ≥ 3.53)
-- Логика «Корзины»: ничего не удаляется физически
-- ============================================================

-- ============================================================
-- 0. НАСТРОЙКИ SQLite
-- ============================================================
PRAGMA recursive_triggers = ON;

-- ============================================================
-- 0.1 ТАБЛИЦА СЕССИИ (temp)
-- Приложение УСТАНАВЛИВАЕТ user_id перед любым запросом:
--   INSERT INTO temp.user_session SELECT 1;
-- ============================================================
CREATE TEMP TABLE IF NOT EXISTS user_session (
  user_id INTEGER NOT NULL
);

-- Вспомогательная функция получения текущего user_id
-- Используется в триггерах/представлениях как:
--   (SELECT user_id FROM temp.user_session)

-- ============================================================
-- 1. СПРАВОЧНИКИ
-- ============================================================

-- 1.1 ПОЛЬЗОВАТЕЛИ
CREATE TABLE IF NOT EXISTS users (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  username        TEXT    UNIQUE NOT NULL,
  password_hash   TEXT    NOT NULL,
  fullname        TEXT,
  email           TEXT,
  phone           TEXT,
  role            TEXT    DEFAULT 'manager'
                    CHECK(role IN ('manager','admin','supervisor')),
  is_active       INTEGER DEFAULT 1,
  is_deletable    INTEGER DEFAULT 1,
  parent_id       INTEGER REFERENCES users(id),
  to_recycle      INTEGER DEFAULT 0,
  created_at      DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- 1.2 НОМЕНКЛАТУРА (иерархический справочник: категории + элементы)
CREATE TABLE IF NOT EXISTS nomenclature (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT    NOT NULL,
  price         REAL    DEFAULT 0
                    CHECK(price >= 0),
  is_category   INTEGER DEFAULT 0,
  is_deletable  INTEGER DEFAULT 1,
  parent_id     INTEGER REFERENCES nomenclature(id),
  to_recycle    INTEGER DEFAULT 0
);

-- 1.3 ПРЕДПОЧТЕНИЯ (иерархический справочник настроек)
CREATE TABLE IF NOT EXISTS preferences (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT    NOT NULL,
  value         REAL,
  is_deletable  INTEGER DEFAULT 1,
  parent_id     INTEGER REFERENCES preferences(id),
  to_recycle    INTEGER DEFAULT 0
);

-- 1.4 КЛИЕНТЫ
CREATE TABLE IF NOT EXISTS clients (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  name            TEXT    NOT NULL,
  inn             TEXT,
  phone           TEXT,
  email           TEXT,
  address         TEXT,
  telegram        TEXT,
  max_account     TEXT,
  comment         TEXT,
  contact_person  TEXT,
  is_deletable    INTEGER DEFAULT 1,
  parent_id       INTEGER REFERENCES clients(id),
  to_recycle      INTEGER DEFAULT 0
);

-- 1.5 СТАТУСЫ ЗАКАЗОВ
CREATE TABLE IF NOT EXISTS order_statuses (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT    NOT NULL,
  color         TEXT    DEFAULT 'gray',
  sort_order    INTEGER DEFAULT 0,
  is_active     INTEGER DEFAULT 1,
  is_deletable  INTEGER DEFAULT 1,
  parent_id     INTEGER REFERENCES order_statuses(id),
  to_recycle    INTEGER DEFAULT 0
);

-- 1.6 ВАРИАНТЫ ДОСТАВКИ
CREATE TABLE IF NOT EXISTS delivery_list (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT    NOT NULL,
  is_deletable  INTEGER DEFAULT 1,
  to_recycle    INTEGER DEFAULT 0
);

-- 1.7 СКИДКИ
CREATE TABLE IF NOT EXISTS discount (
  id                    INTEGER PRIMARY KEY AUTOINCREMENT,
  name                  TEXT    NOT NULL,
  discount_amount       REAL    DEFAULT 0
                          CHECK(discount_amount >= 0),
  discount_is_percent   INTEGER DEFAULT 0,
  is_deletable          INTEGER DEFAULT 1,
  to_recycle            INTEGER DEFAULT 0
);

-- 1.8 ТИПЫ ОПЛАТЫ
CREATE TABLE IF NOT EXISTS pay_type (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  name          TEXT    NOT NULL,
  is_deletable  INTEGER DEFAULT 1,
  to_recycle    INTEGER DEFAULT 0
);

-- 1.9 ПЛАТЕЖИ
CREATE TABLE IF NOT EXISTS payments (
  id              INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id        INTEGER NOT NULL REFERENCES orders(id),
  pay_type_id     INTEGER NOT NULL REFERENCES pay_type(id),
  amount          REAL    NOT NULL CHECK(amount > 0),
  payment_date    DATETIME DEFAULT CURRENT_TIMESTAMP,
  comment         TEXT,
  to_recycle      INTEGER DEFAULT 0
);

-- ============================================================
-- 2. ДОКУМЕНТЫ
-- ============================================================

-- 2.1 ЗАКАЗ ПОКУПАТЕЛЯ
CREATE TABLE IF NOT EXISTS orders (
  id                  INTEGER PRIMARY KEY AUTOINCREMENT,
  created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,
  client_id           INTEGER REFERENCES clients(id),
  delivery_list_id    INTEGER REFERENCES delivery_list(id),
  delivery_detail     TEXT,
  delivery_price      REAL    DEFAULT 0
                        CHECK(delivery_price >= 0),
  status_id           INTEGER REFERENCES order_statuses(id),
  discount_id         INTEGER REFERENCES discount(id),
  discount_amount     REAL    DEFAULT 0
                        CHECK(discount_amount >= 0),
  discount_is_percent INTEGER DEFAULT 0,
  author              INTEGER REFERENCES users(id),
  note                TEXT,
  asap                INTEGER DEFAULT 0,
  asap_percent        REAL    DEFAULT 1.0
                        CHECK(asap_percent >= 1.0),
  to_recycle          INTEGER DEFAULT 0,
  -- хранение вычисляемых итогов
  items_subtotal      REAL    DEFAULT 0,
  discount_value      REAL    DEFAULT 0,
  total_sum           REAL    DEFAULT 0,
  -- снапшоты для печати
  client_name_snapshot        TEXT,
  delivery_name_snapshot      TEXT,
  discount_name_snapshot      TEXT,
  status_name_snapshot        TEXT,
  -- черновик и блокировка
  is_draft          INTEGER DEFAULT 0,
  locked_by         INTEGER,
  locked_at         DATETIME
);

-- 2.2 СТРОКИ ЗАКАЗА
CREATE TABLE IF NOT EXISTS order_items (
  id                          INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id                    INTEGER NOT NULL REFERENCES orders(id),
  line_number                 INTEGER NOT NULL,
  -- лекало
  pattern_name                TEXT,
  pattern_width               NUMERIC CHECK(pattern_width >= 0),
  pattern_height              NUMERIC CHECK(pattern_height >= 0),
  pattern_server_name         TEXT,
  -- материал / услуга
  material_id                 INTEGER REFERENCES nomenclature(id),
  material_name_snapshot      TEXT,
  price_per_sqm               REAL    DEFAULT 0 CHECK(price_per_sqm >= 0),
  quantity                    REAL    DEFAULT 1 CHECK(quantity >= 0),
  -- корректировка файла
  has_adjustment              INTEGER DEFAULT 0,
  adjustment_service_id       INTEGER REFERENCES nomenclature(id),
  adjustment_service_name_snapshot TEXT,
  adjustment_qty              REAL    DEFAULT 0 CHECK(adjustment_qty >= 0),
  adjustment_price            REAL    DEFAULT 0 CHECK(adjustment_price >= 0),
  -- склейка
  has_gluing                  INTEGER DEFAULT 0,
  gluing_service_id           INTEGER REFERENCES nomenclature(id),
  gluing_service_name_snapshot TEXT,
  gluing_qty                  REAL    DEFAULT 0 CHECK(gluing_qty >= 0),
  gluing_price                REAL    DEFAULT 0 CHECK(gluing_price >= 0),
  -- итоги строки
  line_total                  REAL    DEFAULT 0
                              CHECK(line_total >= 0),
  UNIQUE(order_id, line_number)
);

-- 2.3 ИСТОРИЯ СТАТУСОВ
CREATE TABLE IF NOT EXISTS status_history (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id    INTEGER NOT NULL REFERENCES orders(id),
  status_id   INTEGER NOT NULL REFERENCES order_statuses(id),
  changed_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  changed_by  INTEGER REFERENCES users(id)
);

-- 2.4 КОММЕНТАРИИ К ЗАКАЗУ
CREATE TABLE IF NOT EXISTS order_comments (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  order_id    INTEGER NOT NULL REFERENCES orders(id),
  user_id     INTEGER NOT NULL REFERENCES users(id),
  text        TEXT    NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- 3. ЛОГ ДЕЙСТВИЙ ПОЛЬЗОВАТЕЛЕЙ
-- ============================================================
CREATE TABLE IF NOT EXISTS user_activity_log (
  id          INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id     INTEGER NOT NULL,
  action      TEXT    NOT NULL,
  table_name  TEXT    NOT NULL,
  record_id   INTEGER,
  old_data    TEXT,
  new_data    TEXT,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================
-- 4. ИНДЕКСЫ
-- ============================================================
CREATE INDEX IF NOT EXISTS idx_orders_client       ON orders(client_id);
CREATE INDEX IF NOT EXISTS idx_orders_status       ON orders(status_id);
CREATE INDEX IF NOT EXISTS idx_orders_author       ON orders(author);
CREATE INDEX IF NOT EXISTS idx_orders_delivery     ON orders(delivery_list_id);
CREATE INDEX IF NOT EXISTS idx_orders_discount     ON orders(discount_id);
CREATE INDEX IF NOT EXISTS idx_order_items_order   ON order_items(order_id);
CREATE INDEX IF NOT EXISTS idx_order_items_material ON order_items(material_id);
CREATE INDEX IF NOT EXISTS idx_status_history_order ON status_history(order_id);
CREATE INDEX IF NOT EXISTS idx_order_comments_order ON order_comments(order_id);
CREATE INDEX IF NOT EXISTS idx_nomenclature_parent  ON nomenclature(parent_id);
CREATE INDEX IF NOT EXISTS idx_clients_parent       ON clients(parent_id);
CREATE INDEX IF NOT EXISTS idx_users_parent         ON users(parent_id);
CREATE INDEX IF NOT EXISTS idx_log_user_time        ON user_activity_log(user_id, created_at);
CREATE INDEX IF NOT EXISTS idx_payments_order       ON payments(order_id);
CREATE INDEX IF NOT EXISTS idx_payments_pay_type    ON payments(pay_type_id);

-- Защита от дублей в справочниках
CREATE UNIQUE INDEX IF NOT EXISTS idx_nomenclature_name_active ON nomenclature(name) WHERE to_recycle = 0;
CREATE UNIQUE INDEX IF NOT EXISTS idx_clients_name_active ON clients(name) WHERE to_recycle = 0;

-- ============================================================
-- 5. ПРЕДСТАВЛЕНИЯ (VIEW)
-- ============================================================

-- 5.1 Активные пользователи
-- Фильтрация untonee (id=0) от admin (id=1) выполняется в приложении
CREATE VIEW IF NOT EXISTS v_users AS
SELECT *
FROM users
WHERE to_recycle = 0;

-- 5.2 Активная номенклатура
CREATE VIEW IF NOT EXISTS v_nomenclature AS
SELECT *
FROM nomenclature
WHERE to_recycle = 0;

-- 5.3 Активные клиенты
CREATE VIEW IF NOT EXISTS v_clients AS
SELECT *
FROM clients
WHERE to_recycle = 0;

-- 5.4 Активные статусы заказов
CREATE VIEW IF NOT EXISTS v_order_statuses AS
SELECT *
FROM order_statuses
WHERE to_recycle = 0 AND is_active = 1
ORDER BY sort_order;

-- 5.5 Варианты доставки
CREATE VIEW IF NOT EXISTS v_delivery_list AS
SELECT *
FROM delivery_list
WHERE to_recycle = 0;

-- 5.6 Скидки
CREATE VIEW IF NOT EXISTS v_discount AS
SELECT *
FROM discount
WHERE to_recycle = 0;

-- 5.8 Типы оплаты
CREATE VIEW IF NOT EXISTS v_pay_type AS
SELECT *
FROM pay_type
WHERE to_recycle = 0;

-- 5.9 Платежи
CREATE VIEW IF NOT EXISTS v_payments AS
SELECT *
FROM payments
WHERE to_recycle = 0;

-- 5.7 Вставка строк заказа через VIEW
-- Приложение пишет сюда, триггер подставляет ID, цены, снапшоты, считает итог
CREATE VIEW IF NOT EXISTS v_order_items AS
SELECT
  id,
  order_id,
  line_number,
  pattern_name,
  pattern_width,
  pattern_height,
  pattern_server_name,
  material_id,
  material_name_snapshot,
  price_per_sqm,
  quantity,
  has_adjustment,
  adjustment_service_id,
  adjustment_service_name_snapshot,
  adjustment_qty,
  adjustment_price,
  has_gluing,
  gluing_service_id,
  gluing_service_name_snapshot,
  gluing_qty,
  gluing_price,
  line_total
FROM order_items;

-- 5.8 Вставка заказов через VIEW
CREATE VIEW IF NOT EXISTS v_orders AS
SELECT
  id,
  created_at,
  client_id,
  delivery_list_id,
  delivery_detail,
  delivery_price,
  status_id,
  discount_id,
  discount_amount,
  discount_is_percent,
  author,
  note,
  asap,
  asap_percent,
  to_recycle,
  items_subtotal,
  discount_value,
  total_sum,
  client_name_snapshot,
  delivery_name_snapshot,
  discount_name_snapshot,
  status_name_snapshot,
  is_draft,
  locked_by,
  locked_at
FROM orders;

-- ============================================================
-- 6. ТРИГГЕРЫ
-- ============================================================

-- ============================================================
-- 6.1 ЗАЩИТА ОТ ФИЗИЧЕСКОГО УДАЛЕНИЯ (логика «Корзины»)
-- ============================================================

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_users
BEFORE DELETE ON users
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_nomenclature
BEFORE DELETE ON nomenclature
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_preferences
BEFORE DELETE ON preferences
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_clients
BEFORE DELETE ON clients
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_order_statuses
BEFORE DELETE ON order_statuses
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_delivery_list
BEFORE DELETE ON delivery_list
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_discount
BEFORE DELETE ON discount
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_orders
BEFORE DELETE ON orders
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_pay_type
BEFORE DELETE ON pay_type
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_payments
BEFORE DELETE ON payments
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

CREATE TRIGGER IF NOT EXISTS trg_protect_delete_order_comments
BEFORE DELETE ON order_comments
BEGIN
  SELECT RAISE(ABORT, 'Физическое удаление запрещено. Используйте to_recycle.');
END;

-- ============================================================
-- 6.2 ЗАПРЕТ УДАЛЕНИЯ НЕУДАЛЯЕМЫХ ЗАПИСЕЙ (is_deletable = 0)
-- ============================================================

CREATE TRIGGER IF NOT EXISTS trg_block_delete_users_nodelete
BEFORE UPDATE OF to_recycle ON users
WHEN NEW.to_recycle = 1 AND OLD.is_deletable = 0
BEGIN
  SELECT RAISE(ABORT, 'Нельзя удалить системную запись users.');
END;

CREATE TRIGGER IF NOT EXISTS trg_block_delete_nomenclature_nodelete
BEFORE UPDATE OF to_recycle ON nomenclature
WHEN NEW.to_recycle = 1 AND OLD.is_deletable = 0
BEGIN
  SELECT RAISE(ABORT, 'Нельзя удалить системную запись nomenclature.');
END;

CREATE TRIGGER IF NOT EXISTS trg_block_delete_preferences_nodelete
BEFORE UPDATE OF to_recycle ON preferences
WHEN NEW.to_recycle = 1 AND OLD.is_deletable = 0
BEGIN
  SELECT RAISE(ABORT, 'Нельзя удалить системную запись preferences.');
END;

CREATE TRIGGER IF NOT EXISTS trg_block_delete_clients_nodelete
BEFORE UPDATE OF to_recycle ON clients
WHEN NEW.to_recycle = 1 AND OLD.is_deletable = 0
BEGIN
  SELECT RAISE(ABORT, 'Нельзя удалить системную запись clients.');
END;

CREATE TRIGGER IF NOT EXISTS trg_block_delete_order_statuses_nodelete
BEFORE UPDATE OF to_recycle ON order_statuses
WHEN NEW.to_recycle = 1 AND OLD.is_deletable = 0
BEGIN
  SELECT RAISE(ABORT, 'Нельзя удалить системную запись order_statuses.');
END;

CREATE TRIGGER IF NOT EXISTS trg_block_delete_delivery_list_nodelete
BEFORE UPDATE OF to_recycle ON delivery_list
WHEN NEW.to_recycle = 1 AND OLD.is_deletable = 0
BEGIN
  SELECT RAISE(ABORT, 'Нельзя удалить системную запись delivery_list.');
END;

CREATE TRIGGER IF NOT EXISTS trg_block_delete_discount_nodelete
BEFORE UPDATE OF to_recycle ON discount
WHEN NEW.to_recycle = 1 AND OLD.is_deletable = 0
BEGIN
  SELECT RAISE(ABORT, 'Нельзя удалить системную запись discount.');
END;

CREATE TRIGGER IF NOT EXISTS trg_block_delete_pay_type_nodelete
BEFORE UPDATE OF to_recycle ON pay_type
WHEN NEW.to_recycle = 1 AND OLD.is_deletable = 0
BEGIN
  SELECT RAISE(ABORT, 'Нельзя удалить системную запись pay_type.');
END;

-- ============================================================
-- 6.2.1 КАСКАДНОЕ УДАЛЕНИЕ НОМЕНКЛАТУРЫ
-- При перемещении категории в корзину — рекурсивно все дочерние
-- Работает через PRAGMA recursive_triggers = ON
-- ============================================================

CREATE TRIGGER IF NOT EXISTS trg_cascade_delete_nomenclature
AFTER UPDATE OF to_recycle ON nomenclature
WHEN NEW.to_recycle = 1 AND OLD.to_recycle = 0
BEGIN
  UPDATE nomenclature SET to_recycle = 1
  WHERE parent_id = NEW.id AND to_recycle = 0;
END;

-- ============================================================
-- 6.3 TRIGGER INSTEAD OF INSERT на v_order_items
-- Автоподстановка цен, снапшотов имён, расчёт line_total
-- ============================================================

CREATE TRIGGER IF NOT EXISTS trg_insert_order_items
INSTEAD OF INSERT ON v_order_items
BEGIN
  -- Вставка с автоподстановкой
  INSERT INTO order_items (
    order_id,
    line_number,
    pattern_name,
    pattern_width,
    pattern_height,
    pattern_server_name,
    material_id,
    material_name_snapshot,
    price_per_sqm,
    quantity,
    has_adjustment,
    adjustment_service_id,
    adjustment_service_name_snapshot,
    adjustment_qty,
    adjustment_price,
    has_gluing,
    gluing_service_id,
    gluing_service_name_snapshot,
    gluing_qty,
    gluing_price,
    line_total
  )
  SELECT
    NEW.order_id,
    COALESCE(NEW.line_number,
      (SELECT COALESCE(MAX(line_number), 0) + 1
       FROM order_items WHERE order_id = NEW.order_id)
    ),
    NEW.pattern_name,
    ROUND(NEW.pattern_width, 4),
    ROUND(NEW.pattern_height, 4),
    NEW.pattern_server_name,
    NEW.material_id,
    -- снапшот имени материала
    COALESCE(
      (SELECT name FROM nomenclature WHERE id = NEW.material_id AND is_category = 0),
      'Неизвестно'
    ),
    -- цена за м² из справочника
    COALESCE(
      (SELECT price FROM nomenclature WHERE id = NEW.material_id AND is_category = 0),
      0
    ),
    COALESCE(NEW.quantity, 1),
    -- корректировка: ID услуги из preferences id=1 → nomenclature
    COALESCE(NEW.has_adjustment, 0),
    (SELECT CAST(value AS INTEGER) FROM preferences WHERE id = 1),
    COALESCE(
      (SELECT n.name
       FROM preferences p
       JOIN nomenclature n ON n.id = CAST(p.value AS INTEGER)
       WHERE p.id = 1),
      ''
    ),
    ROUND(COALESCE(NEW.adjustment_qty, 0), 2),
    ROUND(COALESCE(
      (SELECT n.price
       FROM preferences p
       JOIN nomenclature n ON n.id = CAST(p.value AS INTEGER)
       WHERE p.id = 1),
      0
    ), 2),
    -- склейка: ID услуги из preferences id=2 → nomenclature
    COALESCE(NEW.has_gluing, 0),
    (SELECT CAST(value AS INTEGER) FROM preferences WHERE id = 2),
    COALESCE(
      (SELECT n.name
       FROM preferences p
       JOIN nomenclature n ON n.id = CAST(p.value AS INTEGER)
       WHERE p.id = 2),
      ''
    ),
    ROUND(COALESCE(NEW.gluing_qty, 0), 2),
    ROUND(COALESCE(
      (SELECT n.price
       FROM preferences p
       JOIN nomenclature n ON n.id = CAST(p.value AS INTEGER)
       WHERE p.id = 2),
      0
    ), 2),
    -- line_total = (цена_за_м2 × ширина × высота × кол-во)
    --   + (если корректировка: цена_корр × кол-во_корр)
    --   + (если склейка: цена_склейки × кол-во_склейки)
    ROUND(
      COALESCE(
        (SELECT n.price FROM nomenclature n WHERE n.id = NEW.material_id AND n.is_category = 0),
        0
      )
      * COALESCE(NEW.pattern_width, 0)
      * COALESCE(NEW.pattern_height, 0)
      * COALESCE(NEW.quantity, 1)
      + CASE WHEN COALESCE(NEW.has_adjustment, 0) = 1 THEN
          ROUND(COALESCE(
            (SELECT n.price
             FROM preferences p
             JOIN nomenclature n ON n.id = CAST(p.value AS INTEGER)
             WHERE p.id = 1),
            0
          ), 2)
          * ROUND(COALESCE(NEW.adjustment_qty, 0), 2)
        ELSE 0 END
      + CASE WHEN COALESCE(NEW.has_gluing, 0) = 1 THEN
          ROUND(COALESCE(
            (SELECT n.price
             FROM preferences p
             JOIN nomenclature n ON n.id = CAST(p.value AS INTEGER)
             WHERE p.id = 2),
            0
          ), 2)
          * ROUND(COALESCE(NEW.gluing_qty, 0), 2)
        ELSE 0 END
    , 2);
END;

-- ============================================================
-- 6.4 TRIGGER INSTEAD OF INSERT на v_orders
-- Автоподстановка снапшотов, начальный статус, история
-- ============================================================

CREATE TRIGGER IF NOT EXISTS trg_insert_orders
INSTEAD OF INSERT ON v_orders
BEGIN
  INSERT INTO orders (
    created_at,
    client_id,
    delivery_list_id,
    delivery_detail,
    delivery_price,
    status_id,
    discount_id,
    discount_amount,
    discount_is_percent,
    author,
    note,
    asap,
    asap_percent,
    to_recycle,
    items_subtotal,
    discount_value,
    total_sum,
    client_name_snapshot,
    delivery_name_snapshot,
    discount_name_snapshot,
    status_name_snapshot
  )
  SELECT
    COALESCE(NEW.created_at, CURRENT_TIMESTAMP),
    NEW.client_id,
    NEW.delivery_list_id,
    NEW.delivery_detail,
    ROUND(COALESCE(NEW.delivery_price, 0), 2),
    COALESCE(NEW.status_id, 1),
    NEW.discount_id,
    ROUND(COALESCE(NEW.discount_amount, 0), 2),
    COALESCE(NEW.discount_is_percent, 0),
    COALESCE(NEW.author, 1),
    NEW.note,
    COALESCE(NEW.asap, 0),
    CASE
      WHEN COALESCE(NEW.asap, 0) = 1 THEN
        COALESCE(
          (SELECT value FROM preferences WHERE id = 3),
          1.0
        )
      ELSE 1.0
    END,
    0,
    0,
    0,
    0,
    -- снапшоты
    COALESCE(
      (SELECT name FROM clients WHERE id = NEW.client_id),
      ''
    ),
    COALESCE(
      (SELECT name FROM delivery_list WHERE id = NEW.delivery_list_id),
      ''
    ),
    COALESCE(
      (SELECT name FROM discount WHERE id = NEW.discount_id),
      ''
    ),
    COALESCE(
      (SELECT name FROM order_statuses WHERE id = COALESCE(NEW.status_id, 1)),
      'Новый'
    );

  -- начальная запись в историю статусов
  INSERT INTO status_history (order_id, status_id, changed_by)
  VALUES (
    (SELECT MAX(id) FROM orders),
    COALESCE(NEW.status_id, 1),
    COALESCE(NEW.author, 1)
  );
END;

-- ============================================================
-- 6.5 ПЕРЕСЧЁТ ИТОГОВ ЗАКАЗА
-- Вызывается после вставки/обновления/удаления строки заказа
-- и после изменения полей заказа, влияющих на расчёт
-- ============================================================

-- Общая процедура пересчёта (через вспомогательный триггер)
-- Пересчёт после INSERT в order_items
CREATE TRIGGER IF NOT EXISTS trg_recalc_order_after_item_insert
AFTER INSERT ON order_items
BEGIN
  UPDATE orders SET
    items_subtotal = (
      SELECT COALESCE(SUM(line_total), 0)
      FROM order_items
      WHERE order_id = NEW.order_id
    )
  WHERE id = NEW.order_id;

  UPDATE orders SET
    discount_value = ROUND(
      CASE
        WHEN discount_is_percent = 1
          THEN items_subtotal * discount_amount / 100.0
        ELSE
          MIN(COALESCE(discount_amount, 0), items_subtotal)
      END
    , 2),
    total_sum = ROUND(
      items_subtotal
      * CASE WHEN asap = 1 THEN COALESCE(asap_percent, 1.0) ELSE 1.0 END
      - CASE
          WHEN discount_is_percent = 1
            THEN items_subtotal * discount_amount / 100.0
          ELSE
            MIN(COALESCE(discount_amount, 0), items_subtotal)
        END
      + COALESCE(delivery_price, 0)
    , 2)
  WHERE id = NEW.order_id;
END;

-- Пересчёт после UPDATE в order_items
CREATE TRIGGER IF NOT EXISTS trg_recalc_order_after_item_update
AFTER UPDATE ON order_items
BEGIN
  UPDATE orders SET
    items_subtotal = (
      SELECT COALESCE(SUM(line_total), 0)
      FROM order_items
      WHERE order_id = NEW.order_id
    )
  WHERE id = NEW.order_id;

  UPDATE orders SET
    discount_value = ROUND(
      CASE
        WHEN discount_is_percent = 1
          THEN items_subtotal * discount_amount / 100.0
        ELSE
          MIN(COALESCE(discount_amount, 0), items_subtotal)
      END
    , 2),
    total_sum = ROUND(
      items_subtotal
      * CASE WHEN asap = 1 THEN COALESCE(asap_percent, 1.0) ELSE 1.0 END
      - CASE
          WHEN discount_is_percent = 1
            THEN items_subtotal * discount_amount / 100.0
          ELSE
            MIN(COALESCE(discount_amount, 0), items_subtotal)
        END
      + COALESCE(delivery_price, 0)
    , 2)
  WHERE id = NEW.order_id;
END;

-- Пересчёт после UPDATE полей заказа, влияющих на расчёт
CREATE TRIGGER IF NOT EXISTS trg_recalc_order_on_fields_update
AFTER UPDATE OF delivery_price, discount_id, discount_amount,
  discount_is_percent, asap, asap_percent ON orders
BEGIN
  UPDATE orders SET
    discount_value = ROUND(
      CASE
        WHEN discount_is_percent = 1
          THEN items_subtotal * discount_amount / 100.0
        ELSE
          MIN(COALESCE(discount_amount, 0), items_subtotal)
      END
    , 2),
    total_sum = ROUND(
      items_subtotal
      * CASE WHEN asap = 1 THEN COALESCE(asap_percent, 1.0) ELSE 1.0 END
      - CASE
          WHEN discount_is_percent = 1
            THEN items_subtotal * discount_amount / 100.0
          ELSE
            MIN(COALESCE(discount_amount, 0), items_subtotal)
        END
      + COALESCE(delivery_price, 0)
    , 2)
  WHERE id = NEW.id;
END;

-- ============================================================
-- 6.7 ПЕРЕСЧЁТ ПРИ ИЗМЕНЕНИИ ЦЕН УСЛУГ (Preferences)
-- При изменении цены корректировки (id=1) или склейки (id=2)
-- пересчитываем line_total во всех затронутых order_items
-- ============================================================

-- Пересчёт при изменении цены корректировки
CREATE TRIGGER IF NOT EXISTS trg_recalc_on_adjustment_price_change
AFTER UPDATE OF value ON preferences
WHEN NEW.id = 1 AND OLD.value != NEW.value
BEGIN
  UPDATE order_items SET
    adjustment_price = ROUND(NEW.value, 2),
    line_total = ROUND(
      price_per_sqm * pattern_width * pattern_height * quantity
      + CASE WHEN has_adjustment = 1 THEN NEW.value * adjustment_qty ELSE 0 END
      + CASE WHEN has_gluing = 1 THEN gluing_price * gluing_qty ELSE 0 END
    , 2)
  WHERE has_adjustment = 1;
END;

-- Пересчёт при изменении цены склейки
CREATE TRIGGER IF NOT EXISTS trg_recalc_on_gluing_price_change
AFTER UPDATE OF value ON preferences
WHEN NEW.id = 2 AND OLD.value != NEW.value
BEGIN
  UPDATE order_items SET
    gluing_price = ROUND(NEW.value, 2),
    line_total = ROUND(
      price_per_sqm * pattern_width * pattern_height * quantity
      + CASE WHEN has_adjustment = 1 THEN adjustment_price * adjustment_qty ELSE 0 END
      + CASE WHEN has_gluing = 1 THEN NEW.value * gluing_qty ELSE 0 END
    , 2)
  WHERE has_gluing = 1;
END;

-- ============================================================
-- 6.6 АВТОПОДСТАНОВКА СТАТУСА В ЗАКАЗЕ
-- При смене статуса обновляем снапшот и пишем историю
-- ============================================================

CREATE TRIGGER IF NOT EXISTS trg_update_order_status_snapshot
AFTER UPDATE OF status_id ON orders
WHEN NEW.status_id != OLD.status_id
BEGIN
  UPDATE orders SET
    status_name_snapshot = (
      SELECT name FROM order_statuses WHERE id = NEW.status_id
    )
  WHERE id = NEW.id;

  INSERT INTO status_history (order_id, status_id, changed_by)
  VALUES (NEW.id, NEW.status_id, 1);
END;

-- ============================================================
-- 6.7 РОТАЦИЯ ЛОГА
-- Хранить последние 10000 записей, старые удалять
-- ============================================================

CREATE TRIGGER IF NOT EXISTS trg_rotate_activity_log
AFTER INSERT ON user_activity_log
BEGIN
  DELETE FROM user_activity_log
  WHERE id NOT IN (
    SELECT id FROM user_activity_log ORDER BY created_at DESC LIMIT 10000
  );
END;

-- ============================================================
-- 7. НАЧАЛЬНОЕ ЗАПОЛНЕНИЕ (SEED DATA)
-- ============================================================

-- 7.1 Системные пользователи (неудаляемые)
INSERT INTO users (id, username, password_hash, fullname, role, is_deletable)
VALUES (0, 'untonee', 'scrypt:32768:8:1$HNEMzMe7CIEwl2rT$ab630aa85a8f0c0da41ab8a1861b982be1add42f5dba7e60df06be437cd2c6fca2f435609f4cadd6995d69397c3402909ee15454be16dd3450eef7e96b54646d', 'untonee', 'admin', 0);

INSERT INTO users (id, username, password_hash, fullname, role, is_deletable)
VALUES (1, 'admin', 'scrypt:32768:8:1$Y7sTUNQ3FcCCl6vp$d1f5024adfc99b3073a49680dda7631d4a0d591f79d989aae870912657b20708a2fd161ae5c3e64147e9dc0338975bbe4f327ff1c4c025b2966c01e5ab9b87df', 'Администратор', 'admin', 0);

INSERT INTO users (id, username, password_hash, fullname, role, is_deletable)
VALUES (2, 'supervisor', 'scrypt:32768:8:1$Y7sTUNQ3FcCCl6vp$d1f5024adfc99b3073a49680dda7631d4a0d591f79d989aae870912657b20708a2fd161ae5c3e64147e9dc0338975bbe4f327ff1c4c025b2966c01e5ab9b87df', 'Руководитель', 'supervisor', 1);

INSERT INTO users (id, username, password_hash, fullname, role, is_deletable)
VALUES (3, 'manager', 'scrypt:32768:8:1$Y7sTUNQ3FcCCl6vp$d1f5024adfc99b3073a49680dda7631d4a0d591f79d989aae870912657b20708a2fd161ae5c3e64147e9dc0338975bbe4f327ff1c4c025b2966c01e5ab9b87df', 'Менеджер', 'manager', 1);

-- 7.2 Предпочтения (настройки по умолчанию)
INSERT INTO preferences (id, name, value, is_deletable)
VALUES (1, 'Услуга корректировки файла', 90001, 0);

INSERT INTO preferences (id, name, value, is_deletable)
VALUES (2, 'Услуга склейки', 90002, 0);

INSERT INTO preferences (id, name, value, is_deletable)
VALUES (3, 'Коэффициент за срочность (ASAP)', 1.3, 0);

-- 7.3 Услуги в номенклатуре (неудаляемые)
INSERT INTO nomenclature (id, name, price, is_category, is_deletable)
VALUES (90001, 'Корректировка файла', 11.00, 0, 0);

INSERT INTO nomenclature (id, name, price, is_category, is_deletable)
VALUES (90002, 'Склейка', 22.00, 0, 0);

-- 7.4 Статусы заказов (неудаляемые)
INSERT INTO order_statuses (id, name, color, sort_order, is_deletable)
VALUES (1, 'Новый', '#3B82F6', 1, 0);

INSERT INTO order_statuses (id, name, color, sort_order, is_deletable)
VALUES (2, 'Согласование', '#F59E0B', 2, 0);

INSERT INTO order_statuses (id, name, color, sort_order, is_deletable)
VALUES (3, 'В работе', '#10B981', 3, 0);

INSERT INTO order_statuses (id, name, color, sort_order, is_deletable)
VALUES (4, 'Готов к выдаче', '#8B5CF6', 4, 0);

INSERT INTO order_statuses (id, name, color, sort_order, is_deletable)
VALUES (5, 'Исполнен', '#6B7280', 5, 0);

INSERT INTO order_statuses (id, name, color, sort_order, is_deletable)
VALUES (6, 'Отменён', '#EF4444', 6, 0);

-- 7.5 Варианты доставки (неудаляемые)
INSERT INTO delivery_list (id, name, is_deletable)
VALUES (1, 'Самовывоз', 0);

INSERT INTO delivery_list (id, name, is_deletable)
VALUES (2, 'Курьер', 0);

INSERT INTO delivery_list (id, name, is_deletable)
VALUES (3, 'Дмитровская', 0);

INSERT INTO delivery_list (id, name, is_deletable)
VALUES (4, 'Яндекс.Доставка', 0);

INSERT INTO delivery_list (id, name, is_deletable)
VALUES (5, 'СДЭК', 0);

INSERT INTO delivery_list (id, name, is_deletable)
VALUES (6, 'Иное', 0);

-- 7.6 Скидки (неудаляемые)
INSERT INTO discount (id, name, discount_amount, discount_is_percent, is_deletable)
VALUES (1, 'Скидка на сумму', 100.00, 0, 0);

INSERT INTO discount (id, name, discount_amount, discount_is_percent, is_deletable)
VALUES (2, 'Скидка %', 10.00, 1, 0);

-- 7.7 Типы оплаты (неудаляемые)
INSERT INTO pay_type (id, name, is_deletable)
VALUES (1, 'Наличные', 0);

INSERT INTO pay_type (id, name, is_deletable)
VALUES (2, 'Безналичный расчёт', 0);

INSERT INTO pay_type (id, name, is_deletable)
VALUES (3, 'Карта', 0);

-- ============================================================
-- ГОТОВО
-- ============================================================