--
-- Host: localhost    Database: payroll_app
-- ------------------------------------------------------


--
-- Table structure for table `cash_count_rows`
--

CREATE TABLE `cash_count_rows` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cash_id` int(11) NOT NULL,
  `bill` int(11) NOT NULL,
  `qty` int(11) NOT NULL DEFAULT '0',
  `division` decimal(12,2) NOT NULL DEFAULT '0.00',
  `total` decimal(12,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`id`),
  KEY `cash_id` (`cash_id`),
  CONSTRAINT `cash_count_rows_ibfk_1` FOREIGN KEY (`cash_id`) REFERENCES `cash_counts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `cash_count_rows`
--


--
-- Table structure for table `cash_counts`
--

CREATE TABLE `cash_counts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `employees_base` int(11) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `cash_counts`
--


--
-- Table structure for table `employees`
--

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(120) NOT NULL,
  `doc_id` varchar(30) DEFAULT NULL,
  `phone` varchar(30) DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `position` varchar(80) DEFAULT NULL,
  `base_salary` decimal(12,2) NOT NULL DEFAULT '0.00',
  `commission_type` varchar(50) DEFAULT '0.0',
  `pay_type` varchar(50) DEFAULT NULL,
  `bank` varchar(80) DEFAULT NULL,
  `bank_doc` varchar(30) DEFAULT NULL,
  `bank_phone` varchar(30) DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `employees`
--


--
-- Table structure for table `payroll_lines`
--

CREATE TABLE `payroll_lines` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payroll_id` int(11) NOT NULL,
  `employee_id` int(11) NOT NULL,
  `salary` decimal(12,2) NOT NULL DEFAULT '0.00',
  `commission` decimal(12,2) NOT NULL DEFAULT '0.00',
  `bonus` decimal(12,2) NOT NULL DEFAULT '0.00',
  `discount` decimal(12,2) NOT NULL DEFAULT '0.00',
  `pay_usd` decimal(12,2) NOT NULL DEFAULT '0.00',
  `pay_cash` decimal(12,2) NOT NULL DEFAULT '0.00',
  `pay_transfer` decimal(12,2) NOT NULL DEFAULT '0.00',
  `line_total` decimal(12,2) NOT NULL DEFAULT '0.00',
  `note` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `payroll_id` (`payroll_id`),
  KEY `employee_id` (`employee_id`),
  CONSTRAINT `payroll_lines_ibfk_1` FOREIGN KEY (`payroll_id`) REFERENCES `payrolls` (`id`) ON DELETE CASCADE,
  CONSTRAINT `payroll_lines_ibfk_2` FOREIGN KEY (`employee_id`) REFERENCES `employees` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `payroll_lines`
--


--
-- Table structure for table `payrolls`
--

CREATE TABLE `payrolls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `period_start` date NOT NULL,
  `period_end` date NOT NULL,
  `fx_rate` decimal(12,4) NOT NULL DEFAULT '1.0000',
  `notes` text,
  `total_usd` decimal(14,2) DEFAULT '0.00',
  `total_cash` decimal(14,2) DEFAULT '0.00',
  `total_transfer` decimal(14,2) DEFAULT '0.00',
  `total_payroll` decimal(14,2) DEFAULT '0.00',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `payrolls`
--


--
-- Dumping events for database 'payroll_app'
--

--
-- Dumping routines for database 'payroll_app'
--


