

CREATE TABLE IF NOT EXISTS locations (
 id INT AUTO_INCREMENT PRIMARY KEY,
 parent_id INT NULL,
 location_code VARCHAR(40) NOT NULL UNIQUE,
 name VARCHAR(160) NOT NULL,
 type ENUM('site','room','zone','rack','shelf','bin','vehicle','person','customer_site') NOT NULL DEFAULT 'bin',
 barcode VARCHAR(80) NOT NULL,
 qr_payload VARCHAR(120) NOT NULL,
 capacity DECIMAL(10,2) NULL,
 status ENUM('active','full','blocked','inactive') NOT NULL DEFAULT 'active',
 description TEXT NULL,
 path VARCHAR(500) NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(parent_id), INDEX(location_code), INDEX(type),
 CONSTRAINT fk_locations_parent FOREIGN KEY(parent_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS assets (
 id INT AUTO_INCREMENT PRIMARY KEY,
 asset_code VARCHAR(40) NOT NULL UNIQUE,
 name VARCHAR(190) NOT NULL,
 category VARCHAR(120) NULL,
 asset_type ENUM('asset','tool','device','customer_item','kit','fixture') NOT NULL DEFAULT 'asset',
 brand VARCHAR(120) NULL,
 model VARCHAR(120) NULL,
 serial_number VARCHAR(160) NULL,
 condition_status ENUM('new','good','used','damaged','faulty','repair') NOT NULL DEFAULT 'good',
 status ENUM('available','in_use','reserved','repair','missing','retired','disposed') NOT NULL DEFAULT 'available',
 current_location_id INT NULL,
 assigned_to VARCHAR(160) NULL,
 barcode VARCHAR(80) NOT NULL,
 qr_payload VARCHAR(120) NOT NULL,
 notes TEXT NULL,
 purchase_value DECIMAL(10,2) NULL,
 warranty_expiry DATE NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(asset_code), INDEX(serial_number), INDEX(status), INDEX(category), INDEX(current_location_id),
 CONSTRAINT fk_assets_location FOREIGN KEY(current_location_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS asset_movements (
 id INT AUTO_INCREMENT PRIMARY KEY,
 asset_id INT NOT NULL,
 from_location_id INT NULL,
 to_location_id INT NULL,
 user_id INT NULL,
 movement_type ENUM('received','move','putaway','picked','assigned','returned','transfer','repair','disposed','api_move','audit_adjustment') NOT NULL DEFAULT 'move',
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(asset_id), INDEX(from_location_id), INDEX(to_location_id), INDEX(user_id), INDEX(movement_type),
 CONSTRAINT fk_mov_asset FOREIGN KEY(asset_id) REFERENCES assets(id) ON DELETE CASCADE,
 CONSTRAINT fk_mov_from FOREIGN KEY(from_location_id) REFERENCES locations(id) ON DELETE SET NULL,
 CONSTRAINT fk_mov_to FOREIGN KEY(to_location_id) REFERENCES locations(id) ON DELETE SET NULL,
 CONSTRAINT fk_mov_user FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS inventory_items (
 id INT AUTO_INCREMENT PRIMARY KEY,
 sku VARCHAR(80) NOT NULL UNIQUE,
 name VARCHAR(190) NOT NULL,
 category VARCHAR(120) NULL,
 unit VARCHAR(40) NOT NULL DEFAULT 'each',
 qty_on_hand DECIMAL(12,2) NOT NULL DEFAULT 0,
 reorder_level DECIMAL(12,2) NOT NULL DEFAULT 0,
 location_id INT NULL,
 notes TEXT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(sku), INDEX(category), INDEX(location_id),
 CONSTRAINT fk_inventory_location FOREIGN KEY(location_id) REFERENCES locations(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reservations (
 id INT AUTO_INCREMENT PRIMARY KEY,
 asset_id INT NOT NULL,
 reserved_for VARCHAR(190) NOT NULL,
 reference VARCHAR(120) NULL,
 status ENUM('active','fulfilled','cancelled','expired') NOT NULL DEFAULT 'active',
 created_by INT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 INDEX(asset_id), INDEX(status), INDEX(reference),
 CONSTRAINT fk_res_asset FOREIGN KEY(asset_id) REFERENCES assets(id) ON DELETE CASCADE,
 CONSTRAINT fk_res_user FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audits (
 id INT AUTO_INCREMENT PRIMARY KEY,
 location_id INT NOT NULL,
 started_by INT NULL,
 completed_by INT NULL,
 status ENUM('open','complete','failed','cancelled') NOT NULL DEFAULT 'open',
 started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 completed_at TIMESTAMP NULL,
 notes TEXT NULL,
 INDEX(location_id), INDEX(status),
 CONSTRAINT fk_audit_location FOREIGN KEY(location_id) REFERENCES locations(id) ON DELETE CASCADE,
 CONSTRAINT fk_audit_started FOREIGN KEY(started_by) REFERENCES users(id) ON DELETE SET NULL,
 CONSTRAINT fk_audit_completed FOREIGN KEY(completed_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS audit_scans (
 id INT AUTO_INCREMENT PRIMARY KEY,
 audit_id INT NOT NULL,
 asset_id INT NULL,
 scanned_code VARCHAR(120) NOT NULL,
 result ENUM('matched','unexpected','unknown','missing') NOT NULL DEFAULT 'matched',
 scanned_by INT NULL,
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 INDEX(audit_id), INDEX(asset_id),
 CONSTRAINT fk_audit_scan_audit FOREIGN KEY(audit_id) REFERENCES audits(id) ON DELETE CASCADE,
 CONSTRAINT fk_audit_scan_asset FOREIGN KEY(asset_id) REFERENCES assets(id) ON DELETE SET NULL,
 CONSTRAINT fk_audit_scan_user FOREIGN KEY(scanned_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO locations(location_code,name,type,barcode,qr_payload,capacity,status,description,path) VALUES
('LOC-000001','Next Theme Shop','site','LOC-000001','LOC:LOC-000001',NULL,'active','Main operational site','LOC-000001'),
('LOC-000002','Back Room','room','LOC-000002','LOC:LOC-000002',NULL,'active','Shop storage room','LOC-000001 > LOC-000002'),
('LOC-000003','Rack A','rack','LOC-000003','LOC:LOC-000003',NULL,'active','Primary storage rack','LOC-000001 > LOC-000002 > LOC-000003'),
('LOC-000004','Shelf A1 / Bin 01','bin','LOC-000004','LOC:LOC-000004',20,'active','General small assets','LOC-000001 > LOC-000002 > LOC-000003 > LOC-000004')
ON DUPLICATE KEY UPDATE location_code=location_code;

UPDATE locations child JOIN locations parent ON child.location_code='LOC-000002' AND parent.location_code='LOC-000001' SET child.parent_id=parent.id;
UPDATE locations child JOIN locations parent ON child.location_code='LOC-000003' AND parent.location_code='LOC-000002' SET child.parent_id=parent.id;
UPDATE locations child JOIN locations parent ON child.location_code='LOC-000004' AND parent.location_code='LOC-000003' SET child.parent_id=parent.id;
