-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `signer`
--

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

--
-- Table structure for table `chat`
--

CREATE TABLE `chat` (
  `id` int(11) NOT NULL,
  `sender` int(11) NOT NULL,
  `message` text NOT NULL,
  `time_` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `file` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

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

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

CREATE TABLE `companies` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  `phone` varchar(16) DEFAULT NULL,
  `reminders` int(11) NOT NULL DEFAULT 1,
  `stafflimit` int(11) NOT NULL DEFAULT 0,
  `guest_sign_write` enum('enabled','disabled') NOT NULL DEFAULT 'enabled'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `companies`
--

INSERT INTO `companies` (`id`, `name`, `email`, `phone`, `reminders`, `stafflimit`, `guest_sign_write`) VALUES
(0, 'System Users', '', '', 1, 0, 'enabled'),
(1, 'VLMBD', 'golam.mostofa@bdventura.com', '01787670298', 1, 0, 'enabled');

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

--
-- Table structure for table `files`
--

CREATE TABLE `files` (
  `id` int(11) NOT NULL,
  `company` int(11) NOT NULL,
  `approved_by` int(11) NOT NULL DEFAULT 1,
  `folder` int(11) NOT NULL,
  `uploaded_by` int(11) NOT NULL,
  `uploaded_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `name` varchar(256) NOT NULL,
  `filename` varchar(256) NOT NULL,
  `type` varchar(16) NOT NULL DEFAULT 'pdf',
  `size` int(11) NOT NULL DEFAULT 0,
  `sharing_key` varchar(32) NOT NULL,
  `status` enum('unsigned','signed') NOT NULL DEFAULT 'unsigned',
  `template` int(11) NOT NULL DEFAULT 0,
  `protected` enum('Yes','No') NOT NULL DEFAULT 'No',
  `sign_reason` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `files`
--

INSERT INTO `files` (`id`, `company`, `approved_by`, `folder`, `uploaded_by`, `uploaded_on`, `name`, `filename`, `type`, `size`, `sharing_key`, `status`, `template`, `protected`, `sign_reason`) VALUES
(675, 1, 129, 53, 132, '2018-10-20 11:34:35', 'Test', '8673840271540035275.pdf', 'pdf', 0, 'ymZsDSL0yZq311CaJDcH1540035275', 'unsigned', 0, 'No', NULL),
(676, 1, 1, 59, 1, '2024-05-16 06:18:23', 'Test', 'doc6645a52f8874a.pdf', 'pdf', 0, 'Nz3bswLXX6nvYWU0QqDM1715745514', 'signed', 0, 'No', ' • Digital Signature | Golam Mostofa  ::1, May 16, 2024. 08:18'),
(677, 1, 1, 59, 1, '2024-05-16 08:43:33', ' 2', 'doc6645c7350a50f.pdf', 'pdf', 0, 'f7PnDKh4MC10okO2nPf51715745528', 'signed', 0, 'No', ' • Digital Signature | Golam Mostofa  ::1, May 16, 2024. 06:37 • Digital Signature | Golam Mostofa  ::1, May 16, 2024. 06:38 • Digital Signature | Golam Mostofa  ::1, May 16, 2024. 10:43 • Digital Signature | Golam Mostofa  ::1, May 16, 2024. 10:43');

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

--
-- Table structure for table `folders`
--

CREATE TABLE `folders` (
  `id` int(11) NOT NULL,
  `company` int(11) NOT NULL,
  `created_by` int(11) NOT NULL,
  `approved_by` int(11) DEFAULT 1,
  `created_on` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `name` varchar(256) NOT NULL,
  `level` int(11) DEFAULT NULL,
  `folder` int(11) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `folders`
--

INSERT INTO `folders` (`id`, `company`, `created_by`, `approved_by`, `created_on`, `name`, `level`, `folder`) VALUES
(1, 1, 1, 1, '2017-07-02 21:00:00', 'public_folder', 1, 1),
(53, 1, 132, 129, '2018-10-20 08:22:02', 'F&A for Malik Sir', NULL, 1),
(54, 1, 133, 129, '2018-10-22 05:49:56', 'Admin Doc for Malik Sir', NULL, 1),
(55, 1, 133, 130, '2018-10-22 05:50:34', 'Admin Doc for Lawrence Sir', NULL, 1),
(56, 1, 134, 129, '2018-10-22 05:51:23', 'Logistics Doc for Malik Sir', NULL, 1),
(57, 1, 134, 130, '2018-10-22 05:51:42', 'Logistics Doc for Lawrence Sir', NULL, 1),
(58, 1, 132, 130, '2018-10-22 05:52:25', 'F&A Doc for Lawrence Sir', NULL, 1),
(59, 1, 1, 1, '2024-05-15 03:58:20', 'Mostofa', NULL, 1);

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

--
-- Table structure for table `history`
--

CREATE TABLE `history` (
  `id` int(11) NOT NULL,
  `company` int(11) NOT NULL,
  `file` varchar(32) NOT NULL,
  `activity` text NOT NULL,
  `time_` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `type` enum('default','success','danger') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `history`
--

INSERT INTO `history` (`id`, `company`, `file`, `activity`, `time_`, `type`) VALUES
(2059, 1, 'ymZsDSL0yZq311CaJDcH1540035275', 'File Uploaded by <span class=\"text-primary\">Shafiqur Rahman</span>.', '2018-10-20 11:34:35', 'default'),
(2060, 1, 'Nz3bswLXX6nvYWU0QqDM1715745514', 'File Uploaded by <span class=\"text-primary\">Golam Mostofa</span>.', '2024-05-15 03:58:34', 'default'),
(2061, 1, 'f7PnDKh4MC10okO2nPf51715745528', 'File Uploaded by <span class=\"text-primary\">Golam Mostofa</span>.', '2024-05-15 03:58:48', 'default'),
(2062, 1, 'f7PnDKh4MC10okO2nPf51715745528', '<span class=\"text-primary\">Golam Mostofa</span> signed the document. <span class=\"ip-address-location text-xs\">::1 •   </span>', '2024-05-16 04:37:05', 'success'),
(2063, 1, 'f7PnDKh4MC10okO2nPf51715745528', '<span class=\"text-primary\">Golam Mostofa</span> signed the document. <span class=\"ip-address-location text-xs\">::1 •   </span>', '2024-05-16 04:38:36', 'success'),
(2064, 1, 'Nz3bswLXX6nvYWU0QqDM1715745514', '<span class=\"text-primary\">Golam Mostofa</span> signed the document. <span class=\"ip-address-location text-xs\">::1 •   </span>', '2024-05-16 06:18:23', 'success'),
(2065, 1, 'f7PnDKh4MC10okO2nPf51715745528', '<span class=\"text-primary\">Golam Mostofa</span> signed the document. <span class=\"ip-address-location text-xs\">::1 •   </span>', '2024-05-16 08:43:26', 'success'),
(2066, 1, 'f7PnDKh4MC10okO2nPf51715745528', '<span class=\"text-primary\">Golam Mostofa</span> signed the document. <span class=\"ip-address-location text-xs\">::1 •   </span>', '2024-05-16 08:43:33', 'success');

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `company` int(11) NOT NULL,
  `message` text NOT NULL,
  `time_` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `user` int(11) NOT NULL,
  `type` enum('decline','accept','warning') NOT NULL,
  `email` varchar(256) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `company`, `message`, `time_`, `user`, `type`, `email`) VALUES
(53, 0, '<span class=\"text-primary\">nuki nuki</span> accepted the invitation and signed the <a href=\"https://play.simcycreative.com/signer/sign?key=trmBDmgzFgDF6JEreOWC1534672132\"><span class=\"text-primary\">document.</span></a>', '2018-08-19 08:54:42', 128, 'accept', NULL);

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

--
-- Table structure for table `reminders`
--

CREATE TABLE `reminders` (
  `id` int(11) NOT NULL,
  `company` int(11) NOT NULL,
  `subject` varchar(256) NOT NULL,
  `days` int(11) NOT NULL,
  `message` text NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `reminders`
--

INSERT INTO `reminders` (`id`, `company`, `subject`, `days`, `message`) VALUES
(137, 1, 'Signing invitation reminder ', 3, 'Hello there, \r\n\r\nI hope you are doing well.\r\nI am writing to remind you about the signing request I had sent earlier.\r\n\r\nCheers!\r\nVentura\r\n'),
(138, 1, 'Signing invitation reminder ', 7, 'Hello there, \r\n\r\nI hope you are doing well.\r\nI am writing to remind you about the signing request I had sent earlier.\r\n\r\nCheers!\r\nVentura\r\n'),
(139, 1, 'Signing invitation reminder', 7, 'Hello there,\r\n\r\nI hope you are doing well.\r\nI am writing to remind you about the signing request I had sent earlier.\r\n\r\nCheers!\r\nVentura\r\n');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `chat`
--
ALTER TABLE `chat`
  ADD PRIMARY KEY (`id`),
  ADD KEY `sender` (`sender`),
  ADD KEY `sender_2` (`sender`),
  ADD KEY `file` (`file`);

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

--
-- Indexes for table `files`
--
ALTER TABLE `files`
  ADD UNIQUE KEY `id` (`id`),
  ADD UNIQUE KEY `sharing_key` (`sharing_key`),
  ADD KEY `company` (`company`),
  ADD KEY `uploaded_by` (`uploaded_by`),
  ADD KEY `folder` (`folder`);

--
-- Indexes for table `folders`
--
ALTER TABLE `folders`
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `created_by` (`created_by`),
  ADD KEY `company` (`company`);

--
-- Indexes for table `history`
--
ALTER TABLE `history`
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `company` (`company`),
  ADD KEY `file` (`file`),
  ADD KEY `file_2` (`file`);

--
-- Indexes for table `notifications`
--
ALTER TABLE `notifications`
  ADD UNIQUE KEY `id` (`id`),
  ADD KEY `company` (`company`),
  ADD KEY `user` (`user`);

--
-- Indexes for table `reminders`
--
ALTER TABLE `reminders`
  ADD PRIMARY KEY (`id`),
  ADD KEY `company` (`company`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `chat`
--
ALTER TABLE `chat`
  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=2;

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

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

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

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

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

--
-- Constraints for dumped tables
--

--
-- Constraints for table `chat`
--
ALTER TABLE `chat`
  ADD CONSTRAINT `chat_ibfk_1` FOREIGN KEY (`file`) REFERENCES `files` (`sharing_key`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `chat_ibfk_2` FOREIGN KEY (`sender`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `files`
--
ALTER TABLE `files`
  ADD CONSTRAINT `files_ibfk_1` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE,
  ADD CONSTRAINT `files_ibfk_2` FOREIGN KEY (`company`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `files_ibfk_3` FOREIGN KEY (`folder`) REFERENCES `folders` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `folders`
--
ALTER TABLE `folders`
  ADD CONSTRAINT `folders_ibfk_1` FOREIGN KEY (`company`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `folders_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE CASCADE;

--
-- Constraints for table `history`
--
ALTER TABLE `history`
  ADD CONSTRAINT `history_ibfk_1` FOREIGN KEY (`file`) REFERENCES `files` (`sharing_key`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

