-- ============================================================
--  DATABASE: struk_app  (CLEAN INSTALL — jalankan sekali)
-- ============================================================
CREATE DATABASE IF NOT EXISTS `struk_app`
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `struk_app`;

-- ── users ──────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `users` (
  `id`         INT          NOT NULL AUTO_INCREMENT,
  `name`       VARCHAR(100) NOT NULL,
  `username`   VARCHAR(50)  NOT NULL,
  `password`   VARCHAR(255) NOT NULL,
  `role`       ENUM('admin','user') NOT NULL DEFAULT 'user',
  `is_active`  TINYINT(1)   NOT NULL DEFAULT 1,
  `last_login` DATETIME     NULL,
  `created_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- admin/password  &  user1/password  (bcrypt hash "password")
INSERT INTO `users` (`name`,`username`,`password`,`role`) VALUES
('Administrator','admin','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','admin'),
('User Demo',   'user1','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','user');

-- ── stores ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `stores` (
  `id`          INT          NOT NULL AUTO_INCREMENT,
  `user_id`     INT          NOT NULL DEFAULT 1,
  `name`        VARCHAR(100) NOT NULL,
  `address`     VARCHAR(255) NOT NULL DEFAULT '',
  `phone`       VARCHAR(30)  NOT NULL DEFAULT '',
  `footer_text` VARCHAR(255) NOT NULL DEFAULT 'Terima kasih atas kunjungan Anda',
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_store_user` (`user_id`),
  CONSTRAINT `fk_store_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `stores` (`user_id`,`name`,`address`,`phone`,`footer_text`) VALUES
(1,'Toko Admin','Jl. Contoh No. 1, Kota','08123456789','Terima kasih, selamat datang kembali!'),
(2,'Toko User1','Jl. Demo No. 2, Kota', '08987654321','Terima kasih sudah berbelanja!');

-- ── receipt_templates ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS `receipt_templates` (
  `id`          INT          NOT NULL AUTO_INCREMENT,
  `user_id`     INT          NOT NULL DEFAULT 1,
  `store_id`    INT          NOT NULL DEFAULT 1,
  `name`        VARCHAR(100) NOT NULL,
  `paper_width` INT          NOT NULL DEFAULT 58,
  `layout_json` LONGTEXT     NOT NULL,
  `is_default`  TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_tpl_user`  (`user_id`),
  KEY `idx_tpl_store` (`store_id`),
  CONSTRAINT `fk_tpl_user`  FOREIGN KEY (`user_id`)  REFERENCES `users`(`id`)  ON DELETE CASCADE,
  CONSTRAINT `fk_tpl_store` FOREIGN KEY (`store_id`) REFERENCES `stores`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `receipt_templates` (`user_id`,`store_id`,`name`,`paper_width`,`layout_json`,`is_default`) VALUES
(1,1,'Template Default',58,
'[
  {"id":1,"element_type":"text","label":"store_name","value":"{{store_name}}","x":0,"y":0,"font_size":2,"align":"center","bold":1,"date_format":""},
  {"id":2,"element_type":"text","label":"store_address","value":"{{store_address}}","x":0,"y":1,"font_size":1,"align":"center","bold":0,"date_format":""},
  {"id":3,"element_type":"text","label":"store_phone","value":"Telp: {{store_phone}}","x":0,"y":2,"font_size":1,"align":"center","bold":0,"date_format":""},
  {"id":4,"element_type":"line","label":"separator","value":"","x":0,"y":3,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":5,"element_type":"text","label":"invoice_no","value":"No   : {{invoice_no}}","x":0,"y":4,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":6,"element_type":"text","label":"date","value":"Tgl  : {{date}}","x":0,"y":5,"font_size":1,"align":"left","bold":0,"date_format":"d/m/Y H:i"},
  {"id":7,"element_type":"text","label":"cashier","value":"Kasir: {{cashier}}","x":0,"y":6,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":8,"element_type":"line","label":"separator","value":"","x":0,"y":7,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":9,"element_type":"text","label":"items","value":"{{items}}","x":0,"y":8,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":10,"element_type":"line","label":"separator","value":"","x":0,"y":9,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":11,"element_type":"text","label":"subtotal","value":"Subtotal       {{subtotal}}","x":0,"y":10,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":12,"element_type":"text","label":"discount","value":"Diskon         {{discount}}","x":0,"y":11,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":13,"element_type":"text","label":"tax","value":"Pajak          {{tax}}","x":0,"y":12,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":14,"element_type":"line","label":"separator","value":"","x":0,"y":13,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":15,"element_type":"text","label":"total","value":"TOTAL          {{total}}","x":0,"y":14,"font_size":2,"align":"left","bold":1,"date_format":""},
  {"id":16,"element_type":"line","label":"separator","value":"","x":0,"y":15,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":17,"element_type":"text","label":"footer","value":"{{footer}}","x":0,"y":16,"font_size":1,"align":"center","bold":0,"date_format":""}
]',1),
(2,2,'Template User1',58,
'[
  {"id":1,"element_type":"text","label":"store_name","value":"{{store_name}}","x":0,"y":0,"font_size":2,"align":"center","bold":1,"date_format":""},
  {"id":2,"element_type":"line","label":"separator","value":"","x":0,"y":1,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":3,"element_type":"text","label":"invoice_no","value":"No: {{invoice_no}}","x":0,"y":2,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":4,"element_type":"text","label":"date","value":"Tgl: {{date}}","x":0,"y":3,"font_size":1,"align":"left","bold":0,"date_format":"d/m/Y H:i"},
  {"id":5,"element_type":"line","label":"separator","value":"","x":0,"y":4,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":6,"element_type":"text","label":"items","value":"{{items}}","x":0,"y":5,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":7,"element_type":"line","label":"separator","value":"","x":0,"y":6,"font_size":1,"align":"left","bold":0,"date_format":""},
  {"id":8,"element_type":"text","label":"total","value":"TOTAL  {{total}}","x":0,"y":7,"font_size":2,"align":"left","bold":1,"date_format":""},
  {"id":9,"element_type":"text","label":"footer","value":"{{footer}}","x":0,"y":8,"font_size":1,"align":"center","bold":0,"date_format":""}
]',1);

-- ── template_elements ──────────────────────────────────────
CREATE TABLE IF NOT EXISTS `template_elements` (
  `id`           INT          NOT NULL AUTO_INCREMENT,
  `template_id`  INT          NOT NULL,
  `element_type` ENUM('text','line','barcode','qr') NOT NULL DEFAULT 'text',
  `label`        VARCHAR(100) NOT NULL DEFAULT '',
  `value`        TEXT         NOT NULL,
  `x`            INT          NOT NULL DEFAULT 0,
  `y`            INT          NOT NULL DEFAULT 0,
  `font_size`    TINYINT      NOT NULL DEFAULT 1,
  `align`        ENUM('left','center','right') NOT NULL DEFAULT 'left',
  `bold`         TINYINT(1)   NOT NULL DEFAULT 0,
  `sort_order`   INT          NOT NULL DEFAULT 0,
  `date_format`  VARCHAR(30)  NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `idx_elem_tpl` (`template_id`),
  CONSTRAINT `fk_elem_tpl` FOREIGN KEY (`template_id`) REFERENCES `receipt_templates`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── transactions ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `transactions` (
  `id`          INT            NOT NULL AUTO_INCREMENT,
  `user_id`     INT            NOT NULL DEFAULT 1,
  `store_id`    INT            NOT NULL DEFAULT 1,
  `template_id` INT            NOT NULL DEFAULT 1,
  `invoice_no`  VARCHAR(50)    NOT NULL,
  `items_json`  LONGTEXT       NOT NULL,
  `subtotal`    DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
  `discount`    DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
  `tax`         DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
  `total`       DECIMAL(12,2)  NOT NULL DEFAULT 0.00,
  `cashier`     VARCHAR(100)   NOT NULL DEFAULT 'Admin',
  `trx_date`    DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `note`        VARCHAR(255)   NOT NULL DEFAULT '',
  `created_at`  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`  TIMESTAMP      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_invoice` (`invoice_no`),
  KEY `idx_trx_user`     (`user_id`),
  KEY `idx_trx_store`    (`store_id`),
  KEY `idx_trx_template` (`template_id`),
  CONSTRAINT `fk_trx_user`     FOREIGN KEY (`user_id`)     REFERENCES `users`(`id`)             ON DELETE CASCADE,
  CONSTRAINT `fk_trx_store`    FOREIGN KEY (`store_id`)    REFERENCES `stores`(`id`)             ON DELETE CASCADE,
  CONSTRAINT `fk_trx_template` FOREIGN KEY (`template_id`) REFERENCES `receipt_templates`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Contoh transaksi
INSERT INTO `transactions`
  (`user_id`,`store_id`,`template_id`,`invoice_no`,`items_json`,`subtotal`,`discount`,`tax`,`total`,`cashier`,`trx_date`)
VALUES
(1,1,1,'INV-20260321-0001',
 '[{"name":"Kopi Susu","qty":2,"price":15000},{"name":"Roti Bakar","qty":1,"price":12000}]',
 42000,0,0,42000,'Admin', NOW()),
(2,2,2,'INV-20260321-0002',
 '[{"name":"Nasi Goreng","qty":1,"price":25000}]',
 25000,0,0,25000,'Kasir1', NOW());
