CREATE DATABASE IF NOT EXISTS tally_integration_test
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE tally_integration_test;

CREATE TABLE IF NOT EXISTS api_keys (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    api_key_hash CHAR(64) NOT NULL UNIQUE,
    client_name VARCHAR(150) NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS vouchers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vchnum VARCHAR(100) NOT NULL,
    vchtype VARCHAR(100) NOT NULL,
    vchdt DATETIME NOT NULL,
    vchremarks TEXT NULL,
    ledgername VARCHAR(255) NOT NULL,
    accountid VARCHAR(100) NULL,
    ledamt DECIMAL(18,2) NOT NULL DEFAULT 0,
    ccname VARCHAR(255) NOT NULL DEFAULT '',
    ccamt DECIMAL(18,2) NOT NULL DEFAULT 0,
    billtype VARCHAR(100) NOT NULL DEFAULT '',
    billname VARCHAR(100) NOT NULL DEFAULT '',
    billduedt DATE NULL,
    billamt DECIMAL(18,2) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_voucher_line (vchnum, vchtype, vchdt, ledgername, accountid, billname),
    INDEX idx_vchdt (vchdt),
    INDEX idx_vchnum (vchnum),
    INDEX idx_accountid (accountid)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS suppliers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    accountname VARCHAR(255) NOT NULL,
    accountid VARCHAR(100) NOT NULL,
    parent VARCHAR(255) NOT NULL,
    Address TEXT NULL,
    mobile VARCHAR(255) NULL,
    isbillwise VARCHAR(20) NOT NULL DEFAULT 'no',
    creditdays VARCHAR(50) NOT NULL DEFAULT '',
    checkcreditdays VARCHAR(20) NOT NULL DEFAULT 'no',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_supplier_accountid (accountid)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS cancelled_vouchers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vchnum VARCHAR(100) NOT NULL UNIQUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS api_request_log (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    endpoint VARCHAR(100) NOT NULL,
    method VARCHAR(10) NOT NULL,
    client_ip VARCHAR(45) NULL,
    request_time DATETIME NOT NULL,
    from_time DATETIME NULL,
    to_time DATETIME NULL,
    records_received INT NOT NULL DEFAULT 0,
    records_inserted INT NOT NULL DEFAULT 0,
    records_duplicate INT NOT NULL DEFAULT 0,
    status_code SMALLINT NOT NULL,
    error_message TEXT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_request_time (request_time),
    INDEX idx_endpoint (endpoint)
) ENGINE=InnoDB;

INSERT INTO api_keys (api_key_hash, client_name)
VALUES (SHA2('TEST-TALLY-API-KEY-2026',256), 'Tally Integration Test Client')
ON DUPLICATE KEY UPDATE client_name=VALUES(client_name), is_active=1;

INSERT INTO vouchers
(vchnum,vchtype,vchdt,vchremarks,ledgername,accountid,ledamt,ccname,ccamt,billtype,billname,billduedt,billamt)
VALUES
('2026004657','RTL-JOURNAL','2026-08-29 00:00:00',NULL,'CIRCULAR KNITTING',NULL,-69425.00,'GARMENTS SECTION',-69425.00,'','',NULL,0.00),
('2026004657','RTL-JOURNAL','2026-08-29 00:00:00',NULL,'NRG Composite Yarn Dyeing','4000481',69425.00,'',0.00,'NEW REF','1226010442','2026-11-27',69425.00),
('2026004661','RTL-JOURNAL','2026-08-31 00:00:00',NULL,'CIRCULAR KNITTING',NULL,-100520.00,'GARMENTS SECTION',-100520.00,'','',NULL,0.00),
('2026004661','RTL-JOURNAL','2026-08-31 00:00:00',NULL,'RAHMAN KNIT WEAR','4000896',100520.00,'',0.00,'NEW REF','16389','2026-11-29',100520.00),
('2026004659','RTL-JOURNAL','2026-08-30 00:00:00',NULL,'CIRCULAR KNITTING',NULL,-129360.00,'GARMENTS SECTION',-129360.00,'','',NULL,0.00),
('2026004659','RTL-JOURNAL','2026-08-30 00:00:00',NULL,'RAHMAN KNIT WEAR','4000896',129360.00,'',0.00,'NEW REF','16382','2026-11-28',129360.00),
('2026004662','RTL-JOURNAL','2026-09-15 00:00:00','THIS IS A "TEST" NARRATION WE ARE CREATING, IN ORDER ? TO TEST 1234567890 +-*/ DO YOU HAVE UPDATES >','CIRCULAR KNITTING',NULL,-6150.00,'GARMENTS SECTION',-6150.00,'','',NULL,0.00),
('2026004662','RTL-JOURNAL','2026-09-15 00:00:00','THIS IS A "TEST" NARRATION WE ARE CREATING, IN ORDER ? TO TEST 1234567890 +-*/ DO YOU HAVE UPDATES >','A & T FLAT KNIT INDUSTRY','4000918',6150.00,'',0.00,'NEW REF','477896','2027-01-13',6150.00);

INSERT INTO suppliers
(accountname,accountid,parent,Address,mobile,isbillwise,creditdays,checkcreditdays)
VALUES
('A.M THREAD & ACCESSORIES','4000949','Accounts Payable','ESRAR NOGOR,PARIJAT ROAD,KONABARI,GAZIPUR MOHANOGOR. GAZIPUR.','0163167317,0162742998 123','no','90','yes'),
('A & T FLAT KNIT INDUSTRY','4000918','Accounts Payable','DEWAN EDDRISH ROAD. ZIRABO, ASHULIA, SAVAR, DHAKA NAT.','01712542255','yes','120','yes'),
('PRITOM FASHION WEARS','4000526','Accounts Payable','FACTORY: ZIRABO, SASHULIA, SAVAR, DHAKA, BANGLADESH, BANGLADESH    HOUSE 528/C, KHILGAON, DHAKA','008801711522177','yes','90','yes'),
('MONTRIMS LTD.','4000430','Accounts Payable','SIAAM TOWER (LEVEL 9TH), PLOT #15, "DHAKA" MYMENSINGH ''BYPAS'' ROAD, SECTOR #3, UTTARA MODEL TOWN, UTTARA, DHAKA','+8801938887036','yes','90','yes')
ON DUPLICATE KEY UPDATE
accountname=VALUES(accountname), parent=VALUES(parent), Address=VALUES(Address),
mobile=VALUES(mobile), isbillwise=VALUES(isbillwise), creditdays=VALUES(creditdays),
checkcreditdays=VALUES(checkcreditdays);

INSERT INTO cancelled_vouchers (vchnum)
VALUES ('2026004648'),('2026004653'),('2026004633'),('2026004631')
ON DUPLICATE KEY UPDATE vchnum=VALUES(vchnum);