-- phpMyAdmin SQL Dump
-- version 5.2.1deb1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Dec 26, 2023 at 11:42 AM
-- Server version: 10.11.3-MariaDB-1
-- PHP Version: 8.2.7

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `trackme`
--

-- --------------------------------------------------------

--
-- Table structure for table `accounts`
--

CREATE TABLE `accounts` (
  `account_id` int(11) NOT NULL,
  `account_name` varchar(128) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `colors`
--

CREATE TABLE `colors` (
  `color_id` int(11) NOT NULL,
  `color_factor` int(11) NOT NULL,
  `color_abbrev` varchar(5) NOT NULL,
  `color_cast` varchar(32) NOT NULL,
  `color_round` tinyint(4) NOT NULL DEFAULT 8
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `owners`
--

CREATE TABLE `owners` (
  `owner_id` int(11) NOT NULL,
  `owner_name` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `owners2wallets`
--

CREATE TABLE `owners2wallets` (
  `owner2wallet_id` int(11) NOT NULL,
  `owner_id` int(11) NOT NULL,
  `wallet_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `paths`
--

CREATE TABLE `paths` (
  `path_id` int(11) NOT NULL,
  `xpub_id` int(11) NOT NULL,
  `leaf` varchar(16) NOT NULL,
  `gap_limit` int(11) NOT NULL DEFAULT 20
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `prices`
--

CREATE TABLE `prices` (
  `price_id` int(11) NOT NULL,
  `price_date` date NOT NULL,
  `price_usd` decimal(20,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `scriptpubkeys`
--

CREATE TABLE `scriptpubkeys` (
  `scriptpubkey_id` int(11) NOT NULL,
  `scriptpubkey` varbinary(10000) NOT NULL,
  `reverse_scriptpubkey_hash` varchar(64) DEFAULT NULL,
  `scriptpubkey_status` varchar(64) NOT NULL DEFAULT '',
  `scriptpubkey_desc` varchar(128) DEFAULT NULL,
  `address` varchar(64) DEFAULT NULL,
  `walletpath_id` int(11) DEFAULT NULL,
  `idx` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `txids`
--

CREATE TABLE `txids` (
  `txid_id` int(11) NOT NULL,
  `txid` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `txos`
--

CREATE TABLE `txos` (
  `txo_id` int(11) NOT NULL,
  `txo_value` decimal(16,8) NOT NULL,
  `scriptpubkey_id` int(11) DEFAULT NULL,
  `create_txid_id` int(11) NOT NULL,
  `create_txo_index` int(11) NOT NULL,
  `destroy_txid_id` int(11) DEFAULT NULL,
  `destroy_txo_index` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `txos2accounts`
--

CREATE TABLE `txos2accounts` (
  `txo2account_id` int(11) NOT NULL,
  `txo_id` int(11) NOT NULL,
  `account_id` int(11) NOT NULL,
  `amount` decimal(16,8) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `txs`
--

CREATE TABLE `txs` (
  `tx_id` int(11) NOT NULL,
  `txid_id` int(11) NOT NULL,
  `tx_desc` varchar(512) DEFAULT NULL,
  `tx_fee` decimal(16,8) NOT NULL,
  `tx_blocktime` datetime NOT NULL,
  `tx_height` int(11) NOT NULL,
  `tx_pos` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `walletpaths`
--

CREATE TABLE `walletpaths` (
  `walletpath_id` int(11) NOT NULL,
  `wallet_id` int(11) NOT NULL,
  `path_id` int(11) DEFAULT NULL,
  `color_id` int(11) NOT NULL DEFAULT 1,
  `walletpath_desc` varchar(32) NOT NULL DEFAULT 'Default'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `wallets`
--

CREATE TABLE `wallets` (
  `wallet_id` int(11) NOT NULL,
  `wallet_desc` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `xpubs`
--

CREATE TABLE `xpubs` (
  `xpub_id` int(11) NOT NULL,
  `xpub` varchar(120) NOT NULL,
  `root` varchar(32) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `accounts`
--
ALTER TABLE `accounts`
  ADD PRIMARY KEY (`account_id`),
  ADD UNIQUE KEY `account_name` (`account_name`);

--
-- Indexes for table `colors`
--
ALTER TABLE `colors`
  ADD PRIMARY KEY (`color_id`);

--
-- Indexes for table `owners`
--
ALTER TABLE `owners`
  ADD PRIMARY KEY (`owner_id`);

--
-- Indexes for table `owners2wallets`
--
ALTER TABLE `owners2wallets`
  ADD PRIMARY KEY (`owner2wallet_id`),
  ADD UNIQUE KEY `owner_id` (`owner_id`,`wallet_id`),
  ADD KEY `wallet` (`wallet_id`);

--
-- Indexes for table `paths`
--
ALTER TABLE `paths`
  ADD PRIMARY KEY (`path_id`),
  ADD UNIQUE KEY `xpub_id` (`xpub_id`,`leaf`);

--
-- Indexes for table `prices`
--
ALTER TABLE `prices`
  ADD PRIMARY KEY (`price_id`),
  ADD UNIQUE KEY `price_date` (`price_date`);

--
-- Indexes for table `scriptpubkeys`
--
ALTER TABLE `scriptpubkeys`
  ADD PRIMARY KEY (`scriptpubkey_id`),
  ADD UNIQUE KEY `scriptpubkey` (`scriptpubkey`) USING HASH,
  ADD KEY `walletpath_id` (`walletpath_id`);

--
-- Indexes for table `txids`
--
ALTER TABLE `txids`
  ADD PRIMARY KEY (`txid_id`);

--
-- Indexes for table `txos`
--
ALTER TABLE `txos`
  ADD PRIMARY KEY (`txo_id`),
  ADD UNIQUE KEY `create_txid_id` (`create_txid_id`,`create_txo_index`),
  ADD UNIQUE KEY `destroy_txid_id` (`destroy_txid_id`,`destroy_txo_index`),
  ADD KEY `scriptpubkey_id` (`scriptpubkey_id`);

--
-- Indexes for table `txos2accounts`
--
ALTER TABLE `txos2accounts`
  ADD PRIMARY KEY (`txo2account_id`),
  ADD UNIQUE KEY `txo_id` (`txo_id`,`account_id`),
  ADD KEY `account_id` (`account_id`);

--
-- Indexes for table `txs`
--
ALTER TABLE `txs`
  ADD PRIMARY KEY (`tx_id`),
  ADD UNIQUE KEY `txid_id` (`txid_id`);

--
-- Indexes for table `walletpaths`
--
ALTER TABLE `walletpaths`
  ADD PRIMARY KEY (`walletpath_id`),
  ADD UNIQUE KEY `path_id` (`path_id`),
  ADD KEY `wallet_id` (`wallet_id`),
  ADD KEY `color_id` (`color_id`);

--
-- Indexes for table `wallets`
--
ALTER TABLE `wallets`
  ADD PRIMARY KEY (`wallet_id`);

--
-- Indexes for table `xpubs`
--
ALTER TABLE `xpubs`
  ADD PRIMARY KEY (`xpub_id`),
  ADD UNIQUE KEY `xpub` (`xpub`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `accounts`
--
ALTER TABLE `accounts`
  MODIFY `account_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `colors`
--
ALTER TABLE `colors`
  MODIFY `color_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `owners`
--
ALTER TABLE `owners`
  MODIFY `owner_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `owners2wallets`
--
ALTER TABLE `owners2wallets`
  MODIFY `owner2wallet_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `paths`
--
ALTER TABLE `paths`
  MODIFY `path_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `prices`
--
ALTER TABLE `prices`
  MODIFY `price_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `scriptpubkeys`
--
ALTER TABLE `scriptpubkeys`
  MODIFY `scriptpubkey_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `txids`
--
ALTER TABLE `txids`
  MODIFY `txid_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `txos`
--
ALTER TABLE `txos`
  MODIFY `txo_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `txos2accounts`
--
ALTER TABLE `txos2accounts`
  MODIFY `txo2account_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `txs`
--
ALTER TABLE `txs`
  MODIFY `tx_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `walletpaths`
--
ALTER TABLE `walletpaths`
  MODIFY `walletpath_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `wallets`
--
ALTER TABLE `wallets`
  MODIFY `wallet_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `xpubs`
--
ALTER TABLE `xpubs`
  MODIFY `xpub_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `owners2wallets`
--
ALTER TABLE `owners2wallets`
  ADD CONSTRAINT `owner_id` FOREIGN KEY (`owner_id`) REFERENCES `owners` (`owner_id`),
  ADD CONSTRAINT `wallet` FOREIGN KEY (`wallet_id`) REFERENCES `wallets` (`wallet_id`);

--
-- Constraints for table `paths`
--
ALTER TABLE `paths`
  ADD CONSTRAINT `xpub_id` FOREIGN KEY (`xpub_id`) REFERENCES `xpubs` (`xpub_id`);

--
-- Constraints for table `scriptpubkeys`
--
ALTER TABLE `scriptpubkeys`
  ADD CONSTRAINT `walletpath_id` FOREIGN KEY (`walletpath_id`) REFERENCES `walletpaths` (`walletpath_id`);

--
-- Constraints for table `txos`
--
ALTER TABLE `txos`
  ADD CONSTRAINT `create_tx` FOREIGN KEY (`create_txid_id`) REFERENCES `txids` (`txid_id`),
  ADD CONSTRAINT `destroy_tx` FOREIGN KEY (`destroy_txid_id`) REFERENCES `txids` (`txid_id`),
  ADD CONSTRAINT `scriptpubkey_id` FOREIGN KEY (`scriptpubkey_id`) REFERENCES `scriptpubkeys` (`scriptpubkey_id`);

--
-- Constraints for table `txos2accounts`
--
ALTER TABLE `txos2accounts`
  ADD CONSTRAINT `account_id` FOREIGN KEY (`account_id`) REFERENCES `accounts` (`account_id`),
  ADD CONSTRAINT `txo_id` FOREIGN KEY (`txo_id`) REFERENCES `txos` (`txo_id`);

--
-- Constraints for table `walletpaths`
--
ALTER TABLE `walletpaths`
  ADD CONSTRAINT `color_id` FOREIGN KEY (`color_id`) REFERENCES `colors` (`color_id`),
  ADD CONSTRAINT `path_id` FOREIGN KEY (`path_id`) REFERENCES `paths` (`path_id`),
  ADD CONSTRAINT `wallet_id` FOREIGN KEY (`wallet_id`) REFERENCES `wallets` (`wallet_id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
