-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Jul 17, 2025 at 05:11 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

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: `gstaccounting`
--

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

--
-- Table structure for table `additional_places_of_business`
--

CREATE TABLE `additional_places_of_business` (
  `id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `building_number` varchar(50) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `district` varchar(255) DEFAULT NULL,
  `state` varchar(50) DEFAULT NULL,
  `pincode` varchar(20) DEFAULT NULL,
  `floor_number` varchar(50) DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `companies`
--

CREATE TABLE `companies` (
  `id` int(11) NOT NULL,
  `company_uuid` char(36) DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `gst_number` varchar(15) NOT NULL,
  `legal_name` varchar(255) DEFAULT NULL,
  `trade_name` varchar(255) DEFAULT NULL,
  `business_type` varchar(100) DEFAULT NULL,
  `nature_of_business` varchar(255) DEFAULT NULL,
  `status` varchar(50) DEFAULT NULL,
  `registration_date` date DEFAULT NULL,
  `last_updated` timestamp NULL DEFAULT NULL,
  `frequency_type` varchar(50) DEFAULT NULL,
  `error_msg` varchar(255) DEFAULT NULL,
  `principal_building_number` varchar(50) DEFAULT NULL,
  `principal_street` varchar(255) DEFAULT NULL,
  `principal_location` varchar(255) DEFAULT NULL,
  `principal_district` varchar(255) DEFAULT NULL,
  `principal_state` varchar(50) DEFAULT NULL,
  `principal_pincode` varchar(20) DEFAULT NULL,
  `principal_floor_number` varchar(50) DEFAULT NULL,
  `principal_latitude` decimal(10,7) DEFAULT NULL,
  `principal_longitude` decimal(10,7) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `country_code` varchar(10) DEFAULT NULL,
  `currency_code` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `country`
--

CREATE TABLE `country` (
  `code` varchar(10) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `country`
--

INSERT INTO `country` (`code`, `name`) VALUES
('AD', 'Andorra'),
('AE', 'United Arab Emirates'),
('AF', 'Afghanistan'),
('AG', 'Antigua and Barbuda'),
('AI', 'Anguilla'),
('AL', 'Albania'),
('AM', 'Armenia'),
('AO', 'Angola'),
('AQ', 'Antarctica'),
('AR', 'Argentina'),
('AS', 'American Samoa'),
('AT', 'Austria'),
('AU', 'Australia'),
('AW', 'Aruba'),
('AX', 'Åland Islands'),
('AZ', 'Azerbaijan'),
('BA', 'Bosnia and Herzegovina'),
('BB', 'Barbados'),
('BD', 'Bangladesh'),
('BE', 'Belgium'),
('BF', 'Burkina Faso'),
('BG', 'Bulgaria'),
('BH', 'Bahrain'),
('BI', 'Burundi'),
('BJ', 'Benin'),
('BL', 'Saint Barthélemy'),
('BM', 'Bermuda'),
('BN', 'Brunei Darussalam'),
('BO', 'Bolivia (Plurinational State of)'),
('BQ', 'Bonaire, Sint Eustatius and Saba'),
('BR', 'Brazil'),
('BS', 'Bahamas'),
('BT', 'Bhutan'),
('BV', 'Bouvet Island'),
('BW', 'Botswana'),
('BY', 'Belarus'),
('BZ', 'Belize'),
('CA', 'Canada'),
('CC', 'Cocos (Keeling) Islands'),
('CD', 'Congo (Democratic Republic of the)'),
('CF', 'Central African Republic'),
('CG', 'Congo'),
('CH', 'Switzerland'),
('CI', 'Côte d\'Ivoire'),
('CK', 'Cook Islands'),
('CL', 'Chile'),
('CM', 'Cameroon'),
('CN', 'China'),
('CO', 'Colombia'),
('CR', 'Costa Rica'),
('CU', 'Cuba'),
('CV', 'Cabo Verde'),
('CW', 'Curaçao'),
('CX', 'Christmas Island'),
('CY', 'Cyprus'),
('CZ', 'Czechia'),
('DE', 'Germany'),
('DJ', 'Djibouti'),
('DK', 'Denmark'),
('DM', 'Dominica'),
('DO', 'Dominican Republic'),
('DZ', 'Algeria'),
('EC', 'Ecuador'),
('EE', 'Estonia'),
('EG', 'Egypt'),
('EH', 'Western Sahara'),
('ER', 'Eritrea'),
('ES', 'Spain'),
('ET', 'Ethiopia'),
('FI', 'Finland'),
('FJ', 'Fiji'),
('FM', 'Micronesia (Federated States of)'),
('FO', 'Faroe Islands'),
('FR', 'France'),
('GA', 'Gabon'),
('GB', 'United Kingdom of Great Britain and Northern Ireland'),
('GD', 'Grenada'),
('GE', 'Georgia'),
('GF', 'French Guiana'),
('GG', 'Guernsey'),
('GH', 'Ghana'),
('GI', 'Gibraltar'),
('GL', 'Greenland'),
('GM', 'Gambia'),
('GN', 'Guinea'),
('GP', 'Guadeloupe'),
('GQ', 'Equatorial Guinea'),
('GR', 'Greece'),
('GT', 'Guatemala'),
('GU', 'Guam'),
('GW', 'Guinea-Bissau'),
('GY', 'Guyana'),
('HK', 'Hong Kong'),
('HM', 'Heard Island and McDonald Islands'),
('HN', 'Honduras'),
('HR', 'Croatia'),
('HT', 'Haiti'),
('HU', 'Hungary'),
('ID', 'Indonesia'),
('IE', 'Ireland'),
('IL', 'Israel'),
('IM', 'Isle of Man'),
('IN', 'India'),
('IO', 'British Indian Ocean Territory'),
('IQ', 'Iraq'),
('IR', 'Iran (Islamic Republic of)'),
('IS', 'Iceland'),
('IT', 'Italy'),
('JE', 'Jersey'),
('JM', 'Jamaica'),
('JO', 'Jordan'),
('JP', 'Japan'),
('KE', 'Kenya'),
('KG', 'Kyrgyzstan'),
('KH', 'Cambodia'),
('KI', 'Kiribati'),
('KM', 'Comoros'),
('KN', 'Saint Kitts and Nevis'),
('KP', 'Korea (Democratic People\'s Republic of)'),
('KR', 'Korea (Republic of)'),
('KW', 'Kuwait'),
('KY', 'Cayman Islands'),
('KZ', 'Kazakhstan'),
('LA', 'Lao People\'s Democratic Republic'),
('LB', 'Lebanon'),
('LC', 'Saint Lucia'),
('LI', 'Liechtenstein'),
('LK', 'Sri Lanka'),
('LR', 'Liberia'),
('LS', 'Lesotho'),
('LT', 'Lithuania'),
('LU', 'Luxembourg'),
('LV', 'Latvia'),
('LY', 'Libya'),
('MA', 'Morocco'),
('MC', 'Monaco'),
('MD', 'Moldova (Republic of)'),
('ME', 'Montenegro'),
('MF', 'Saint Martin (French part)'),
('MG', 'Madagascar'),
('MH', 'Marshall Islands'),
('MK', 'North Macedonia'),
('ML', 'Mali'),
('MM', 'Myanmar'),
('MN', 'Mongolia'),
('MO', 'Macao'),
('MP', 'Northern Mariana Islands'),
('MQ', 'Martinique'),
('MR', 'Mauritania'),
('MS', 'Montserrat'),
('MT', 'Malta'),
('MU', 'Mauritius'),
('MV', 'Maldives'),
('MW', 'Malawi'),
('MX', 'Mexico'),
('MY', 'Malaysia'),
('MZ', 'Mozambique'),
('NA', 'Namibia'),
('NC', 'New Caledonia'),
('NE', 'Niger'),
('NF', 'Norfolk Island'),
('NG', 'Nigeria'),
('NI', 'Nicaragua'),
('NL', 'Netherlands'),
('NO', 'Norway'),
('NP', 'Nepal'),
('NR', 'Nauru'),
('NU', 'Niue'),
('NZ', 'New Zealand'),
('OM', 'Oman'),
('PA', 'Panama'),
('PE', 'Peru'),
('PF', 'French Polynesia'),
('PG', 'Papua New Guinea'),
('PH', 'Philippines'),
('PK', 'Pakistan'),
('PL', 'Poland'),
('PM', 'Saint Pierre and Miquelon'),
('PN', 'Pitcairn'),
('PR', 'Puerto Rico'),
('PS', 'Palestine, State of'),
('PT', 'Portugal'),
('PW', 'Palau'),
('PY', 'Paraguay'),
('QA', 'Qatar'),
('RE', 'Réunion'),
('RO', 'Romania'),
('RS', 'Serbia'),
('RU', 'Russian Federation'),
('RW', 'Rwanda'),
('SA', 'Saudi Arabia'),
('SB', 'Solomon Islands'),
('SC', 'Seychelles'),
('SD', 'Sudan'),
('SE', 'Sweden'),
('SG', 'Singapore'),
('SH', 'Saint Helena, Ascension and Tristan da Cunha'),
('SI', 'Slovenia'),
('SJ', 'Svalbard and Jan Mayen'),
('SK', 'Slovakia'),
('SL', 'Sierra Leone'),
('SM', 'San Marino'),
('SN', 'Senegal'),
('SO', 'Somalia'),
('SR', 'Suriname'),
('SS', 'South Sudan'),
('ST', 'Sao Tome and Principe'),
('SV', 'El Salvador'),
('SX', 'Sint Maarten (Dutch part)'),
('SY', 'Syrian Arab Republic'),
('SZ', 'Eswatini'),
('TC', 'Turks and Caicos Islands'),
('TD', 'Chad'),
('TF', 'French Southern Territories'),
('TG', 'Togo'),
('TH', 'Thailand'),
('TJ', 'Tajikistan'),
('TK', 'Tokelau'),
('TL', 'Timor-Leste'),
('TM', 'Turkmenistan'),
('TN', 'Tunisia'),
('TO', 'Tonga'),
('TR', 'Turkey'),
('TT', 'Trinidad and Tobago'),
('TV', 'Tuvalu'),
('TZ', 'Tanzania, United Republic of'),
('UA', 'Ukraine'),
('UG', 'Uganda'),
('UM', 'United States Minor Outlying Islands'),
('US', 'United States of America'),
('UY', 'Uruguay'),
('UZ', 'Uzbekistan'),
('VA', 'Holy See'),
('VC', 'Saint Vincent and the Grenadines'),
('VE', 'Venezuela (Bolivarian Republic of)'),
('VG', 'Virgin Islands (British)'),
('VI', 'Virgin Islands (U.S.)'),
('VN', 'Viet Nam'),
('VU', 'Vanuatu'),
('WF', 'Wallis and Futuna'),
('WS', 'Samoa'),
('YE', 'Yemen'),
('YT', 'Mayotte'),
('ZA', 'South Africa'),
('ZM', 'Zambia'),
('ZW', 'Zimbabwe');

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

--
-- Table structure for table `currency`
--

CREATE TABLE `currency` (
  `code` varchar(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `symbol` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `currency`
--

INSERT INTO `currency` (`code`, `name`, `symbol`) VALUES
('AED', 'United Arab Emirates dirham', NULL),
('AFN', 'Afghan afghani', NULL),
('ALL', 'Albanian lek', NULL),
('AMD', 'Armenian dram', NULL),
('ANG', 'Netherlands Antillen guilder', NULL),
('AOA', 'Angolan kwanza', NULL),
('ARS', 'Argentine peso', NULL),
('AUD', 'Australian dollar', NULL),
('AWG', 'Aruban florin', NULL),
('AZN', 'Azerbaijani manat', NULL),
('BAM', 'Bosnia and Herzegovina convertible mark', NULL),
('BBD', 'Barbados dollar', NULL),
('BDT', 'Bangladeshi taka', NULL),
('BGN', 'Bulgarian lev', NULL),
('BHD', 'Bahraini dinar', NULL),
('BIF', 'Burundian franc', NULL),
('BMD', 'Bermudian dollar', NULL),
('BND', 'Brunei dollar', NULL),
('BOB', 'Boliviano', NULL),
('BRL', 'Brazilian real', NULL),
('BSD', 'Bahamian dollar', NULL),
('BTN', 'Bhutanese ngultrum', NULL),
('BWP', 'Botswana pula', NULL),
('BYN', 'Belarusian ruble', NULL),
('BZD', 'Belize dollar', NULL),
('CAD', 'Canadian dollar', '$'),
('CDF', 'Congolese franc', NULL),
('CHF', 'WIR Euro (complementary currency)', NULL),
('CHW', 'WIR Franc (complementary currency)', NULL),
('CLF', 'Unidad de Fomento (funds code)', NULL),
('CLP', 'Chilean peso', NULL),
('CNY', 'Renminbi (Chinese) yuan', '¥'),
('COP', 'Colombian peso', NULL),
('COU', 'Unidad de Valor Real (UVR) (funds code)', NULL),
('CRC', 'Costa Rican colon', NULL),
('CUC', 'Cuban convertible peso', NULL),
('CUP', 'Cuban peso', NULL),
('CVE', 'Cape Verdean escudo', NULL),
('CZK', 'Czech koruna', NULL),
('DJF', 'Djiboutian franc', NULL),
('DKK', 'Danish krone', NULL),
('DOP', 'Dominican peso', NULL),
('DZD', 'Algerian dinar', NULL),
('EGP', 'Egyptian pound', NULL),
('ERN', 'Eritrean nakfa', NULL),
('ETB', 'Ethiopian birr', NULL),
('EUR', 'Euro', '€'),
('FJD', 'Fiji dollar', NULL),
('FKP', 'Falkland Islands pound', NULL),
('FOK', 'Faroese króna', NULL),
('GBP', 'British pound', '£'),
('GEL', 'Georgian lari', NULL),
('GHS', 'Ghanaian cedi', NULL),
('GIP', 'Gibraltar pound', NULL),
('GMD', 'Gambian dalasi', NULL),
('GNF', 'Guinean franc', NULL),
('GTQ', 'Guatemalan quetzal', NULL),
('GYD', 'Guyanaese dollar', NULL),
('HKD', 'Hong Kong dollar', '$'),
('HNL', 'Honduran lempira', NULL),
('HRK', 'Croatian kuna', NULL),
('HTG', 'Haitian gourde', NULL),
('HUF', 'Hungarian forint', NULL),
('IDR', 'Indonesian rupiah', NULL),
('ILS', 'Israeli new shekel', NULL),
('INR', 'Indian rupee', '₹'),
('IQD', 'Iraqi dinar', NULL),
('IRR', 'Iranian rial', NULL),
('ISK', 'Icelandic króna', NULL),
('JMD', 'Jamaican dollar', NULL),
('JOD', 'Jordanian dinar', NULL),
('JPY', 'Japanese yen', '¥'),
('KES', 'Kenyan shilling', NULL),
('KGS', 'Kyrgyzstani som', NULL),
('KHR', 'Cambodian riel', NULL),
('KID', 'Kiribati dollar', NULL),
('KMF', 'Comorian franc', NULL),
('KRW', 'South Korean won', '₩'),
('KWD', 'Kuwaiti dinar', NULL),
('KYD', 'Cayman Islands dollar', NULL),
('KZT', 'Kazakhstani tenge', NULL),
('LAK', 'Lao kip', NULL),
('LBP', 'Lebanese pound', NULL),
('LKR', 'Sri Lankan rupee', NULL),
('LRD', 'Liberian dollar', NULL),
('LSL', 'Lesotho loti', NULL),
('LYD', 'Libyan dinar', NULL),
('MAD', 'Moroccan dirham', NULL),
('MDL', 'Moldovan leu', NULL),
('MGA', 'Malagasy ariary', NULL),
('MKD', 'Macedonian denar', NULL),
('MMK', 'Myanmar kyat', NULL),
('MNT', 'Mongolian tögrög', NULL),
('MOP', 'Macanese pataca', NULL),
('MRU', 'Mauritanian ouguiya', NULL),
('MUR', 'Mauritian rupee', NULL),
('MVR', 'Maldivian rufiyaa', NULL),
('MWK', 'Malawian kwacha', NULL),
('MXN', 'Mexican peso', NULL),
('MYR', 'Malaysian ringgit', NULL),
('MZN', 'Mozambican metical', NULL),
('NAD', 'Namibian dollar', NULL),
('NGN', 'Nigerian naira', NULL),
('NIO', 'Nicaraguan córdoba', NULL),
('NOK', 'Norwegian krone', NULL),
('NPR', 'Nepalese rupee', NULL),
('NZD', 'New Zealand dollar', NULL),
('OMR', 'Omani rial', NULL),
('PAB', 'Panamanian balboa', NULL),
('PEN', 'Peruvian sol', NULL),
('PGK', 'Papua New Guinean kina', NULL),
('PHP', 'Philippine peso', NULL),
('PKR', 'Pakistani rupee', NULL),
('PLN', 'Polish zloty', NULL),
('PYG', 'Paraguayan guarani', NULL),
('QAR', 'Qatari riyal', NULL),
('RON', 'Romanian leu', NULL),
('RSD', 'Serbian dinar', NULL),
('RUB', 'Russian ruble', '₽'),
('RWF', 'Rwandan franc', NULL),
('SAR', 'Saudi riyal', NULL),
('SBD', 'Solomon Islands dollar', NULL),
('SCR', 'Seychelles rupee', NULL),
('SDG', 'Sudanese pound', NULL),
('SEK', 'Swedish krona/kronor', NULL),
('SGD', 'Singapore dollar', NULL),
('SHP', 'Saint Helena pound', NULL),
('SLL', 'Sierra Leonean leone', NULL),
('SOS', 'Somali shilling', NULL),
('SRD', 'Surinamese dollar', NULL),
('SSP', 'South Sudanese pound', NULL),
('STN', 'São Tomé and Príncipe dobra', NULL),
('SYP', 'Syrian pound', NULL),
('SZL', 'Swazi lilangeni', NULL),
('THB', 'Thai baht', '฿'),
('TJS', 'Tajikistani somoni', NULL),
('TMT', 'Turkmenistan manat', NULL),
('TND', 'Tunisian dinar', NULL),
('TOP', 'Tongan paʻanga', NULL),
('TRY', 'Turkish lira', NULL),
('TTD', 'Trinidad and Tobago dollar', NULL),
('TVD', 'Tuvaluan dollar', NULL),
('TZS', 'Tanzanian shilling', NULL),
('UAH', 'Ukrainian hryvnia', NULL),
('UGX', 'Ugandan shilling', NULL),
('USD', 'United States dollar', '$'),
('UYI', 'Uruguay Peso en Unidades Indexadas (URUIURUI)', NULL),
('UYU', 'Uruguayan peso', NULL),
('UZS', 'Uzbekistan som', NULL),
('VES', 'Venezuelan bolívar', NULL),
('VND', 'Vietnamese đồng', NULL),
('VUV', 'Vanuatu vatu', NULL),
('WST', 'Samoan tala', NULL),
('XAF', 'CFA franc BEAC', NULL),
('XAG', 'Silver (one troy ounce)', NULL),
('XAU', 'Gold (one troy ounce)', NULL),
('XBA', 'European Composite Unit (EURCO) (bond market unit)', NULL),
('XBB', 'European Monetary Unit (E.M.U.) (bond market unit)', NULL),
('XBC', 'European Unit of Account 9 (E.U.A. - bond market unit)', NULL),
('XBD', 'European Unit of Account 17 (E.U.A. - bond market unit)', NULL),
('XCD', 'East Caribbean dollar', NULL),
('XDR', 'Special Drawing Rights', NULL),
('XOF', 'CFA franc BCEAO', NULL),
('XPD', 'Palladium (one troy ounce)', NULL),
('XPF', 'CFP franc (franc Pacifique)', NULL),
('XPT', 'Platinum (one troy ounce)', NULL),
('XSU', 'Sucre', NULL),
('XUA', 'ADB unit of account', NULL),
('XXX', 'The codes assigned for transactions where no currency is involved', NULL),
('YER', 'Yemeni rial', NULL),
('ZAR', 'South African rand', 'R'),
('ZMW', 'Zambian kwacha', NULL),
('ZWL', 'Zimbabwean dollar', NULL);

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

--
-- Table structure for table `financial_years`
--

CREATE TABLE `financial_years` (
  `id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `year_label` varchar(20) NOT NULL,
  `status` varchar(20) DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `godown_master`
--

CREATE TABLE `godown_master` (
  `godown_id` int(11) NOT NULL,
  `godown_name` varchar(150) NOT NULL,
  `godown_code` varchar(50) NOT NULL,
  `address` text NOT NULL,
  `company_id` int(11) NOT NULL,
  `place_of_business_id` int(11) DEFAULT NULL,
  `branch_id` int(11) DEFAULT NULL,
  `gstin` varchar(15) DEFAULT NULL,
  `contact_person` varchar(100) DEFAULT NULL,
  `contact_number` varchar(20) DEFAULT NULL,
  `storage_capacity` decimal(12,2) DEFAULT NULL,
  `default_for_transactions` enum('Purchase','Sales','Transfer','None') DEFAULT 'None',
  `status` enum('Active','Inactive') DEFAULT 'Active',
  `created_by` int(11) NOT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `warehouse_type` varchar(50) DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `operating_hours` varchar(100) DEFAULT NULL,
  `security_details` text DEFAULT NULL,
  `inventory_methods` enum('FIFO','LIFO','Batch','Other') DEFAULT 'FIFO',
  `compliance_licenses` text DEFAULT NULL,
  `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)),
  `notes` text DEFAULT NULL,
  `attachments` text DEFAULT NULL,
  `approval_workflow_status` enum('Pending','Approved','Rejected') DEFAULT 'Approved'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `gst_statecodes`
--

CREATE TABLE `gst_statecodes` (
  `StateCode` int(11) NOT NULL,
  `StateName` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `gst_statecodes`
--

INSERT INTO `gst_statecodes` (`StateCode`, `StateName`) VALUES
(1, 'Jammu & Kashmir'),
(2, 'Himachal Pradesh'),
(3, 'Punjab'),
(4, 'Chandigarh'),
(5, 'Uttarakhand'),
(6, 'Haryana'),
(7, 'Delhi'),
(8, 'Rajasthan'),
(9, 'Uttar Pradesh'),
(10, 'Bihar'),
(11, 'Sikkim'),
(12, 'Arunachal Pradesh'),
(13, 'Nagaland'),
(14, 'Manipur'),
(15, 'Mizoram'),
(16, 'Tripura'),
(17, 'Meghalaya'),
(18, 'Assam'),
(19, 'West Bengal'),
(20, 'Jharkhand'),
(21, 'Odisha'),
(22, 'Chhattisgarh'),
(23, 'Madhya Pradesh'),
(24, 'Gujarat'),
(25, 'Daman & Diu'),
(26, 'Dadra & Nagar Haveli'),
(27, 'Maharashtra'),
(28, 'Andhra Pradesh'),
(29, 'Karnataka'),
(30, 'Goa'),
(31, 'Lakshadweep'),
(32, 'Kerala'),
(33, 'Tamil Nadu'),
(34, 'Puducherry'),
(35, 'Andaman & Nicobar Islands'),
(36, 'Telangana'),
(37, 'Andhra Pradesh (New)'),
(96, 'OTHER COUNTRIES'),
(97, 'OTHER TERRITORY');

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

--
-- Table structure for table `invoice`
--

CREATE TABLE `invoice` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `supply_type_code` varchar(10) NOT NULL,
  `reverse_charge` tinyint(1) DEFAULT 0,
  `ecomm_gstin` varchar(15) DEFAULT NULL,
  `igst_on_intra` tinyint(1) DEFAULT 0,
  `document_type` varchar(50) NOT NULL,
  `document_number` varchar(50) NOT NULL,
  `document_date` date NOT NULL,
  `buyer_gstin` varchar(15) NOT NULL,
  `buyer_legal_name` varchar(255) NOT NULL,
  `buyer_trade_name` varchar(255) DEFAULT NULL,
  `buyer_pos` varchar(10) NOT NULL,
  `buyer_addr1` varchar(255) NOT NULL,
  `buyer_addr2` varchar(255) DEFAULT NULL,
  `buyer_location` varchar(255) NOT NULL,
  `buyer_pin_code` varchar(10) DEFAULT NULL,
  `buyer_state` varchar(100) NOT NULL,
  `buyer_phone_number` varchar(20) DEFAULT NULL,
  `buyer_email_id` varchar(255) DEFAULT NULL,
  `dispatch_name` varchar(255) DEFAULT NULL,
  `dispatch_addr1` varchar(255) DEFAULT NULL,
  `dispatch_addr2` varchar(255) DEFAULT NULL,
  `dispatch_location` varchar(255) DEFAULT NULL,
  `dispatch_pin_code` varchar(10) DEFAULT NULL,
  `dispatch_state` varchar(100) DEFAULT NULL,
  `shipping_gstin` varchar(15) DEFAULT NULL,
  `shipping_legal_name` varchar(255) DEFAULT NULL,
  `shipping_trade_name` varchar(255) DEFAULT NULL,
  `shipping_addr1` varchar(255) DEFAULT NULL,
  `shipping_addr2` varchar(255) DEFAULT NULL,
  `shipping_location` varchar(255) DEFAULT NULL,
  `shipping_pin_code` varchar(10) DEFAULT NULL,
  `shipping_state` varchar(100) DEFAULT NULL,
  `total_taxable_value` decimal(15,2) DEFAULT 0.00,
  `sgst_amt` decimal(15,2) DEFAULT 0.00,
  `cgst_amt` decimal(15,2) DEFAULT 0.00,
  `igst_amt` decimal(15,2) DEFAULT 0.00,
  `cess_amt` decimal(15,2) DEFAULT 0.00,
  `state_cess_amt` decimal(15,2) DEFAULT 0.00,
  `discount` decimal(15,2) DEFAULT 0.00,
  `other_charges` decimal(15,2) DEFAULT 0.00,
  `round_off` decimal(15,2) DEFAULT 0.00,
  `total_invoice_value` decimal(15,2) NOT NULL,
  `total_invoice_value_additional_currency` decimal(15,2) DEFAULT NULL,
  `shipping_bill_no` varchar(50) DEFAULT NULL,
  `shipping_bill_date` date DEFAULT NULL,
  `port` varchar(100) DEFAULT NULL,
  `supplier_refund` tinyint(1) DEFAULT 0,
  `foreign_currency` varchar(10) DEFAULT NULL,
  `country_code` varchar(10) DEFAULT NULL,
  `export_duty_amount` decimal(15,2) DEFAULT NULL,
  `trans_id` varchar(50) DEFAULT NULL,
  `trans_name` varchar(255) DEFAULT NULL,
  `trans_mode` varchar(50) DEFAULT NULL,
  `distance` int(11) DEFAULT NULL,
  `trans_doc_no` varchar(50) DEFAULT NULL,
  `trans_doc_date` date DEFAULT NULL,
  `vehicle_no` varchar(50) DEFAULT NULL,
  `vehicle_type` varchar(50) DEFAULT NULL,
  `payee_name` varchar(255) DEFAULT NULL,
  `account_number` varchar(50) DEFAULT NULL,
  `mode` varchar(50) DEFAULT NULL,
  `branch_ifsc_code` varchar(50) DEFAULT NULL,
  `term_of_payment` varchar(255) DEFAULT NULL,
  `payment_instruction` text DEFAULT NULL,
  `credit_transfer` tinyint(1) DEFAULT 0,
  `direct_debit` tinyint(1) DEFAULT 0,
  `credit_days` int(11) DEFAULT NULL,
  `paid_amount` decimal(15,2) DEFAULT NULL,
  `due_amount` decimal(15,2) DEFAULT NULL,
  `remarks` text DEFAULT NULL,
  `invoice_period_start_date` date DEFAULT NULL,
  `invoice_period_end_date` date DEFAULT NULL,
  `original_invoice` varchar(50) DEFAULT NULL,
  `preceding_invoice_date` date DEFAULT NULL,
  `other_reference` varchar(255) DEFAULT NULL,
  `receipt_advice_number` varchar(50) DEFAULT NULL,
  `date_of_receipt_advice` date DEFAULT NULL,
  `lot_batch_reference_number` varchar(50) DEFAULT NULL,
  `contract_reference_number` varchar(50) DEFAULT NULL,
  `any_other_reference` varchar(255) DEFAULT NULL,
  `project_reference_number` varchar(50) DEFAULT NULL,
  `vendor_po_reference_number` varchar(50) DEFAULT NULL,
  `vendor_po_reference_date` date DEFAULT NULL,
  `supporting_doc_url` text DEFAULT NULL,
  `supporting_doc_base64` longtext DEFAULT NULL,
  `additional_information` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `invoice_items`
--

CREATE TABLE `invoice_items` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `sl_no` int(11) NOT NULL,
  `product_description` text NOT NULL,
  `is_service` tinyint(1) NOT NULL,
  `hsn_code` varchar(20) NOT NULL,
  `bar_code` varchar(50) DEFAULT NULL,
  `quantity` decimal(15,2) NOT NULL,
  `free_quantity` decimal(15,2) DEFAULT NULL,
  `unit` varchar(50) NOT NULL,
  `unit_price` decimal(15,2) NOT NULL,
  `gross_amount` decimal(15,2) NOT NULL,
  `discount` decimal(15,2) DEFAULT NULL,
  `pre_tax_value` decimal(15,2) DEFAULT NULL,
  `taxable_value` decimal(15,2) NOT NULL,
  `gst_rate` decimal(5,2) NOT NULL,
  `sgst_amt` decimal(15,2) DEFAULT NULL,
  `cgst_amt` decimal(15,2) DEFAULT NULL,
  `igst_amt` decimal(15,2) DEFAULT NULL,
  `cess_rate` decimal(5,2) DEFAULT NULL,
  `cess_amt_adval` decimal(15,2) DEFAULT NULL,
  `cess_non_adval_amt` decimal(15,2) DEFAULT NULL,
  `state_cess_rate` decimal(5,2) DEFAULT NULL,
  `state_cess_adval_amt` decimal(15,2) DEFAULT NULL,
  `state_cess_non_adval_amt` decimal(15,2) DEFAULT NULL,
  `other_charges` decimal(15,2) DEFAULT NULL,
  `item_total` decimal(15,2) NOT NULL,
  `order_line_reference` varchar(50) DEFAULT NULL,
  `origin_country` varchar(50) DEFAULT NULL,
  `unique_item_sl_no` varchar(50) DEFAULT NULL,
  `batch_name` varchar(255) DEFAULT NULL,
  `batch_expiry_dt` date DEFAULT NULL,
  `warranty_dt` date DEFAULT NULL,
  `attribute_details` text DEFAULT NULL,
  `attribute_value` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `invoice_numbering`
--

CREATE TABLE `invoice_numbering` (
  `id` int(11) NOT NULL,
  `prefix` varchar(20) NOT NULL COMMENT 'Prefix for invoice number e.g., FY25 or BLR01',
  `number_length` int(11) NOT NULL COMMENT 'Number of digits for the numeric part, max 16 digits',
  `suffix` varchar(20) DEFAULT NULL COMMENT 'Optional suffix for invoice number',
  `reset_frequency` enum('yearly','monthly','never') NOT NULL DEFAULT 'yearly' COMMENT 'Reset frequency for numbering',
  `last_number` int(11) NOT NULL DEFAULT 0 COMMENT 'Last issued number to calculate the next one',
  `status` enum('active','inactive') NOT NULL DEFAULT 'active' COMMENT 'Status of this numbering configuration',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `ledger`
--

CREATE TABLE `ledger` (
  `id` int(11) NOT NULL,
  `ledger_name` varchar(255) NOT NULL,
  `alternative_ledger_name` varchar(255) DEFAULT NULL,
  `gstin` varchar(15) DEFAULT NULL,
  `ledger_code` varchar(50) DEFAULT NULL,
  `opening_balance` decimal(15,2) DEFAULT 0.00,
  `opening_balance_type` enum('Dr','Cr') DEFAULT 'Dr',
  `description` text DEFAULT NULL,
  `mastergroup_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `building_number` varchar(100) DEFAULT NULL,
  `street` varchar(255) DEFAULT NULL,
  `location` varchar(255) DEFAULT NULL,
  `district` varchar(255) DEFAULT NULL,
  `state_code` int(11) DEFAULT NULL,
  `country_code` varchar(10) DEFAULT 'IN',
  `currency_code` varchar(10) DEFAULT 'INR',
  `pincode` varchar(10) DEFAULT NULL,
  `floor_number` varchar(50) DEFAULT NULL,
  `latitude` decimal(10,7) DEFAULT NULL,
  `longitude` decimal(10,7) DEFAULT NULL,
  `status` enum('Suspended','Active','Inactive','Cancelled') DEFAULT 'Active',
  `registration_type` enum('Regular Taxpayer','Composition Scheme','Input Service Distributor (ISD)','E-commerce Operator','Non-Resident Taxable Person','Casual Taxable Person','Government Departments and Local Authorities','Charitable Organizations') DEFAULT 'Regular Taxpayer',
  `business_type` varchar(255) DEFAULT NULL,
  `frequency_type` enum('Monthly','Quarterly','Half Yearly','Yearly') DEFAULT 'Monthly',
  `bank_name` varchar(255) DEFAULT NULL,
  `bank_account_number` varchar(50) DEFAULT NULL,
  `bank_ifsc_code` varchar(20) DEFAULT NULL,
  `swift_code` varchar(20) DEFAULT NULL,
  `micr_code` varchar(20) DEFAULT NULL,
  `branch_name` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `ledger_transactions`
--

CREATE TABLE `ledger_transactions` (
  `id` bigint(20) NOT NULL,
  `ledger_id` int(11) NOT NULL,
  `transaction_date` date NOT NULL,
  `reference_no` varchar(50) DEFAULT NULL,
  `description` varchar(255) DEFAULT NULL,
  `debit_amount` decimal(15,2) DEFAULT 0.00,
  `credit_amount` decimal(15,2) DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `mastergroup`
--

CREATE TABLE `mastergroup` (
  `id` int(11) NOT NULL,
  `group_name` varchar(255) NOT NULL,
  `group_code` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `primary_group_id` int(11) DEFAULT NULL,
  `company_id` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `mastergroup`
--

INSERT INTO `mastergroup` (`id`, `group_name`, `group_code`, `description`, `primary_group_id`, `company_id`, `created_at`, `updated_at`) VALUES
(1, 'Current Assets', 'CUR_ASSET', 'Assets expected to be converted into cash or used up within one year', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(2, 'Fixed Assets', 'FIXED_ASSET', 'Long-term tangible assets used in operations', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(3, 'Investments', 'INVESTMENTS', 'Financial assets in other companies or projects', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(4, 'Miscellaneous Expenses (Asset)', 'MISC_EXP_ASSET', 'Expenses treated as assets due to long-term benefits', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(5, 'Bank Accounts', 'BANK_ACCTS', 'All bank accounts held by the company', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(6, 'Cash-in-Hand', 'CASH_HAND', 'Physical cash available to the company', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(7, 'Deposits (Asset)', 'DEPOSITS_ASSET', 'Money deposited with banks or other entities', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(8, 'Loans & Advances (Asset)', 'LOANS_ADV_ASSET', 'Money lent to others expected to be repaid', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(9, 'Stock-in-Hand', 'STOCK_HAND', 'Inventory available for sale', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(10, 'Sundry Debtors', 'SUNDRY_DEBTORS', 'Money owed to the company by customers', 1, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(11, 'Current Liabilities', 'CUR_LIABILITY', 'Obligations due within one year', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(12, 'Loans (Liability)', 'LOANS_LIABILITY', 'Money borrowed that needs to be repaid', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(13, 'Suspense A/c', 'SUSPENSE_AC', 'Temporary accounts for unclassified transactions', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(14, 'Reserves & Surplus', 'RESERVES_SURPLUS', 'Retained earnings and reserves for specific purposes', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(15, 'Provisions', 'PROVISIONS', 'Amounts set aside for anticipated future liabilities', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(16, 'Duties & Taxes', 'DUTIES_TAXES', 'Taxes owed to the government', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(17, 'Sundry Creditors', 'SUNDRY_CREDITORS', 'Money owed by the company to suppliers or creditors', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(18, 'Bank OD A/c', 'BANK_OD_AC', 'Overdraft accounts', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(19, 'Secured Loans', 'SECURED_LOANS', 'Loans backed by collateral', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(20, 'Unsecured Loans', 'UNSECURED_LOANS', 'Loans not backed by collateral', 2, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(21, 'Direct Incomes', 'DIRECT_INCOME', 'Income directly related to core business activities', 3, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(22, 'Indirect Incomes', 'INDIRECT_INCOME', 'Income from secondary activities such as investments', 3, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(23, 'Sales Accounts', 'SALES_ACCTS', 'Accounts related to sale of goods or services', 3, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(24, 'Direct Expenses', 'DIRECT_EXPENSES', 'Costs directly attributed to production of goods or services', 4, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(25, 'Indirect Expenses', 'INDIRECT_EXPENSES', 'Costs not directly tied to production but necessary for operations', 4, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(26, 'Purchase Accounts', 'PURCHASE_ACCTS', 'Accounts related to purchase of goods or services', 4, NULL, '2025-06-08 04:39:42', '2025-06-08 04:39:42');

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

--
-- Table structure for table `primary_group`
--

CREATE TABLE `primary_group` (
  `id` int(11) NOT NULL,
  `group_name` varchar(255) NOT NULL,
  `group_code` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `primary_group`
--

INSERT INTO `primary_group` (`id`, `group_name`, `group_code`, `description`, `created_at`, `updated_at`) VALUES
(1, 'Asset', 'ASSET', 'Asset group', '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(2, 'Liability', 'LIABILITY', 'Liability group', '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(3, 'Income', 'INCOME', 'Income group', '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(4, 'Expenses', 'EXPENSES', 'Expenses group', '2025-06-08 04:39:42', '2025-06-08 04:39:42'),
(5, 'Equity Investments', 'EQUITY_INVESTMENTS', 'Investments providing ownership interest', '2025-06-08 04:39:42', '2025-06-08 04:39:42');

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

--
-- Table structure for table `stock_groups`
--

CREATE TABLE `stock_groups` (
  `id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `group_name` varchar(255) NOT NULL,
  `parent_group_id` int(11) DEFAULT 0,
  `group_code` varchar(100) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `valuation_method` varchar(50) DEFAULT NULL,
  `uom_primary` varchar(50) DEFAULT NULL,
  `uom_secondary` varchar(50) DEFAULT NULL,
  `gst_rate` decimal(5,2) DEFAULT NULL,
  `hsn_code` varchar(20) DEFAULT NULL,
  `allow_batch_tracking` tinyint(1) DEFAULT 0,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `stock_items`
--

CREATE TABLE `stock_items` (
  `id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_code` varchar(100) NOT NULL,
  `stock_group_id` int(11) NOT NULL,
  `primary_uom_id` int(11) NOT NULL,
  `hsn_code` varchar(20) DEFAULT NULL,
  `gst_rate` decimal(5,2) DEFAULT NULL,
  `purchase_price` decimal(10,2) DEFAULT NULL,
  `selling_price` decimal(10,2) DEFAULT NULL,
  `opening_stock_qty` decimal(10,2) DEFAULT 0.00,
  `opening_stock_value` decimal(10,2) DEFAULT 0.00,
  `reorder_level` decimal(10,2) DEFAULT 0.00,
  `batch_tracking` tinyint(1) DEFAULT 0,
  `serial_tracking` tinyint(1) DEFAULT 0,
  `is_active` tinyint(1) DEFAULT 1,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `stock_units`
--

CREATE TABLE `stock_units` (
  `id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  `unit_name` varchar(100) NOT NULL,
  `unit_symbol` varchar(20) NOT NULL,
  `description` text DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `created_date` datetime DEFAULT current_timestamp(),
  `is_active` tinyint(1) DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `user_id` int(11) NOT NULL,
  `user_uuid` char(36) DEFAULT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `email` varchar(100) NOT NULL,
  `password` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`user_id`, `user_uuid`, `first_name`, `last_name`, `username`, `email`, `password`, `phone`, `address`, `created_at`) VALUES
(1, NULL, 'Anand Kumar', 'Kodigehalli Shivarudrappa', 'Gvaanand', 'ananda.k.s@icai.org', '$2y$10$G5vm5ucx.TTF6DKl7LJHUuFWWzjc7kV6cYjHVLYDEuIB1aDqAB9sW', '8123030275', 'Kodigehalli', '2025-06-07 16:13:47');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `additional_places_of_business`
--
ALTER TABLE `additional_places_of_business`
  ADD PRIMARY KEY (`id`),
  ADD KEY `company_id` (`company_id`);

--
-- Indexes for table `companies`
--
ALTER TABLE `companies`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_gst_number` (`gst_number`),
  ADD UNIQUE KEY `company_uuid` (`company_uuid`),
  ADD KEY `idx_user_id` (`user_id`);

--
-- Indexes for table `country`
--
ALTER TABLE `country`
  ADD PRIMARY KEY (`code`);

--
-- Indexes for table `currency`
--
ALTER TABLE `currency`
  ADD PRIMARY KEY (`code`);

--
-- Indexes for table `financial_years`
--
ALTER TABLE `financial_years`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `godown_master`
--
ALTER TABLE `godown_master`
  ADD PRIMARY KEY (`godown_id`),
  ADD UNIQUE KEY `godown_code` (`godown_code`),
  ADD KEY `idx_godown_name` (`godown_name`),
  ADD KEY `idx_company_id` (`company_id`),
  ADD KEY `idx_place_of_business_id` (`place_of_business_id`),
  ADD KEY `idx_branch_id` (`branch_id`);

--
-- Indexes for table `gst_statecodes`
--
ALTER TABLE `gst_statecodes`
  ADD PRIMARY KEY (`StateCode`);

--
-- Indexes for table `invoice`
--
ALTER TABLE `invoice`
  ADD PRIMARY KEY (`id`),
  ADD KEY `user_id` (`user_id`);

--
-- Indexes for table `invoice_items`
--
ALTER TABLE `invoice_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `invoice_id` (`invoice_id`);

--
-- Indexes for table `invoice_numbering`
--
ALTER TABLE `invoice_numbering`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `ledger`
--
ALTER TABLE `ledger`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `ledger_code` (`ledger_code`),
  ADD KEY `fk_ledger_mastergroup` (`mastergroup_id`),
  ADD KEY `fk_ledger_company` (`company_id`),
  ADD KEY `fk_ledger_state` (`state_code`),
  ADD KEY `fk_ledger_currency` (`currency_code`),
  ADD KEY `fk_ledger_country` (`country_code`);

--
-- Indexes for table `ledger_transactions`
--
ALTER TABLE `ledger_transactions`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_ledger_id` (`ledger_id`),
  ADD KEY `idx_transaction_date` (`transaction_date`);

--
-- Indexes for table `mastergroup`
--
ALTER TABLE `mastergroup`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `group_name` (`group_name`),
  ADD UNIQUE KEY `group_code` (`group_code`),
  ADD KEY `fk_primary_group` (`primary_group_id`),
  ADD KEY `fk_company` (`company_id`);

--
-- Indexes for table `primary_group`
--
ALTER TABLE `primary_group`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `group_name` (`group_name`),
  ADD UNIQUE KEY `group_code` (`group_code`);

--
-- Indexes for table `stock_groups`
--
ALTER TABLE `stock_groups`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `group_code` (`group_code`),
  ADD KEY `parent_group_id` (`parent_group_id`),
  ADD KEY `company_id` (`company_id`);

--
-- Indexes for table `stock_items`
--
ALTER TABLE `stock_items`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `item_code` (`item_code`),
  ADD KEY `company_id` (`company_id`),
  ADD KEY `stock_group_id` (`stock_group_id`),
  ADD KEY `primary_uom_id` (`primary_uom_id`);

--
-- Indexes for table `stock_units`
--
ALTER TABLE `stock_units`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unit_name` (`unit_name`),
  ADD UNIQUE KEY `unit_symbol` (`unit_symbol`),
  ADD KEY `company_id` (`company_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`user_id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `user_id454` (`user_id`),
  ADD UNIQUE KEY `user_uuid` (`user_uuid`),
  ADD KEY `user_id454_2` (`user_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `additional_places_of_business`
--
ALTER TABLE `additional_places_of_business`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `companies`
--
ALTER TABLE `companies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `financial_years`
--
ALTER TABLE `financial_years`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `godown_master`
--
ALTER TABLE `godown_master`
  MODIFY `godown_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `invoice`
--
ALTER TABLE `invoice`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `invoice_items`
--
ALTER TABLE `invoice_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `invoice_numbering`
--
ALTER TABLE `invoice_numbering`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ledger`
--
ALTER TABLE `ledger`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `ledger_transactions`
--
ALTER TABLE `ledger_transactions`
  MODIFY `id` bigint(20) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `mastergroup`
--
ALTER TABLE `mastergroup`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=27;

--
-- AUTO_INCREMENT for table `primary_group`
--
ALTER TABLE `primary_group`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `stock_groups`
--
ALTER TABLE `stock_groups`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock_items`
--
ALTER TABLE `stock_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `stock_units`
--
ALTER TABLE `stock_units`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `user_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `additional_places_of_business`
--
ALTER TABLE `additional_places_of_business`
  ADD CONSTRAINT `additional_places_of_business_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `godown_master`
--
ALTER TABLE `godown_master`
  ADD CONSTRAINT `fk_godown_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `fk_godown_place_of_business` FOREIGN KEY (`place_of_business_id`) REFERENCES `additional_places_of_business` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `invoice`
--
ALTER TABLE `invoice`
  ADD CONSTRAINT `invoice_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE;

--
-- Constraints for table `invoice_items`
--
ALTER TABLE `invoice_items`
  ADD CONSTRAINT `invoice_items_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoice` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `ledger_transactions`
--
ALTER TABLE `ledger_transactions`
  ADD CONSTRAINT `fk_ledger_transactions_ledger` FOREIGN KEY (`ledger_id`) REFERENCES `ledger` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `mastergroup`
--
ALTER TABLE `mastergroup`
  ADD CONSTRAINT `fk_company` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE SET NULL,
  ADD CONSTRAINT `fk_primary_group` FOREIGN KEY (`primary_group_id`) REFERENCES `primary_group` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `stock_groups`
--
ALTER TABLE `stock_groups`
  ADD CONSTRAINT `stock_groups_ibfk_1` FOREIGN KEY (`parent_group_id`) REFERENCES `stock_groups` (`id`),
  ADD CONSTRAINT `stock_groups_ibfk_2` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`);

--
-- Constraints for table `stock_items`
--
ALTER TABLE `stock_items`
  ADD CONSTRAINT `stock_items_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`),
  ADD CONSTRAINT `stock_items_ibfk_2` FOREIGN KEY (`stock_group_id`) REFERENCES `stock_groups` (`id`),
  ADD CONSTRAINT `stock_items_ibfk_3` FOREIGN KEY (`primary_uom_id`) REFERENCES `stock_units` (`id`);

--
-- Constraints for table `stock_units`
--
ALTER TABLE `stock_units`
  ADD CONSTRAINT `stock_units_ibfk_1` FOREIGN KEY (`company_id`) REFERENCES `companies` (`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 */;
 for above database i need Vocher types and Vouchers along with relations to exisitance databasse