-- ============================================================
-- HuoltoERP — MySQL 8.0+ tietokantaskeema
-- Kattaa: Asiakkaat, Laitteet, Työmääräimet, Varaosat,
--         Teknikot, Laskutus, POS-kassa, Kalenteri, SMS
-- Merkistö: UTF8MB4 | Moottori: InnoDB
-- ============================================================

SET foreign_key_checks = 0;

-- ============================================================
-- 2. TEKNIKOT / HENKILÖSTÖ
-- ============================================================
CREATE TABLE IF NOT EXISTS mechanics (
  id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name          VARCHAR(120) NOT NULL,
  email         VARCHAR(160),
  phone         VARCHAR(40),
  skills        VARCHAR(255) COMMENT 'CSV: Pyykinpesukone,Astianpesukone,Sähkö',
  hourly_rate   DECIMAL(8,2) NOT NULL DEFAULT 75.00,
  is_active     TINYINT(1)   NOT NULL DEFAULT 1,
  created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 3. ASIAKKAAT
-- ============================================================
CREATE TABLE IF NOT EXISTS customers (
  id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_number         VARCHAR(20)  NOT NULL UNIQUE COMMENT 'esim. 14830',
  name                    VARCHAR(120) NOT NULL,
  name2                   VARCHAR(120) COMMENT 'Yritys tai lisänimi',
  y_tunnus                VARCHAR(20),
  alv_tunnus              VARCHAR(30),
  phone                   VARCHAR(40),
  email                   VARCHAR(160),
  website                 VARCHAR(200),
  location                VARCHAR(100) COMMENT 'Kaupunki/alue',
  -- Käyntiosoite
  street_address          VARCHAR(160),
  postal_code             VARCHAR(20),
  city                    VARCHAR(80),
  country                 CHAR(2)      NOT NULL DEFAULT 'FI',
  -- Eri toimitusosoite
  has_delivery_address    TINYINT(1)   NOT NULL DEFAULT 0,
  delivery_street         VARCHAR(160),
  delivery_postal_code    VARCHAR(20),
  delivery_city           VARCHAR(80),
  delivery_country        CHAR(2),
  -- Eri laskutusosoite
  has_billing_address     TINYINT(1)   NOT NULL DEFAULT 0,
  billing_name            VARCHAR(120),
  billing_street          VARCHAR(160),
  billing_postal_code     VARCHAR(20),
  billing_city            VARCHAR(80),
  billing_country         CHAR(2),
  -- Laskutustiedot
  different_billing_customer TINYINT(1) NOT NULL DEFAULT 0,
  default_payment_method  ENUM('lasku','käteinen','kortti','verkkomaksu','suoraveloitus') DEFAULT 'lasku',
  default_vat_rate        DECIMAL(5,2) DEFAULT 25.50,
  payment_terms_days      INT          DEFAULT 14 COMMENT 'Maksuehto päivinä',
  billing_channel         ENUM('email','finvoice','posti','nouto') DEFAULT 'email',
  our_reference           VARCHAR(100),
  order_default_message   TEXT         COMMENT 'Vakioviesti tilauksille',
  invoice_default_message TEXT         COMMENT 'Vakioviesti laskulle',
  -- Muut tiedot
  status                  ENUM('aktiivinen','passiivinen','suljettu','prospekti') NOT NULL DEFAULT 'aktiivinen',
  guarantee_multiplier    DECIMAL(5,2) DEFAULT 1.00,
  is_guarantee_target     TINYINT(1)   NOT NULL DEFAULT 0,
  notes                   TEXT,
  payment_default_date    DATE         COMMENT 'Maksuhäiriömerkintä',
  crm_notes               TEXT         COMMENT 'CRM sisäinen muistiinpano',
  created_at              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at              DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_name (name),
  INDEX idx_customer_number (customer_number),
  INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 4. LAITEREKISTERI
-- ============================================================
CREATE TABLE IF NOT EXISTS devices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  serial_number   VARCHAR(80)  NOT NULL UNIQUE COMMENT 'Sarjanumero',
  brand           VARCHAR(80)  NOT NULL COMMENT 'Merkki: Bosch, Miele...',
  model           VARCHAR(120) NOT NULL COMMENT 'Malli: WAX32EH0DN',
  device_type     ENUM('pyykinpesukone','astianpesukone','kuivausrumpu',
                       'jääkaappi','pakastin','liesi','mikro','imuri','muu')
                  NOT NULL DEFAULT 'muu',
  type_number     VARCHAR(80)  COMMENT 'Tyyppinumero',
  customer_id     INT UNSIGNED,
  purchase_date   DATE,
  warranty_end    DATE,
  importer        VARCHAR(120) COMMENT 'Maahantuoja: BSH Kodinkoneet Oy',
  seller          VARCHAR(120) COMMENT 'Myyjäliike',
  notes           TEXT,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_serial (serial_number),
  INDEX idx_brand_model (brand, model),
  INDEX idx_customer (customer_id),
  CONSTRAINT fk_device_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 5. TOIMITTAJAT (varaosatoimittajat)
-- ============================================================
CREATE TABLE IF NOT EXISTS suppliers (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name            VARCHAR(120) NOT NULL,
  contact_person  VARCHAR(100),
  phone           VARCHAR(40),
  email           VARCHAR(160),
  website         VARCHAR(200),
  street_address  VARCHAR(160),
  postal_code     VARCHAR(20),
  city            VARCHAR(80),
  country         CHAR(2)      DEFAULT 'FI',
  notes           TEXT,
  is_active       TINYINT(1)   NOT NULL DEFAULT 1,
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 6. VARAOSAT / TUOTTEET
-- ============================================================
CREATE TABLE IF NOT EXISTS parts (
  id                      INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sku                     VARCHAR(80)  NOT NULL UNIQUE COMMENT 'Artikkelinumero / SKU',
  barcode                 VARCHAR(80)  COMMENT 'Viivakoodi',
  name                    VARCHAR(200) NOT NULL,
  brand                   VARCHAR(80)  COMMENT 'Merkki / Soveltuu',
  part_type               VARCHAR(80)  COMMENT 'Tuotetyyppi: Vastus, Tiiviste...',
  manufacturer_name       VARCHAR(120) COMMENT 'Valmistajan nimi',
  manufacturer_part_no    VARCHAR(80)  COMMENT 'Valmistajan artikkelinumero',
  supplier_id             INT UNSIGNED,
  supplier_part_no        VARCHAR(80)  COMMENT 'Toimittajan artikkelinumero',
  supplier_order_no       VARCHAR(80)  COMMENT 'Toimittajan tilausnumero',
  -- Hinnat
  cost_price              DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Ostohinta alv 0%',
  sell_price              DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Myyntihinta alv 0%',
  vat_rate                DECIMAL(5,2)  NOT NULL DEFAULT 25.50,
  -- Varasto
  stock_qty               DECIMAL(10,2) NOT NULL DEFAULT 0,
  stock_min               DECIMAL(10,2) NOT NULL DEFAULT 2   COMMENT 'Hälytysraja',
  stock_unit              ENUM('kpl','h','km','m','l','kg')  DEFAULT 'kpl',
  warehouse               VARCHAR(80)   DEFAULT 'Iisalmi'    COMMENT 'Varasto',
  shelf                   VARCHAR(80)   DEFAULT 'Liike'      COMMENT 'Hylly',
  -- Arvioitu saatavuus
  estimated_arrival       DATE,
  -- Status
  is_active               TINYINT(1)    NOT NULL DEFAULT 1,
  notes                   TEXT,
  created_at              DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at              DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_sku (sku),
  INDEX idx_barcode (barcode),
  INDEX idx_brand (brand),
  INDEX idx_stock_low (stock_qty, stock_min),
  CONSTRAINT fk_part_supplier FOREIGN KEY (supplier_id)
    REFERENCES suppliers(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 7. TYÖMÄÄRÄIMET
-- ============================================================
CREATE TABLE IF NOT EXISTS work_orders (
  id                    INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_number          VARCHAR(20)  NOT NULL UNIQUE COMMENT 'esim. TM-00001',
  -- Asiakastiedot (snapshot tallennettuna)
  customer_id           INT UNSIGNED NOT NULL,
  customer_name         VARCHAR(120) NOT NULL COMMENT 'Snapshot tallennuksesta',
  customer_name2        VARCHAR(120),
  customer_street       VARCHAR(160),
  customer_postal_code  VARCHAR(20),
  customer_city         VARCHAR(80),
  customer_country      CHAR(2)      DEFAULT 'FI',
  customer_phone        VARCHAR(40),
  customer_email        VARCHAR(160),
  customer_y_tunnus     VARCHAR(20),
  customer_alv_tunnus   VARCHAR(30),
  work_orderer          VARCHAR(120) COMMENT 'Työ tilaaja',
  orderer_phone         VARCHAR(40),
  orderer_email         VARCHAR(160),
  billing_channel       ENUM('email','finvoice','posti','nouto'),
  payment_terms         VARCHAR(40),
  customer_reference    VARCHAR(100) COMMENT 'Tilausviite / Viitteenne',
  -- Laitetiedot
  device_id             INT UNSIGNED,
  serial_number         VARCHAR(80)  COMMENT 'Sarjanumero — kopioitu',
  device_brand          VARCHAR(80),
  device_model          VARCHAR(120),
  device_type           VARCHAR(80),
  type_number           VARCHAR(80)  COMMENT 'Tyyppinumero',
  purchase_date         DATE         COMMENT 'Ostettu',
  warranty_end          DATE,
  fault_code            VARCHAR(50)  COMMENT 'Vikakoodi (2)',
  seller_store          VARCHAR(120) COMMENT 'Myyjäliike',
  delivery_deadline     DATE         COMMENT 'Sovittu toimitusaika',
  accessories_info      TEXT         COMMENT 'Lisätarvikkeet',
  included_items        VARCHAR(255) COMMENT 'Lähetyksessä mukana',
  -- Työn tyyppi ja takuu
  work_type             ENUM('maksullinen','takuutyö','maksuton') NOT NULL DEFAULT 'maksullinen',
  is_warranty           TINYINT(1)   NOT NULL DEFAULT 0,
  warranty_number       VARCHAR(80),
  importer              VARCHAR(120) COMMENT 'Maahantuoja (takuutyö)',
  charge_target         ENUM('asiakas','vakuutus','takuu','maahantuoja') DEFAULT 'asiakas',
  -- Työn kuvaukset
  work_order_description TEXT        NOT NULL COMMENT 'Työtilaus *',
  work_report           TEXT         COMMENT 'Työselostus',
  internal_notes        TEXT         COMMENT 'Huollon sisäinen lisätieto',
  -- Huollon tiedot
  status                ENUM('saapunut','diagnoosi','odottaa_osia','korjaus',
                             'takuukäsittely','valmis','luovutettu','peruttu')
                        NOT NULL DEFAULT 'saapunut',
  priority              ENUM('normaali','kiireellinen','kriittinen') NOT NULL DEFAULT 'normaali',
  mechanic_id           INT UNSIGNED COMMENT 'Vastuuhenkilö',
  suggested_service_date DATE        COMMENT 'Ehdotettu huoltopäivä',
  payment_method        ENUM('lasku','käteinen','kortti','verkkomaksu') DEFAULT 'lasku',
  loan_device           VARCHAR(120) COMMENT 'Lainalaite',
  travel_km             INT          DEFAULT 0 COMMENT 'Työmatka km',
  service_visit_date    DATE         COMMENT 'Huoltokäynnin päivämäärä',
  -- Hinnoittelu
  hourly_rate           DECIMAL(8,2) NOT NULL DEFAULT 75.00,
  hours_logged          DECIMAL(6,2) NOT NULL DEFAULT 0,
  -- Laskutus
  invoiced              TINYINT(1)   NOT NULL DEFAULT 0,
  invoice_id            INT UNSIGNED,
  -- Aikaleimät
  created_at            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at            DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  completed_at          DATETIME,
  delivered_at          DATETIME,
  -- Liitteet: tallennettaan erilliseen tauluun
  INDEX idx_order_number (order_number),
  INDEX idx_customer (customer_id),
  INDEX idx_device (device_id),
  INDEX idx_serial (serial_number),
  INDEX idx_status (status),
  INDEX idx_mechanic (mechanic_id),
  INDEX idx_created (created_at),
  CONSTRAINT fk_wo_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE RESTRICT,
  CONSTRAINT fk_wo_device FOREIGN KEY (device_id)
    REFERENCES devices(id) ON DELETE SET NULL,
  CONSTRAINT fk_wo_mechanic FOREIGN KEY (mechanic_id)
    REFERENCES mechanics(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 8. TYÖMÄÄRÄIMEN VARAOSAT / KUSTANNUSRIVIT
-- ============================================================
CREATE TABLE IF NOT EXISTS order_parts (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id        INT UNSIGNED NOT NULL,
  part_id         INT UNSIGNED COMMENT 'NULL jos käsin syötetty',
  product_name    VARCHAR(200) NOT NULL COMMENT 'Tuote / palvelu',
  product_code    VARCHAR(80)  COMMENT 'Koodi / SKU',
  additional_info VARCHAR(255) COMMENT 'Lisätieto riville',
  product_type    ENUM('varaosa','työ','matka','lisätarvike','muu') DEFAULT 'varaosa',
  unit_price_net  DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat_rate        DECIMAL(5,2)  NOT NULL DEFAULT 25.50,
  qty             DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit            ENUM('kpl','h','km','m','l','kg') DEFAULT 'kpl',
  discount_pct    DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
  total_net       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_gross     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  part_status     ENUM('tilattava','hyllyssä','tilattu','ei_saatavilla') DEFAULT 'hyllyssä',
  estimated_arrival DATE,
  created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_order (order_id),
  INDEX idx_part (part_id),
  CONSTRAINT fk_op_order FOREIGN KEY (order_id)
    REFERENCES work_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_op_part FOREIGN KEY (part_id)
    REFERENCES parts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 9. TYÖMÄÄRÄIMEN LIITTEET
-- ============================================================
CREATE TABLE IF NOT EXISTS order_attachments (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    INT UNSIGNED NOT NULL,
  filename    VARCHAR(255) NOT NULL,
  title       VARCHAR(200),
  description TEXT,
  mime_type   VARCHAR(100),
  file_size   INT UNSIGNED,
  file_path   VARCHAR(500) NOT NULL COMMENT 'Palvelimen polku',
  uploaded_by INT UNSIGNED,
  created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_att_order FOREIGN KEY (order_id)
    REFERENCES work_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_att_user FOREIGN KEY (uploaded_by)
    REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 10. TILAHISTORIA (audit trail)
-- ============================================================
CREATE TABLE IF NOT EXISTS order_status_history (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    INT UNSIGNED NOT NULL,
  old_status  VARCHAR(40),
  new_status  VARCHAR(40)  NOT NULL,
  changed_by  INT UNSIGNED,
  note        VARCHAR(255),
  changed_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sh_order FOREIGN KEY (order_id)
    REFERENCES work_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_sh_user FOREIGN KEY (changed_by)
    REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- HUOM: Tilamuutoshistoria kirjataan sovelluskoodissa (WorkOrderModel::changeStatus),
-- ei triggerillä. Näin vältetään kaksoiskirjaus ja saadaan mukaan changed_by + note.

-- ============================================================
-- 11. TYÖAIKASEURANTA
-- ============================================================
CREATE TABLE IF NOT EXISTS time_logs (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id    INT UNSIGNED NOT NULL,
  mechanic_id INT UNSIGNED NOT NULL,
  log_date    DATE         NOT NULL,
  hours       DECIMAL(5,2) NOT NULL DEFAULT 0,
  km          INT          NOT NULL DEFAULT 0,
  description TEXT,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_order (order_id),
  INDEX idx_mechanic (mechanic_id),
  INDEX idx_date (log_date),
  CONSTRAINT fk_tl_order FOREIGN KEY (order_id)
    REFERENCES work_orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_tl_mechanic FOREIGN KEY (mechanic_id)
    REFERENCES mechanics(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 12. LASKUT
-- ============================================================
CREATE TABLE IF NOT EXISTS invoices (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  invoice_number  VARCHAR(20)   NOT NULL UNIQUE COMMENT 'esim. LASKU-00001',
  order_id        INT UNSIGNED,
  customer_id     INT UNSIGNED  NOT NULL,
  -- Summat
  subtotal_net    DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Alv 0%',
  vat_amount      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat_rate        DECIMAL(5,2)  NOT NULL DEFAULT 25.50,
  discount_pct    DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
  discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_gross     DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT 'Verollinen yht.',
  -- Maksu
  status          ENUM('luonnos','avoin','maksettu','erääntynyt','peruttu') NOT NULL DEFAULT 'luonnos',
  payment_method  ENUM('lasku','käteinen','kortti','verkkomaksu'),
  due_date        DATE,
  paid_date       DATE,
  -- Viite
  our_reference   VARCHAR(100),
  customer_reference VARCHAR(100),
  notes           TEXT,
  -- Procountor/taloushallinto
  accounting_ref  VARCHAR(80)   COMMENT 'Taloushallinnon viite (Procountor)',
  sent_at         DATETIME      COMMENT 'Lähetysaika',
  created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_invoice_number (invoice_number),
  INDEX idx_customer (customer_id),
  INDEX idx_order (order_id),
  INDEX idx_status (status),
  CONSTRAINT fk_inv_order FOREIGN KEY (order_id)
    REFERENCES work_orders(id) ON DELETE SET NULL,
  CONSTRAINT fk_inv_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE work_orders ADD CONSTRAINT fk_wo_invoice
  FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE SET NULL;

-- ============================================================
-- 13. AJANVARAUKSET / KALENTERI
-- ============================================================
CREATE TABLE IF NOT EXISTS bookings (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  booking_ref     VARCHAR(20)  NOT NULL UNIQUE COMMENT 'esim. B-00001',
  customer_id     INT UNSIGNED,
  order_id        INT UNSIGNED COMMENT 'Liitetty työmääräin',
  booking_date    DATE         NOT NULL,
  start_time      TIME         NOT NULL DEFAULT '08:00:00',
  end_time        TIME         NOT NULL DEFAULT '09:00:00',
  duration_min    INT          NOT NULL DEFAULT 60,
  description     TEXT,
  booking_type    ENUM('huolto','korjaus','asennus','tarkistus','muu') DEFAULT 'huolto',
  status          ENUM('vahvistettu','peruttu','siirretty') DEFAULT 'vahvistettu',
  created_at      DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_date (booking_date),
  INDEX idx_customer (customer_id),
  CONSTRAINT fk_bk_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_bk_order FOREIGN KEY (order_id)
    REFERENCES work_orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS booking_mechanics (
  booking_id  INT UNSIGNED NOT NULL,
  mechanic_id INT UNSIGNED NOT NULL,
  PRIMARY KEY (booking_id, mechanic_id),
  CONSTRAINT fk_bm_booking FOREIGN KEY (booking_id)
    REFERENCES bookings(id) ON DELETE CASCADE,
  CONSTRAINT fk_bm_mechanic FOREIGN KEY (mechanic_id)
    REFERENCES mechanics(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 14. SMS-VIESTILOKI
-- ============================================================
CREATE TABLE IF NOT EXISTS sms_log (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  customer_id     INT UNSIGNED,
  order_id        INT UNSIGNED,
  to_phone        VARCHAR(40)  NOT NULL,
  recipient_name  VARCHAR(120),
  message         TEXT         NOT NULL,
  message_type    ENUM('valmis','muistutus','varaus','viivästys','muu') DEFAULT 'muu',
  status          ENUM('toimitettu','virhe','jonossa') DEFAULT 'jonossa',
  provider_ref    VARCHAR(80)  COMMENT 'Twilio/Vonage viestiID',
  sent_at         DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_customer (customer_id),
  INDEX idx_order (order_id),
  CONSTRAINT fk_sms_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_sms_order FOREIGN KEY (order_id)
    REFERENCES work_orders(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 15. POS-KASSATAPAHTUMAT
-- ============================================================
CREATE TABLE IF NOT EXISTS pos_sales (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  receipt_number  VARCHAR(20)  NOT NULL UNIQUE COMMENT 'esim. KUITTI-0001',
  customer_id     INT UNSIGNED COMMENT 'NULL = käteisasiakas',
  order_id        INT UNSIGNED COMMENT 'Liitetty työmääräin',
  cashier_id      INT UNSIGNED COMMENT 'Kassanhoitaja',
  payment_method  ENUM('käteinen','kortti','lasku') NOT NULL DEFAULT 'käteinen',
  subtotal_net    DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  vat_amount      DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  discount_pct    DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
  discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  total_gross     DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  cash_tendered   DECIMAL(10,2) COMMENT 'Maksettu käteinen',
  change_given    DECIMAL(10,2) COMMENT 'Vaihtoraha',
  shift_date      DATE          NOT NULL,
  sale_time       DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_receipt (receipt_number),
  INDEX idx_shift (shift_date),
  INDEX idx_customer (customer_id),
  CONSTRAINT fk_pos_customer FOREIGN KEY (customer_id)
    REFERENCES customers(id) ON DELETE SET NULL,
  CONSTRAINT fk_pos_order FOREIGN KEY (order_id)
    REFERENCES work_orders(id) ON DELETE SET NULL,
  CONSTRAINT fk_pos_cashier FOREIGN KEY (cashier_id)
    REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS pos_sale_items (
  id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  sale_id         INT UNSIGNED NOT NULL,
  part_id         INT UNSIGNED COMMENT 'NULL jos palvelu',
  product_name    VARCHAR(200) NOT NULL,
  sku             VARCHAR(80),
  unit_price_net  DECIMAL(10,2) NOT NULL,
  vat_rate        DECIMAL(5,2)  NOT NULL DEFAULT 25.50,
  qty             DECIMAL(10,2) NOT NULL DEFAULT 1,
  unit            VARCHAR(10)   DEFAULT 'kpl',
  discount_pct    DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
  total_net       DECIMAL(10,2) NOT NULL,
  total_gross     DECIMAL(10,2) NOT NULL,
  CONSTRAINT fk_psi_sale FOREIGN KEY (sale_id)
    REFERENCES pos_sales(id) ON DELETE CASCADE,
  CONSTRAINT fk_psi_part FOREIGN KEY (part_id)
    REFERENCES parts(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- HUOM: Varastonvähennys tehdään sovelluskoodissa (StockService::deduct),
-- ei triggerillä. Näin vältetään kaksoisvähennys ja kirjataan stock_movements.

-- ============================================================
-- 16. JÄRJESTELMÄASETUKSET
-- ============================================================
CREATE TABLE IF NOT EXISTS settings (
  setting_key   VARCHAR(100) NOT NULL PRIMARY KEY,
  setting_value TEXT,
  description   VARCHAR(255),
  updated_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 17. VARASTOSIIRROT (audit trail varastolle)
-- ============================================================
CREATE TABLE IF NOT EXISTS stock_movements (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  part_id     INT UNSIGNED  NOT NULL,
  movement    ENUM('in','out','adjustment') NOT NULL,
  qty         DECIMAL(10,2) NOT NULL,
  reference   VARCHAR(80)   COMMENT 'Tilaus/myynti viite',
  reason      VARCHAR(255),
  stock_after DECIMAL(10,2),
  created_by  INT UNSIGNED,
  created_at  DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_sm_part FOREIGN KEY (part_id)
    REFERENCES parts(id) ON DELETE CASCADE,
  CONSTRAINT fk_sm_user FOREIGN KEY (created_by)
    REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- 18. TEKSTIPOHJAT (Työselostuksen pohjat)
-- ============================================================
CREATE TABLE IF NOT EXISTS text_templates (
  id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(120) NOT NULL,
  category    VARCHAR(80)  COMMENT 'esim. työselostus, sms, lasku',
  content     TEXT         NOT NULL,
  is_active   TINYINT(1)   NOT NULL DEFAULT 1,
  created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================
-- SEED DATA: Oletusasetukset
-- ============================================================
INSERT INTO settings (setting_key, setting_value, description) VALUES
('company_name',        'HuoltoERP Oy',              'Yrityksen nimi'),
('company_y_tunnus',    '1234567-8',                  'Y-tunnus'),
('company_alv_tunnus',  'FI12345678',                 'ALV-tunnus'),
('company_address',     'Korjaamokatu 1, 00100 Helsinki', 'Osoite'),
('company_phone',       '010 123 4567',               'Puhelin'),
('company_email',       'info@huoltoerp.fi',          'Sähköposti'),
('company_website',     'https://www.huoltoerp.fi',   'Kotisivu'),
('default_vat_rate',    '25.5',                       'Oletusalv-%'),
('default_hourly_rate', '75.00',                      'Oletustuntihinta €'),
('invoice_due_days',    '14',                         'Laskun maksuaika päivinä'),
('order_number_prefix', 'TM-',                        'Työmääräinnumeron etuliite'),
('invoice_number_prefix','LASKU-',                    'Laskunumeron etuliite'),
('receipt_number_prefix','KUITTI-',                   'Kuittinumeron etuliite'),
('sms_provider',        'twilio',                     'SMS-palveluntarjoaja'),
('accounting_system',   'procountor',                 'Taloushallintojärjestelmä');

-- Oletusteknikot
INSERT INTO mechanics (name, email, phone, skills, hourly_rate) VALUES
('Pekka Leinonen',  'pekka@huoltoerp.fi', '+358 40 111 1111', 'Pyykinpesukone,Sähkö',          75.00),
('Antti Salminen',  'antti@huoltoerp.fi', '+358 40 222 2222', 'Astianpesukone,Kuivausrumpu',   75.00),
('Tiina Virtala',   'tiina@huoltoerp.fi', '+358 40 333 3333', 'Jääkaappi,Liesi,Ilmastointi',   75.00),
('Mikko Ranta',     'mikko@huoltoerp.fi', '+358 40 444 4444', 'Diagnoosi,Sähkö,Kaikki',        80.00),
('Ari Kaarakainen', 'ari@huoltoerp.fi',   '+358 40 555 5555', 'Hallinto',                       75.00);

-- Oletustoimittajat
INSERT INTO suppliers (name, phone, email, country) VALUES
('BSH Kodinkoneet Oy',    '020 1234 567', 'tilaukset@bsh.fi',         'FI'),
('Electrolux Finland Oy', '020 2345 678', 'tilaukset@electrolux.fi',  'FI'),
('Miele Finland Oy',      '020 3456 789', 'tilaukset@miele.fi',       'FI'),
('Teknos Oy',             '020 4567 890', 'varaosat@teknos.fi',       'FI');

-- Tekstipohjat
INSERT INTO text_templates (name, category, content) VALUES
('Tiivistevuoto korjattu',    'työselostus', 'Tarkistettu vuotokohta. Oventiiviste vaihdettu uuteen. Kone käynnistetty ja vuodon poistuminen varmistettu. Tarkistusajo suoritettu.'),
('Lämmitysvastus vaihdettu',  'työselostus', 'Diagnosoitu vikaantunut lämmitysvastus. Vanha vastus irrotettu ja uusi alkuperäisosa asennettu. Koneen toiminta testattu täydellä pesuohjelmalla.'),
('Pumppumoottori vaihdettu',  'työselostus', 'Pumppumoottori diagnosoitu vikaantuneeksi. Vaihdettu uuteen alkuperäisosaan. Tiivisteet tarkistettu. Toiminta varmistettu.'),
('Laakerisarja vaihdettu',    'työselostus', 'Linkouksessa esiintyvä ääni diagnosoitu laakeriviaksi. Laakerisarja vaihdettu. Kone tasapainotettu ja testiohjelmilla varmistettu normaali toiminta.'),
('Ohjauspaneeli vaihdettu',   'työselostus', 'Ohjauspaneeli diagnosoitu vikaantuneeksi. Uusi alkuperäinen paneeli asennettu. Kaikki ohjelmat testattu.'),
('Laite valmis — SMS',        'sms',         'Hei {nimi}! Laitteenne {laite} on valmis noudettavaksi. Aukioloajat: ma–pe 8–17. Tervetuloa!'),
('Huoltomuistutus — SMS',     'sms',         'Hei {nimi}! Muistutus: laitteenne vuosihuolto lähestyy. Varaa aika soittamalla tai vastaamalla tähän viestiin.'),
('Varausvahvistus — SMS',     'sms',         'Hei {nimi}! Ajanvarauksenne {pvm} klo {aika} on vahvistettu. Tervetuloa!');

-- Esimerkkivaraosat
INSERT INTO parts (sku, name, brand, part_type, manufacturer_name, supplier_id, cost_price, sell_price, vat_rate, stock_qty, stock_min, warehouse, shelf) VALUES
('BSH-00A123', 'Lämmitysvastus 2000W',        'Bosch/Siemens', 'Vastus',   'BSH',        1, 28.50,  49.00, 25.5, 3, 2, 'Iisalmi', 'Varasto A'),
('ELX-P0042',  'Pumppumoottori',               'Electrolux/AEG','Moottori', 'Electrolux', 2, 55.00,  89.00, 25.5, 1, 2, 'Iisalmi', 'Varasto A'),
('MIL-T0088',  'Oventiiviste Miele W1',        'Miele',         'Tiiviste', 'Miele',      3, 22.00,  38.00, 25.5, 4, 2, 'Iisalmi', 'Liike'),
('BSH-00B210', 'Oventiiviste astianpesukone',  'Bosch/Siemens', 'Tiiviste', 'BSH',        1, 18.50,  31.00, 25.5, 0, 3, 'Iisalmi', 'Liike'),
('UNI-B0005',  'Laakerisarja 6204ZZ',          'Universaali',   'Laakeri',  NULL,         4,  8.00,  16.00, 25.5, 6, 3, 'Iisalmi', 'Varasto B');

SET foreign_key_checks = 1;

-- ============================================================
-- HYÖDYLLISET NÄKYMÄT (VIEWS)
-- ============================================================

-- Avoimet työmääräimet yhteenvedolla
CREATE OR REPLACE VIEW v_open_orders AS
SELECT
  wo.id, wo.order_number, wo.status, wo.priority,
  wo.customer_name, wo.customer_phone, wo.customer_email,
  wo.device_brand, wo.device_model, wo.serial_number,
  wo.work_type, wo.is_warranty, wo.importer,
  m.name AS mechanic_name,
  wo.created_at, wo.suggested_service_date,
  COALESCE(SUM(op.total_gross), 0) AS estimated_total_gross
FROM work_orders wo
LEFT JOIN mechanics m ON wo.mechanic_id = m.id
LEFT JOIN order_parts op ON op.order_id = wo.id
WHERE wo.status NOT IN ('luovutettu','peruttu')
GROUP BY wo.id;

-- Varastoalertit
CREATE OR REPLACE VIEW v_low_stock AS
SELECT
  p.id, p.sku, p.name, p.brand, p.stock_qty, p.stock_min,
  p.sell_price, p.warehouse, p.shelf,
  s.name AS supplier_name, s.email AS supplier_email,
  CASE WHEN p.stock_qty = 0 THEN 'loppu' ELSE 'vähissä' END AS alert_type
FROM parts p
LEFT JOIN suppliers s ON p.supplier_id = s.id
WHERE p.stock_qty <= p.stock_min AND p.is_active = 1;

-- Teknikkotuntiraportti
CREATE OR REPLACE VIEW v_mechanic_hours AS
SELECT
  m.id, m.name AS mechanic_name,
  COUNT(DISTINCT tl.order_id) AS orders_count,
  SUM(tl.hours) AS total_hours,
  SUM(tl.km) AS total_km,
  SUM(tl.hours * m.hourly_rate) AS labor_value
FROM mechanics m
LEFT JOIN time_logs tl ON tl.mechanic_id = m.id
WHERE m.is_active = 1
GROUP BY m.id;

-- Takuutyöraportti
CREATE OR REPLACE VIEW v_warranty_orders AS
SELECT
  wo.order_number, wo.customer_name, wo.customer_phone,
  wo.device_brand, wo.device_model, wo.serial_number,
  wo.purchase_date, wo.warranty_end, wo.importer,
  wo.warranty_number, wo.status, wo.fault_code,
  wo.hours_logged, wo.hourly_rate,
  wo.hours_logged * wo.hourly_rate AS labor_cost,
  COALESCE(SUM(op.total_net), 0) AS parts_cost,
  (wo.hours_logged * wo.hourly_rate) + COALESCE(SUM(op.total_net), 0) AS total_claim,
  m.name AS mechanic_name,
  wo.created_at, wo.completed_at
FROM work_orders wo
LEFT JOIN mechanics m ON wo.mechanic_id = m.id
LEFT JOIN order_parts op ON op.order_id = wo.id AND op.product_type = 'varaosa'
WHERE wo.is_warranty = 1
GROUP BY wo.id;

-- POS vuororaportti
CREATE OR REPLACE VIEW v_pos_shift_report AS
SELECT
  shift_date,
  COUNT(*) AS sale_count,
  SUM(subtotal_net) AS total_net,
  SUM(vat_amount) AS total_vat,
  SUM(total_gross) AS total_gross,
  SUM(CASE WHEN payment_method='käteinen' THEN total_gross ELSE 0 END) AS cash_total,
  SUM(CASE WHEN payment_method='kortti' THEN total_gross ELSE 0 END) AS card_total,
  SUM(CASE WHEN payment_method='lasku' THEN total_gross ELSE 0 END) AS invoice_total
FROM pos_sales
GROUP BY shift_date
ORDER BY shift_date DESC;
