text:
CREATE MATERIALIZED VIEW trip.list AS (
  SELECT
    cp.trip_id as trip_id,
    cp.start_insee as start_insee,
    cp.end_insee as end_insee,
    cp.operator_id as operator_id,
    cp.operator_class as operator_class,
    cp.datetime as datetime,
    extract(isodow from cp.datetime) as weekday,
    extract(hour from cp.datetime) as dayhour,
    cp.seats as seats,
    cp.is_driver as is_driver,
    cis.town as start_town,
    tis.territory_id as start_territory_id,
    cie.town as end_town,
    tie.territory_id as end_territory_id,
    (CASE WHEN cp.distance <> null THEN cp.distance ELSE (cp.meta::json->>'calc_distance')::int END) as distance
  FROM carpool.carpools as cp
  JOIN common.insee as cis ON cp.start_insee = cis._id
  JOIN common.insee as cie ON cp.end_insee = cie._id
  JOIN territory.insee as tis ON cp.start_insee = tis._id
  JOIN territory.insee as tie ON cp.end_insee = tie._id
);

CREATE INDEX ON trip.list (start_territory_id);
CREATE INDEX ON trip.list (end_territory_id);
CREATE INDEX ON trip.list (operator_id);
CREATE INDEX ON trip.list (datetime);
CREATE INDEX ON trip.list (weekday);
CREATE INDEX ON trip.list (dayhour);
CREATE INDEX ON trip.list (distance);
CREATE INDEX ON trip.list (operator_class);
CREATE INDEX ON trip.list (is_driver);

domain: sql


text:
/*
Deployment script for TiendaProd

This code was generated by a tool.
Changes to this file may cause incorrect behavior and will be lost if
the code is regenerated.
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;

GO
:setvar DatabaseName "TiendaProd"
:setvar DefaultFilePrefix "TiendaProd"
:setvar DefaultDataPath ""
:setvar DefaultLogPath ""

GO
:on error exit
GO
/*
Detect SQLCMD mode and disable script execution if SQLCMD mode is not supported.
To re-enable the script after enabling SQLCMD mode, execute the following:
SET NOEXEC OFF;
*/
:setvar __IsSqlCmdEnabled "True"
GO
IF N'$(__IsSqlCmdEnabled)' NOT LIKE N'True'
    BEGIN
        PRINT N'SQLCMD mode must be enabled to successfully execute this script.';
        SET NOEXEC ON;
    END

GO
USE [master];

GO

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL)
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)] COLLATE SQL_Latin1_General_CP1_CI_AS
GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF
            WITH ROLLBACK IMMEDIATE;
    END

GO
USE [$(DatabaseName)];

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF
            WITH ROLLBACK IMMEDIATE;
    END

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF
            WITH ROLLBACK IMMEDIATE;
    END

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE,
                SUPPLEMENTAL_LOGGING OFF
            WITH ROLLBACK IMMEDIATE;
    END

GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END

GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET HONOR_BROKER_PRIORITY OFF
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings cannot be modified. You must be a SysAdmin to apply these settings.';
    END

GO
ALTER DATABASE [$(DatabaseName)]
    SET TARGET_RECOVERY_TIME = 0 SECONDS
    WITH ROLLBACK IMMEDIATE;

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET FILESTREAM(NON_TRANSACTED_ACCESS = OFF),
                CONTAINMENT = NONE
            WITH ROLLBACK IMMEDIATE;
    END

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CREATE_STATISTICS ON(INCREMENTAL = OFF),
                MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT = OFF,
                DELAYED_DURABILITY = DISABLED
            WITH ROLLBACK IMMEDIATE;
    END

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL, DATA_FLUSH_INTERVAL_SECONDS = 900, INTERVAL_LENGTH_MINUTES = 60, MAX_PLANS_PER_QUERY = 200, CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 367), MAX_STORAGE_SIZE_MB = 100)
            WITH ROLLBACK IMMEDIATE;
    END

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET QUERY_STORE = OFF
            WITH ROLLBACK IMMEDIATE;
    END

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
        ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
        ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
    END

GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET TEMPORAL_HISTORY_RETENTION ON
            WITH ROLLBACK IMMEDIATE;
    END

GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';

GO
PRINT N'Creating [dbo].[Articulo]...';

GO
CREATE TABLE [dbo].[Articulo] (
    [Id]          INT           IDENTITY (1, 1) NOT NULL,
    [Nombre]      VARCHAR (150) NOT NULL,
    [Descripcion] VARCHAR (MAX) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
PRINT N'Creating [dbo].[Cliente]...';

GO
CREATE TABLE [dbo].[Cliente] (
    [Id]        INT           IDENTITY (1, 1) NOT NULL,
    [Nombre]    VARCHAR (50)  NOT NULL,
    [Apellidos] VARCHAR (75)  NOT NULL,
    [Movil]     VARCHAR (12)  NOT NULL,
    [Email]     VARCHAR (100) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
PRINT N'Creating [dbo].[DetallePedido]...';

GO
CREATE TABLE [dbo].[DetallePedido] (
    [Id]         INT             IDENTITY (1, 1) NOT NULL,
    [PedidoId]   INT             NOT NULL,
    [ArticuloId] INT             NOT NULL,
    [Precio]     DECIMAL (12, 2) NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
PRINT N'Creating [dbo].[Pedido]...';

GO
CREATE TABLE [dbo].[Pedido] (
    [Id]               INT      IDENTITY (1, 1) NOT NULL,
    [ClienteId]        INT      NOT NULL,
    [Creado]           DATETIME NOT NULL,
    [CuponDescuentoId] INT      NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC)
);

GO
PRINT N'Creating [dbo].[FK_DetallePedido_Pedido]...';

GO
ALTER TABLE [dbo].[DetallePedido]
    ADD CONSTRAINT [FK_DetallePedido_Pedido] FOREIGN KEY ([PedidoId]) REFERENCES [dbo].[Pedido] ([Id]);

GO
PRINT N'Creating [dbo].[FK_DetallePedido_Articulo]...';

GO
ALTER TABLE [dbo].[DetallePedido]
    ADD CONSTRAINT [FK_DetallePedido_Articulo] FOREIGN KEY ([ArticuloId]) REFERENCES [dbo].[Articulo] ([Id]);

GO
PRINT N'Creating [dbo].[FK_Pedido_Cliente]...';

GO
ALTER TABLE [dbo].[Pedido]
    ADD CONSTRAINT [FK_Pedido_Cliente] FOREIGN KEY ([ClienteId]) REFERENCES [dbo].[Cliente] ([Id]);

GO
DECLARE @VarDecimalSupported AS BIT;

SELECT @VarDecimalSupported = 0;

IF ((ServerProperty(N'EngineEdition') = 3)
    AND (((@@microsoftversion / power(2, 24) = 9)
          AND (@@microsoftversion & 0xffff >= 3024))
         OR ((@@microsoftversion / power(2, 24) = 10)
             AND (@@microsoftversion & 0xffff >= 1600))))
    SELECT @VarDecimalSupported = 1;

IF (@VarDecimalSupported > 0)
    BEGIN
        EXECUTE sp_db_vardecimal_storage_format N'$(DatabaseName)', 'ON';
    END

GO
PRINT N'Update complete.';

GO


text:
-- MySQL dump 10.13  Distrib 5.7.23, for Linux (x86_64)
--
-- Host: localhost    Database: ujiwawasan
-- ------------------------------------------------------
-- Server version	5.7.23-0ubuntu0.16.04.1

/*!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 utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `achievement`
--

/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `achievement` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `id_soal` int(11) NOT NULL,
  `id_kategori` int(11) NOT NULL,
  `id_member` int(11) NOT NULL,
  `tanggal` date NOT NULL,
  `jam` text NOT NULL,
  `point` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=131 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-09-22 21:49:36


text:
-- phpMyAdmin SQL Dump
-- version 4.7.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Mar 18, 2018 at 04:10 AM
-- Server version: 10.1.22-MariaDB
-- PHP Version: 7.1.4

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
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: `creas_ci`
--
CREATE DATABASE IF NOT EXISTS `creas_ci` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `creas_ci`;

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

--
-- Table structure for table `adolescentes`
--

CREATE TABLE `adolescentes` (
  `idadolescente` int(10) UNSIGNED NOT NULL,
  `responsavel` varchar(150) DEFAULT NULL,
  `pai` varchar(150) DEFAULT NULL,
  `pai_nasc` date DEFAULT NULL,
  `pai_natural` varchar(50) DEFAULT NULL,
  `mae` varchar(150) DEFAULT NULL,
  `mae_nasc` date DEFAULT NULL,
  `mae_natural` varchar(50) DEFAULT NULL,
  `idpessoa` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `cargos`
--

CREATE TABLE `cargos` (
  `idcargo` int(10) UNSIGNED NOT NULL,
  `nome` varchar(100) NOT NULL,
  `descricao` varchar(255) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `cargos`
--

INSERT INTO `cargos` (`idcargo`, `nome`, `descricao`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Administrador', 'Acesso e gestão total do sistema', '2017-11-22 11:30:11', '2017-11-22 11:30:11', NULL),
(2, 'Assistente Social', '', '2017-11-22 11:30:21', '2017-11-22 11:30:21', NULL);

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

--
-- Table structure for table `contatos`
--

CREATE TABLE `contatos` (
  `idcontato` int(10) UNSIGNED NOT NULL,
  `descricao` varchar(50) DEFAULT NULL,
  `tipo_cont` char(1) DEFAULT NULL,
  `contato` varchar(200) DEFAULT NULL,
  `ativo` tinyint(1) DEFAULT NULL,
  `idpessoa` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `documentos`
--

CREATE TABLE `documentos` (
  `iddocumento` int(10) UNSIGNED NOT NULL,
  `cert_nasc` int(11) DEFAULT NULL,
  `cert_livro` varchar(10) DEFAULT NULL,
  `cert_folhas` varchar(15) DEFAULT NULL,
  `cert_cartorio` varchar(150) DEFAULT NULL,
  `bairro_cartorio` varchar(50) DEFAULT NULL,
  `municipio_cartorio` varchar(50) DEFAULT NULL,
  `RG` varchar(20) DEFAULT NULL,
  `RG_emissao` date DEFAULT NULL,
  `CTPS` int(11) DEFAULT NULL,
  `CTPS_serie` varchar(15) DEFAULT NULL,
  `CPF` varchar(20) DEFAULT NULL,
  `titulo_eleitor` varchar(20) DEFAULT NULL,
  `te_secao` int(11) DEFAULT NULL,
  `te_zona` int(11) DEFAULT NULL,
  `CAM` varchar(20) DEFAULT NULL,
  `CDI` varchar(20) DEFAULT NULL,
  `CR` varchar(20) DEFAULT NULL,
  `providenciar` varchar(255) DEFAULT NULL,
  `idpessoa` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `documentos`
--

INSERT INTO `documentos` (`iddocumento`, `cert_nasc`, `cert_livro`, `cert_folhas`, `cert_cartorio`, `bairro_cartorio`, `municipio_cartorio`, `RG`, `RG_emissao`, `CTPS`, `CTPS_serie`, `CPF`, `titulo_eleitor`, `te_secao`, `te_zona`, `CAM`, `CDI`, `CR`, `providenciar`, `idpessoa`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 0, '', '', '', '', '', '', NULL, 0, '', '', '', 0, 0, '', '', '', '', 1, '2017-12-28 17:28:30', '2017-12-28 17:28:30', NULL);

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

--
-- Table structure for table `enderecos`
--

CREATE TABLE `enderecos` (
  `idendereco` int(10) UNSIGNED NOT NULL,
  `descricao` varchar(45) DEFAULT NULL,
  `logradouro` varchar(150) DEFAULT NULL,
  `numero` varchar(10) DEFAULT NULL,
  `complemento` varchar(45) DEFAULT NULL,
  `bairro` varchar(45) DEFAULT NULL,
  `cidade` varchar(45) DEFAULT NULL,
  `estado` char(2) DEFAULT NULL,
  `cep` varchar(15) DEFAULT NULL,
  `referencia` varchar(45) DEFAULT NULL,
  `dt_mudanca` date DEFAULT NULL,
  `motivo` text,
  `idpessoa` int(10) UNSIGNED NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `entidades`
--

CREATE TABLE `entidades` (
  `identidade` int(10) UNSIGNED NOT NULL,
  `nome` varchar(200) NOT NULL,
  `cnpj` varchar(18) DEFAULT NULL,
  `logradouro` varchar(200) DEFAULT NULL,
  `numero` varchar(10) DEFAULT NULL,
  `bairro` varchar(100) DEFAULT NULL,
  `cidade` varchar(100) DEFAULT NULL,
  `estado` char(2) DEFAULT NULL,
  `cep` varchar(10) DEFAULT NULL,
  `telefones` varchar(50) DEFAULT NULL,
  `email` varchar(150) DEFAULT NULL,
  `responsavel` varchar(200) NOT NULL,
  `resp_tel` varchar(16) NOT NULL,
  `resp_email` varchar(150) NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `entidades`
--

INSERT INTO `entidades` (`identidade`, `nome`, `cnpj`, `logradouro`, `numero`, `bairro`, `cidade`, `estado`, `cep`, `telefones`, `email`, `responsavel`, `resp_tel`, `resp_email`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'ETEC de Ibitinga', '', '', '', '', '', 'SP', '', '', '', 'Patricia', '', '', '2017-12-28 17:27:44', '2017-12-28 17:27:44', NULL);

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

--
-- Table structure for table `login_attempts`
--

CREATE TABLE `login_attempts` (
  `id` int(11) UNSIGNED NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `login` varchar(100) NOT NULL,
  `time` int(11) UNSIGNED DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `pessoas`
--

CREATE TABLE `pessoas` (
  `idpessoa` int(10) UNSIGNED NOT NULL,
  `nome` varchar(200) DEFAULT NULL,
  `dt_nasc` date DEFAULT NULL,
  `nome_tratamento` varchar(50) DEFAULT NULL,
  `sexo` char(1) DEFAULT NULL,
  `estado_civil` char(1) DEFAULT NULL,
  `natural` varchar(50) DEFAULT NULL,
  `obs` text,
  `tipo_pessoa` char(1) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `pessoas`
--

INSERT INTO `pessoas` (`idpessoa`, `nome`, `dt_nasc`, `nome_tratamento`, `sexo`, `estado_civil`, `natural`, `obs`, `tipo_pessoa`, `created_at`, `updated_at`, `deleted_at`) VALUES
(1, 'Administrador', NULL, '', 'O', '', NULL, '', 'F', '2017-12-28 17:26:57', '2017-12-28 17:28:27', NULL),
(2, 'Adolescente 1', NULL, '', 'M', '', '', '', 'A', '2017-12-28 17:38:58', '2017-12-28 17:38:58', NULL);

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

--
-- Table structure for table `situacao_habitacional`
--

CREATE TABLE `situacao_habitacional` (
  `idsh` int(10) UNSIGNED NOT NULL,
  `tipo` char(1) DEFAULT NULL COMMENT 'Tipo de Domicilio',
  `situacao` char(1) DEFAULT NULL COMMENT 'Situação do Domicilio',
  `valor` decimal(12,2) DEFAULT NULL,
  `agua` char(1) DEFAULT NULL,
  `esgoto` char(1) DEFAULT NULL,
  `energia` char(1) DEFAULT NULL,
  `pavimento` char(1) DEFAULT NULL,
  `coleta_lixo` char(1) DEFAULT NULL,
  `qtde_comodos` tinyint(1) DEFAULT NULL,
  `espaco` smallint(5) UNSIGNED DEFAULT NULL,
  `qtde_pessoas` tinyint(1) DEFAULT NULL,
  `idendereco` int(10) UNSIGNED NOT NULL,
  `obs` text NOT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `trabalhos`
--

CREATE TABLE `trabalhos` (
  `idtrabalho` int(10) UNSIGNED NOT NULL,
  `descricao` varchar(150) DEFAULT NULL,
  `empresa` varchar(250) DEFAULT NULL,
  `dt_inicio` datetime DEFAULT NULL,
  `dt_recisao` datetime DEFAULT NULL,
  `obs` longtext,
  `motivo_recisao` longtext,
  `tipo` char(1) DEFAULT NULL COMMENT '(F)ormal / (I)nformal',
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `deleted_at` datetime DEFAULT NULL,
  `idpessoa` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

--
-- Table structure for table `usuarios`
--

CREATE TABLE `usuarios` (
  `idusuario` int(10) UNSIGNED NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `identidade` int(10) UNSIGNED NOT NULL,
  `idpessoa` int(10) UNSIGNED NOT NULL,
  `idcargo` int(10) UNSIGNED NOT NULL,
  `salt` varchar(255) DEFAULT NULL,
  `email` varchar(200) NOT NULL,
  `password` varchar(255) NOT NULL,
  `username` varchar(100) DEFAULT NULL,
  `activation_code` varchar(40) DEFAULT NULL,
  `forgotten_password_code` varchar(40) DEFAULT NULL,
  `forgotten_password_time` int(10) UNSIGNED DEFAULT NULL,
  `remember_code` varchar(40) DEFAULT NULL,
  `last_login` int(10) UNSIGNED DEFAULT NULL,
  `active` tinyint(1) DEFAULT NULL,
  `termo` tinyint(1) NOT NULL,
  `data_termo` datetime DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `usuarios`
--

INSERT INTO `usuarios` (`idusuario`, `ip_address`, `identidade`, `idpessoa`, `idcargo`, `salt`, `email`, `password`, `username`, `activation_code`, `forgotten_password_code`, `forgotten_password_time`, `remember_code`, `last_login`, `active`, `termo`, `data_termo`, `created_at`, `updated_at`) VALUES
(1, '::1', 1, 1, 1, NULL, 'admin@admin.com', '$2y$08$AV2psVee/aqZyk/rYF0Hve.XHHvqYtR0mw5lsB8NCX8SWJ3giEW7.', NULL, NULL, NULL, NULL, NULL, 1518106743, 1, 0, NULL, '2017-12-28 17:28:26', '2017-12-28 17:28:26');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `adolescentes`
--
ALTER TABLE `adolescentes`
  ADD PRIMARY KEY (`idadolescente`),
  ADD KEY `fk_pessoas_atendidos_idx` (`idpessoa`);

--
-- Indexes for table `cargos`
--
ALTER TABLE `cargos`
  ADD PRIMARY KEY (`idcargo`);

--
-- Indexes for table `contatos`
--
ALTER TABLE `contatos`
  ADD PRIMARY KEY (`idcontato`),
  ADD KEY `fk_pessoas_documentos_idx` (`idpessoa`);

--
-- Indexes for table `documentos`
--
ALTER TABLE `documentos`
  ADD PRIMARY KEY (`iddocumento`),
  ADD KEY `fk_pessoa_documentos_idx` (`idpessoa`);

--
-- Indexes for table `enderecos`
--
ALTER TABLE `enderecos`
  ADD PRIMARY KEY (`idendereco`),
  ADD KEY `fk_pessoa_idx` (`idpessoa`);

--
-- Indexes for table `entidades`
--
ALTER TABLE `entidades`
  ADD PRIMARY KEY (`identidade`);

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

--
-- Indexes for table `pessoas`
--
ALTER TABLE `pessoas`
  ADD PRIMARY KEY (`idpessoa`);

--
-- Indexes for table `situacao_habitacional`
--
ALTER TABLE `situacao_habitacional`
  ADD PRIMARY KEY (`idsh`),
  ADD KEY `fk_endereco_idx` (`idendereco`);

--
-- Indexes for table `trabalhos`
--
ALTER TABLE `trabalhos`
  ADD PRIMARY KEY (`idtrabalho`),
  ADD KEY `fk_pessoa_trabalhos_idx` (`idpessoa`);

--
-- Indexes for table `usuarios`
--
ALTER TABLE `usuarios`
  ADD PRIMARY KEY (`idusuario`),
  ADD KEY `fk_pessoa_ep_idx` (`idpessoa`),
  ADD KEY `fk_entidade_ep_idx` (`identidade`),
  ADD KEY `fk_cargo_usuario_idx` (`idcargo`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `adolescentes`
--
ALTER TABLE `adolescentes`
  MODIFY `idadolescente` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `cargos`
--
ALTER TABLE `cargos`
  MODIFY `idcargo` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `contatos`
--
ALTER TABLE `contatos`
  MODIFY `idcontato` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `documentos`
--
ALTER TABLE `documentos`
  MODIFY `iddocumento` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `enderecos`
--
ALTER TABLE `enderecos`
  MODIFY `idendereco` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `entidades`
--
ALTER TABLE `entidades`
  MODIFY `identidade` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `login_attempts`
--
ALTER TABLE `login_attempts`
  MODIFY `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `pessoas`
--
ALTER TABLE `pessoas`
  MODIFY `idpessoa` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `situacao_habitacional`
--
ALTER TABLE `situacao_habitacional`
  MODIFY `idsh` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `trabalhos`
--
ALTER TABLE `trabalhos`
  MODIFY `idtrabalho` int(10) UNSIGNED NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `usuarios`
--
ALTER TABLE `usuarios`
  MODIFY `idusuario` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- Constraints for dumped tables
--

--
-- Constraints for table `adolescentes`
--
ALTER TABLE `adolescentes`
  ADD CONSTRAINT `fk_pessoas_atendidos` FOREIGN KEY (`idpessoa`) REFERENCES `pessoas` (`idpessoa`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `contatos`
--
ALTER TABLE `contatos`
  ADD CONSTRAINT `fk_pessoas_documentos` FOREIGN KEY (`idpessoa`) REFERENCES `pessoas` (`idpessoa`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `documentos`
--
ALTER TABLE `documentos`
  ADD CONSTRAINT `fk_pessoa_documentos` FOREIGN KEY (`idpessoa`) REFERENCES `pessoas` (`idpessoa`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `enderecos`
--
ALTER TABLE `enderecos`
  ADD CONSTRAINT `fk_pessoa` FOREIGN KEY (`idpessoa`) REFERENCES `pessoas` (`idpessoa`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `situacao_habitacional`
--
ALTER TABLE `situacao_habitacional`
  ADD CONSTRAINT `fk_endereco` FOREIGN KEY (`idendereco`) REFERENCES `enderecos` (`idendereco`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `trabalhos`
--
ALTER TABLE `trabalhos`
  ADD CONSTRAINT `fk_pessoa_trabalhos` FOREIGN KEY (`idpessoa`) REFERENCES `pessoas` (`idpessoa`) ON DELETE NO ACTION ON UPDATE NO ACTION;

--
-- Constraints for table `usuarios`
--
ALTER TABLE `usuarios`
  ADD CONSTRAINT `fk_cargo_usuario` FOREIGN KEY (`idcargo`) REFERENCES `cargos` (`idcargo`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_entidade_usuario` FOREIGN KEY (`identidade`) REFERENCES `entidades` (`identidade`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  ADD CONSTRAINT `fk_pessoa_usuario` FOREIGN KEY (`idpessoa`) REFERENCES `pessoas` (`idpessoa`) ON DELETE NO ACTION ON UPDATE NO ACTION;
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 */;


text:
SET NAMES utf8;

DROP TABLE IF EXISTS banner CASCADE;
DROP TABLE IF EXISTS consumer CASCADE;
DROP TABLE IF EXISTS dining_hall CASCADE;
DROP TABLE IF EXISTS feedback CASCADE;
DROP TABLE IF EXISTS food CASCADE;
DROP TABLE IF EXISTS food_category CASCADE;
DROP TABLE IF EXISTS global_notice CASCADE;
DROP TABLE IF EXISTS operator CASCADE;
DROP TABLE IF EXISTS operator_id_seq CASCADE;
DROP TABLE IF EXISTS `order` CASCADE;
DROP TABLE IF EXISTS ordered_food CASCADE;
DROP TABLE IF EXISTS recommended_food CASCADE;
DROP TABLE IF EXISTS stall CASCADE;
DROP TABLE IF EXISTS consignee_information CASCADE;
DROP TABLE IF EXISTS stall_id_seq CASCADE;

CREATE TABLE banner
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增 ID',
  image_url VARCHAR(100) NOT NULL
  COMMENT '图片 URL',
  link VARCHAR(2000) NOT NULL
  COMMENT '广告 URL',
  weight INT NOT NULL DEFAULT -1
  COMMENT '排序权重，权重大者靠前，小者靠后',
  insert_user VARCHAR(12) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(12) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '横幅广告';

CREATE TABLE consumer
(
  id VARCHAR(28) PRIMARY KEY
  COMMENT '用户 ID（微信 Open ID）',
  is_member BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '会员标记
 TRUE=会员
 FALSE=普通用户',
  phone VARCHAR(20) COMMENT '手机号码,默认为空,用户获取后填入数据库',
  name VARCHAR(64) COMMENT '姓名，普通用户该字段为 NULL',
  student_id VARCHAR(64) COMMENT '学生证号，普通用户该字段为 NULL',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '用户';

CREATE TABLE dining_hall
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增 ID',
  name VARCHAR(32) NOT NULL
  COMMENT '食堂名称',
  image_url VARCHAR(100) NOT NULL
  COMMENT '图片 URL',
  start_time TIME NOT NULL
  COMMENT '开始营业时间',
  end_time TIME NOT NULL
  COMMENT '结束营业时间',
  introduction VARCHAR(100) DEFAULT ''
  COMMENT '介绍',
  weight INT NOT NULL DEFAULT -1
  COMMENT '排序权重，权重大者靠前，小者靠后',
  insert_user VARCHAR(12) COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(12) COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '食堂';

CREATE TABLE feedback
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增 ID',
  content VARCHAR(100) NOT NULL DEFAULT ''
  COMMENT '反馈内容',
  insert_user VARCHAR(28) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(28) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '用户反馈';

CREATE TABLE food
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增 ID',
  name VARCHAR(32) NOT NULL
  COMMENT '菜品名称',
  image_url VARCHAR(100) NOT NULL
  COMMENT '图片 URL',
  category_id INT NOT NULL
  COMMENT '菜品分类 ID',
  introduction VARCHAR(100) DEFAULT ''
  COMMENT '介绍',
  is_on_sale BIT(1) NOT NULL DEFAULT TRUE
  COMMENT '在售标记
 TRUE=在售
 FALSE=下架',
  pack_fee NUMERIC(12, 8) NOT NULL DEFAULT 0
  COMMENT '打包费用',
  standard_price NUMERIC(14, 8) NOT NULL
  COMMENT '标准价格，即非会员价格',
  member_price NUMERIC(14, 8) COMMENT '会员价格',
  member_price_cost NUMERIC(14, 8) COMMENT '会员价格成本，即商户提供给运营的会员价菜品的价格',
  member_price_limit INT COMMENT '会员价菜品每日限量',
  member_price_remaining INT COMMENT '当日剩余会员价菜品数量',
  sale_count INT NOT NULL
  COMMENT '总销量',
  weight INT NOT NULL DEFAULT -1
  COMMENT '排序权重，权重大者靠前，小者靠后',
  insert_user VARCHAR(28) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(28) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '菜品';

CREATE TABLE food_category
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增 ID',
  name VARCHAR(32) NOT NULL
  COMMENT '分类名称',
  stall_id VARCHAR(12) NOT NULL
  COMMENT '商户 ID',
  weight INT NOT NULL DEFAULT -1
  COMMENT '排序权重，权重大者靠前，小者靠后',
  insert_user VARCHAR(12) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(12) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '菜品分类';

CREATE TABLE global_notice
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增 ID',
  content VARCHAR(100) NOT NULL DEFAULT ''
  COMMENT '通知内容',
  insert_user VARCHAR(12) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '',
  update_user VARCHAR(12) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '全局通知';

CREATE TABLE operator
(
  id VARCHAR(12) PRIMARY KEY
  COMMENT '自增 ID，由“o_”开头',
  username VARCHAR(32) NOT NULL UNIQUE
  COMMENT '用户名',
  salt VARCHAR(64) NOT NULL
  COMMENT '盐值',
  pwd_hash VARCHAR(64) NOT NULL
  COMMENT '加盐密码的散列值',
  role INT NOT NULL DEFAULT 2
  COMMENT '运营用户角色
 1=超级管理员（拥有查看报表的权限）
 2=普通管理员',
  insert_user VARCHAR(12) COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(12) COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '运营用户';

CREATE TABLE operator_id_seq
(
  id INT PRIMARY KEY AUTO_INCREMENT
);

DELIMITER //
CREATE TRIGGER operator_id_trig
  BEFORE INSERT
  ON operator
  FOR EACH ROW
  BEGIN
    IF new.id IS NULL
    THEN
      INSERT INTO operator_id_seq VALUES ();
      SET new.id = concat('o_', last_insert_id());
    END IF;
  END;
//
DELIMITER ;

CREATE TABLE `order`
(
  id VARCHAR(32) PRIMARY KEY
  COMMENT '自增 ID',
  consumer_id VARCHAR(28) NOT NULL
  COMMENT '用户 ID',
  stall_id VARCHAR(12) NOT NULL
  COMMENT '商户 ID',
  phone VARCHAR(18) NOT NULL
  COMMENT '用户联系电话',
  appointment_id INT NOT NULL
  COMMENT '取餐号',
  appointment_time DATETIME
  COMMENT '取餐时间,当时间为null时表示菜品为立即制作',
  remark VARCHAR(100) NOT NULL
  COMMENT '订单备注',
  is_pack BIT(1) NOT NULL
  COMMENT '打包标记
 TRUE=打包
 FALSE=不打包',
  status INT NOT NULL
  COMMENT '订单状态
 0=未支付
 1=未接单
 2=已接单，即已完成
 3=商家拒单
 4=申请退款
 5=已退款
 6=商家拒绝退款
 7=已取消',
  order_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '下单时间',
  pay_time DATETIME COMMENT '付款成功时间，未支付时为 NULL',
  refund_application_time DATETIME COMMENT '退款申请时间',
  refund_end_time DATETIME COMMENT '退款结束（退款完成和退款被拒绝）时间',
  insert_user VARCHAR(28) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(28) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '订单';

CREATE TABLE ordered_food
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增 ID',
  food_id INT NOT NULL
  COMMENT '菜品 ID',
  order_id VARCHAR(32) NOT NULL
  COMMENT '订单 ID',
  count TINYINT NOT NULL
  COMMENT '购买数量',
  is_member BIT(1) NOT NULL
  COMMENT '会员价格标记
 TRUE=以会员价格购买
 FALSE=以标准价格购买',
  pack_fee NUMERIC(12, 8) NOT NULL
  COMMENT '购买时的打包费用',
  standard_price NUMERIC(14, 8) COMMENT '购买时的标准价格，以会员价格购买时该字段为 NULL',
  member_price_cost NUMERIC(14, 8) COMMENT '购买时的会员价成本，以标准价格购买时该字段为 NULL',
  member_price NUMERIC(14, 8) COMMENT '购买时的会员价格，以标准价格购买时该字段为 NULL',
  mark TINYINT DEFAULT 0
  COMMENT '评分,默认为中间值0',
  insert_user VARCHAR(28) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(28) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '订单菜品';

CREATE TABLE recommended_food
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增 ID',
  food_id INT NOT NULL
  COMMENT '菜品 ID',
  weight INT NOT NULL DEFAULT -1
  COMMENT '排序权重，权重大者靠前，小者靠后',
  insert_user VARCHAR(12) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(12) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '推荐菜品';

CREATE TABLE stall
(
  id VARCHAR(12) PRIMARY KEY
  COMMENT '自增 ID，由“s_”开头',
  dining_hall_id INT NOT NULL
  COMMENT '食堂 ID',
  role TINYINT NOT NULL DEFAULT 1
  COMMENT '商户角色
 1=食堂档口
 2=早点
 3=夜间超市',
  name VARCHAR(32) NOT NULL
  COMMENT '商户名称',
  phone VARCHAR(18) COMMENT '联系电话',
  image_url VARCHAR(100) NOT NULL
  COMMENT '图片 URL',
  introduction VARCHAR(100) DEFAULT ''
  COMMENT '介绍',
  is_open BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '营业标记
 TRUE=正在营业
 FALSE=停止营业',
  auto_accept_at_rest BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '商家休息时自动接单标记
 TRUE=商家休息时自动接单
 FALSE=商家休息时不自动接单',
  weight INT NOT NULL DEFAULT -1
  COMMENT '排序权重，权重大者靠前，小者靠后',
  username VARCHAR(32) NOT NULL UNIQUE
  COMMENT '用户名',
  salt VARCHAR(64) NOT NULL
  COMMENT '盐值',
  pwd_hash VARCHAR(64) NOT NULL
  COMMENT '加盐密码的散列值',
  insert_user VARCHAR(12) NOT NULL
  COMMENT '插入数据的用户',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_user VARCHAR(12) NOT NULL
  COMMENT '最后修改数据的用户',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '商户';

CREATE TABLE consignee_information
(
  id INT PRIMARY KEY AUTO_INCREMENT
  COMMENT '自增序列',
  consumer_id VARCHAR(28) NOT NULL
  COMMENT '客户 id',
  address VARCHAR(50) NOT NULL
  COMMENT '地址',
  empty1 VARCHAR(100) COMMENT '预留字段1',
  empty2 VARCHAR(100) COMMENT '预留字段2',
  insert_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '插入数据的时间',
  update_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
  COMMENT '最后修改数据的时间',
  is_deleted BIT(1) NOT NULL DEFAULT FALSE
  COMMENT '删除标记'
)
  COMMENT '收货信息表';

CREATE TABLE stall_id_seq
(
  id INT PRIMARY KEY AUTO_INCREMENT
);

DELIMITER //
CREATE TRIGGER stall_id_trig
  BEFORE INSERT
  ON stall
  FOR EACH ROW
  BEGIN
    IF new.id IS NULL
    THEN
      INSERT INTO stall_id_seq VALUES ();
      SET new.id = concat('s_', last_insert_id());
    END IF;
  END;
//
DELIMITER ;


text:
/*
Navicat MySQL Data Transfer

Source Server         : vagrant
Source Server Version : 50546
Source Host           : localhost:33066
Source Database       : onecms

Target Server Type    : MYSQL
Target Server Version : 50546
File Encoding         : 65001

Date: 2015-12-22 18:54:46
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `advertising`
-- ----------------------------
DROP TABLE IF EXISTS `advertising`;
CREATE TABLE `advertising` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `position` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  `end_date` datetime DEFAULT NULL,
  `image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `link` varchar(255) COLLATE utf8_unicode_ci DEFAULT '#',
  `published` tinyint(4) DEFAULT '10',
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_start_date` (`start_date`),
  KEY `idx_end_date` (`end_date`),
  KEY `idx_start_end_date` (`start_date`,`end_date`),
  KEY `idx_position` (`position`)
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of advertising
-- ----------------------------
INSERT INTO `advertising` VALUES ('1', 'sl1', 'slideshow', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '/uploads/slideshow/1.jpg', '#', '10', '1', '1', '1450754123', '1450754123');
INSERT INTO `advertising` VALUES ('2', 'sl2', 'slideshow', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '/uploads/slideshow/2.jpg', '#', '10', '1', '1', '1450754280', '1450754302');
INSERT INTO `advertising` VALUES ('3', 'sl3', 'slideshow', '0000-00-00 00:00:00', '0000-00-00 00:00:00', '/uploads/slideshow/3.jpg', '#', '10', '1', '1', '1450754294', '1450784576');
INSERT INTO `advertising` VALUES ('4', 'Sản phẩm 1', 'home_product', null, null, '/uploads/products/1.jpg', '#', '10', '1', '1', '1450782014', '1450784547');
INSERT INTO `advertising` VALUES ('5', 'Sản phẩm 2', 'home_product', null, null, '/uploads/products/2.jpg', '#', '10', '1', '1', '1450782033', '1450784106');
INSERT INTO `advertising` VALUES ('6', 'Sản phẩm 3', 'home_product', null, null, '/uploads/products/3.jpg', '#', '10', '1', '1', '1450782047', '1450784112');
INSERT INTO `advertising` VALUES ('7', 'Sản phẩm 4', 'home_product', null, null, '/uploads/products/4.jpg', '#', '10', '1', '1', '1450782064', '1450784117');
INSERT INTO `advertising` VALUES ('8', 'Sản phẩm 5', 'home_product', null, null, '/uploads/products/5.jpg', '#', '10', '1', '1', '1450782082', '1450784122');
INSERT INTO `advertising` VALUES ('9', 'Sản phẩm 6', 'home_product', null, null, '/uploads/products/6.jpg', '#', '10', '1', '1', '1450782097', '1450784127');
INSERT INTO `advertising` VALUES ('10', 'Sản phẩm 7', 'home_product', null, null, '/uploads/products/1.jpg', '#', '10', '1', '1', '1450782114', '1450784132');
INSERT INTO `advertising` VALUES ('11', 'Sản phẩm 8', 'home_product', null, null, '/uploads/products/2.jpg', '#', '10', '1', '1', '1450782142', '1450784138');
INSERT INTO `advertising` VALUES ('12', 'Sản phẩm 9', 'home_product', null, null, '/uploads/products/3.jpg', '#', '10', '1', '1', '1450782158', '1450784143');
INSERT INTO `advertising` VALUES ('13', 'Sản phẩm 10', 'home_product', null, null, '/uploads/products/4.jpg', '#', '10', '1', '1', '1450782173', '1450784148');

-- ----------------------------
-- Table structure for `auth_assignment`
-- ----------------------------
DROP TABLE IF EXISTS `auth_assignment`;
CREATE TABLE `auth_assignment` (
  `item_name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `user_id` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `created_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`item_name`,`user_id`),
  CONSTRAINT `auth_assignment_ibfk_1` FOREIGN KEY (`item_name`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of auth_assignment
-- ----------------------------

-- ----------------------------
-- Table structure for `auth_item`
-- ----------------------------
DROP TABLE IF EXISTS `auth_item`;
CREATE TABLE `auth_item` (
  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `type` int(11) NOT NULL,
  `description` text COLLATE utf8_unicode_ci,
  `rule_name` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
  `data` text COLLATE utf8_unicode_ci,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`name`),
  KEY `rule_name` (`rule_name`),
  KEY `idx-auth_item-type` (`type`),
  CONSTRAINT `auth_item_ibfk_1` FOREIGN KEY (`rule_name`) REFERENCES `auth_rule` (`name`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of auth_item
-- ----------------------------

-- ----------------------------
-- Table structure for `auth_item_child`
-- ----------------------------
DROP TABLE IF EXISTS `auth_item_child`;
CREATE TABLE `auth_item_child` (
  `parent` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `child` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`parent`,`child`),
  KEY `child` (`child`),
  CONSTRAINT `auth_item_child_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `auth_item_child_ibfk_2` FOREIGN KEY (`child`) REFERENCES `auth_item` (`name`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of auth_item_child
-- ----------------------------

-- ----------------------------
-- Table structure for `auth_rule`
-- ----------------------------
DROP TABLE IF EXISTS `auth_rule`;
CREATE TABLE `auth_rule` (
  `name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
  `data` text COLLATE utf8_unicode_ci,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of auth_rule
-- ----------------------------

-- ----------------------------
-- Table structure for `category_post`
-- ----------------------------
DROP TABLE IF EXISTS `category_post`;
CREATE TABLE `category_post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `tree` int(11) NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  `depth` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` mediumtext COLLATE utf8_unicode_ci,
  `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_description` varchar(160) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_params` text COLLATE utf8_unicode_ci,
  `published` tinyint(4) DEFAULT NULL,
  `layouts` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `views` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title` (`title`) USING BTREE,
  KEY `idx_slug` (`slug`),
  KEY `idx_lft` (`lft`),
  KEY `idx_lft_rgt` (`lft`,`rgt`),
  KEY `idx_id_lft_rgt` (`id`,`lft`,`rgt`),
  KEY `idx_parent_id` (`parent_id`),
  KEY `idx_tree_lft` (`tree`,`lft`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of category_post
-- ----------------------------
INSERT INTO `category_post` VALUES ('1', null, '1', '1', '2', '0', 'Tin tức', 'tin-tuc', '', '', '', '', '', null, '10', null, null, '1', '1', '1450760543', '1450760543');
INSERT INTO `category_post` VALUES ('2', null, '2', '1', '2', '0', 'Sự kiện', 'su-kien', '', '', '', '', '', null, '10', null, null, '1', '1', '1450760603', '1450765458');

-- ----------------------------
-- Table structure for `category_product`
-- ----------------------------
DROP TABLE IF EXISTS `category_product`;
CREATE TABLE `category_product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `parent_id` int(11) DEFAULT NULL,
  `tree` int(11) NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  `depth` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` mediumtext COLLATE utf8_unicode_ci,
  `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_description` varchar(160) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_params` text COLLATE utf8_unicode_ci,
  `published` tinyint(4) DEFAULT NULL,
  `layouts` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `views` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title` (`title`) USING BTREE,
  KEY `idx_slug` (`slug`) USING BTREE,
  KEY `idx_lft` (`lft`) USING BTREE,
  KEY `idx_lft_rgt` (`lft`,`rgt`) USING BTREE,
  KEY `idx_id_lft_rgt` (`id`,`lft`,`rgt`) USING BTREE,
  KEY `idx_parent_id` (`parent_id`) USING BTREE,
  KEY `idx_tree_lft` (`tree`,`lft`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of category_product
-- ----------------------------
INSERT INTO `category_product` VALUES ('1', null, '1', '1', '2', '0', 'Sản phẩm', 'san-pham', '', '', '', '', '', null, '10', null, null, '1', '1', '1450765903', '1450768865');

-- ----------------------------
-- Table structure for `migration`
-- ----------------------------
DROP TABLE IF EXISTS `migration`;
CREATE TABLE `migration` (
  `version` varchar(180) NOT NULL,
  `apply_time` int(11) DEFAULT NULL,
  PRIMARY KEY (`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of migration
-- ----------------------------
INSERT INTO `migration` VALUES ('m000000_000000_base', '1450684366');
INSERT INTO `migration` VALUES ('m130524_201442_init', '1450684371');
INSERT INTO `migration` VALUES ('m140506_102106_rbac_init', '1450726671');

-- ----------------------------
-- Table structure for `page`
-- ----------------------------
DROP TABLE IF EXISTS `page`;
CREATE TABLE `page` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `content` mediumtext COLLATE utf8_unicode_ci,
  `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_description` varchar(160) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_params` text COLLATE utf8_unicode_ci,
  `published` tinyint(4) DEFAULT NULL,
  `layouts` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `views` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_title` (`title`) USING BTREE,
  KEY `idx_slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of page
-- ----------------------------
INSERT INTO `page` VALUES ('1', 'Giới thiệu', 'gioi-thieu', '', '', '', '', null, '10', null, null, '1', '1', '1450769510', '1450769510');

-- ----------------------------
-- Table structure for `post`
-- ----------------------------
DROP TABLE IF EXISTS `post`;
CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `content` mediumtext COLLATE utf8_unicode_ci,
  `meta_params` text COLLATE utf8_unicode_ci,
  `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_description` varchar(160) COLLATE utf8_unicode_ci DEFAULT NULL,
  `published` tinyint(4) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_title` (`title`) USING BTREE,
  KEY `idx_slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of post
-- ----------------------------
INSERT INTO `post` VALUES ('1', '1', 'Nội dung test thử', 'noi-dung-test-thu', '/uploads/slideshow/8.jpg', '', null, '', '', '', '10', '1', '1', '1450769617', '1450769617');

-- ----------------------------
-- Table structure for `product`
-- ----------------------------
DROP TABLE IF EXISTS `product`;
CREATE TABLE `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category_id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `images` tinytext COLLATE utf8_unicode_ci,
  `sku` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `quantity` int(11) DEFAULT NULL,
  `price` int(11) DEFAULT NULL,
  `discount` int(11) DEFAULT NULL,
  `video` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `download` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `summary` tinytext COLLATE utf8_unicode_ci,
  `content` mediumtext COLLATE utf8_unicode_ci,
  `specification` text COLLATE utf8_unicode_ci,
  `meta_params` text COLLATE utf8_unicode_ci,
  `meta_title` varchar(70) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_keywords` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_description` varchar(160) COLLATE utf8_unicode_ci DEFAULT NULL,
  `published` tinyint(4) DEFAULT NULL,
  `created_by` int(11) DEFAULT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` int(11) DEFAULT NULL,
  `updated_at` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_category_id` (`category_id`),
  KEY `idx_title` (`title`) USING BTREE,
  KEY `idx_slug` (`slug`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Records of product
-- ----------------------------
INSERT INTO `product` VALUES ('1', '1', 'Máy tạo ozone làm sạch rau quả Ozonier Fresh MUM-99 30W (Trắng)', 'may-tao-ozone-lam-sach-rau-qua-ozonier-fresh-mum-99-30w-trang', '/uploads/products/1447640871096_5818759.jpg', '/uploads/products/1447640871096_5818759.jpg, /uploads/products/1447640873755_6125840.jpg', 'aaa', null, '1575000', null, '72g4Zkexu3E', null, '
- Được làm bằng chất liệu nhựa cao cấp, bền bỉ
\r\n\r\n
- Sử dụng công nghệ ozone khuếch tán hiện đại
\r\n\r\n
- Giúp khử độc và làm sạch rau củ, thực phẩm an toàn
\r\n', '
Máy tạo ozone làm sạch rau quả Ozonier Fresh MUM-99 30W (Trắng)
\r\n\r\n
- Máy tạo ozone làm sạch rau quả Ozonier Fresh MUM-99 30W (Trắng) được làm bằng chất liệu nhựa cao cấp, bền bỉ, không chứa các hóa chất gây hại, đảm bảo an toàn cho sức khỏe người sử dụng.
\r\n- Thiết kế độc đáo và hiện đại với các lỗ khí nhỏ được tích hợp ngay trên máy sẽ giúp ozone khuếch tán vào trong nước, thâm nhập vào sâu bên trong thực phẩm, tiêu diệt mầm bệnh và vi khuẩn, giúp rau, củ, quả và thức ăn luôn được tươi ngon và an toàn nhất.
\r\n- Sản phẩm có khả năng phân hủy thuốc trừ sâu, tiêu diệt vi khuẩn liên quan đến bệnh đường ruột, loại bỏ các chất độc hữu cơ và khử mùi hải sản, thịt, cá... Ngoài ra, máy còn có chức năng khử độc bát đĩa và làm sạch không gian xung quanh.
\r\n- Thiết kế tiện lợi, máy tạo ozone làm sạch rau quả Ozonier Fresh MUM-99 sẽ là một vật dụng hoàn hảo và cần thiết cho gia đình bạn.
\r\n
\r\nHướng dẫn sử dụng:
\r\n\r\n
\r\n
Làm sạch rau quả: rửa sạch bẩn trên rau quả, đổ đầy nước vào chậu với lượng vừa đủ, thả quả sục vào giữa đáy chậu, bật máy, ấn nút hẹn giờ phù hợp.
\r\n
Làm sạch thịt rau hải sản: để thực phẩm vào nồi đến khi ngập nước, thả sục vào đáy nồi, ấn nút hẹn thời gian phù hợp. Ozone khuếch tán trong nước thẩm thấu vào thực phẩm khử sạch vi khuẩn và mần bệnh. Sau khi rửa lại thực phẩm bằng nước sạch, sau 5 - 10 phút mới ướp.
\r\n
Khử độc bát đĩa: sau khi rửa bát, cốc xong. Xếp gọn vào chậu nước sạch để sục khí ozon, bát đĩa, ly cốc chén sẽ được diệt trùng hoàn toàn.
\r\n
Làm sạch nước: thả quả sục vào đáy bình, chậu sau đó bật công tắc nguồn, ấn nút hẹn giờ phù hợp với dung tích nước cần xử lý. Nước xử lý xong cần lọc lại trước khi sử dụng.
\r\n
Khử mùi không khí: tháo quả sục, đặt ống vào vị trí cần khử mùi, bật máy, ấn nút hẹn giờ phù hợp với diện tích cần làm sạch. Ozone sẽ khử sạch mùi, không gian được trả lại không khí trong lành tự nhiên.
\r\n
\r\n', '
\r\n
\r\n
\r\n			Màu sắc	\r\n			Trắng	\r\n
\r\n
\r\n			Chất liệu	\r\n			Nhựa	\r\n
\r\n
\r\n			Model	\r\n			MUM-99	\r\n
\r\n
\r\n			Kích thước	\r\n			32 x 12 x 27 (cm)	\r\n
\r\n
\r\n			Xuất xứ	\r\n			Trung Quốc	\r\n
\r\n
\r\n			Điện áp	\r\n			220 (V)	\r\n
\r\n
\r\n			Công suất	\r\n			30 (W)	\r\n
\r\n
\r\n			Bảo hành	\r\n			12 (tháng)	\r\n
\r\n
\r\n
\r\n', null, '', '', '', '10', '1', '1', '1450777213', '1450785241');

-- ----------------------------
-- Table structure for `setting`
-- ----------------------------
DROP TABLE IF EXISTS `setting`;
CREATE TABLE `setting` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `summary` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `meta_key` varchar(200) COLLATE utf8_unicode_ci NOT NULL,
  `meta_value` mediumtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `idx_meta_key` (`meta_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


text:
-- phpMyAdmin SQL Dump
-- version 4.7.4
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: 15 Des 2020 pada 17.43
-- Versi Server: 10.1.28-MariaDB
-- PHP Version: 7.1.11

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
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: `db_wo`
--

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

--
-- Struktur dari tabel `keys`
--

CREATE TABLE `keys` (
  `id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `key` varchar(40) NOT NULL,
  `level` int(2) NOT NULL,
  `ignore_limits` tinyint(1) NOT NULL DEFAULT '0',
  `is_private_key` tinyint(1) NOT NULL DEFAULT '0',
  `ip_addresses` text,
  `date_created` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data untuk tabel `keys`
--

INSERT INTO `keys` (`id`, `user_id`, `key`, `level`, `ignore_limits`, `is_private_key`, `ip_addresses`, `date_created`) VALUES
(1, 1, '123456789', 1, 1, 0, '1', 0),
(2, 2, '123456798', 1, 1, 0, '1', 0),
(3, 3, '123654987', 1, 1, 0, '1', 0);

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

--
-- Struktur dari tabel `tb_jenis`
--

CREATE TABLE `tb_jenis` (
  `id_jenis` int(11) NOT NULL,
  `nama_jenis` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data untuk tabel `tb_jenis`
--

INSERT INTO `tb_jenis` (`id_jenis`, `nama_jenis`) VALUES
(1, 'Tidak Ada'),
(2, 'Make Up Artist & Hair Do'),
(3, 'Dress & Attire'),
(4, 'Photographer & Videographer'),
(5, 'Venue'),
(6, 'Decoration & Lighting'),
(7, 'Catering'),
(8, 'Favors & Gifts'),
(9, 'Invitation'),
(10, 'Jewelry'),
(11, 'Bridal');

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

--
-- Struktur dari tabel `tb_konsumen`
--

CREATE TABLE `tb_konsumen` (
  `id_konsumen` int(11) NOT NULL,
  `nama_konsumen` varchar(255) NOT NULL,
  `layanan` varchar(255) NOT NULL,
  `id_vendor` bigint(20) DEFAULT NULL,
  `nama_vendor` varchar(255) NOT NULL,
  `tgl_pemesanan` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data untuk tabel `tb_konsumen`
--

INSERT INTO `tb_konsumen` (`id_konsumen`, `nama_konsumen`, `layanan`, `id_vendor`, `nama_vendor`, `tgl_pemesanan`) VALUES
(1, 'Fitri Febrianti', 'Pernikahan Tradisional', NULL, 'Sanggar Lisa Amanda', '2019-01-01'),
(2, 'Devina Olivia Herwanda', 'Akad Nikah', NULL, 'Flo Make Up Artist', '2019-01-01'),
(3, 'Ezra Kusumaningrum', 'Foto Prewedd', NULL, 'ISO DUO', '2019-02-02'),
(4, 'Sema Kurniawan', 'Katering 500pax', NULL, 'Sono Kembang', '2019-02-02'),
(5, 'Jeanneth Angelina', 'Katering 600pax', NULL, 'Sono Kembang', '2019-03-16'),
(6, 'David Samuel Galla', 'Foto & Video Prewedd', NULL, 'Home Native Photography', '2019-04-06'),
(7, 'Desvianty Ayu', 'Souvenir 300 item', NULL, 'Gifu Invitation & Souvenir', '2019-04-06'),
(8, 'Dik Ajeng Ayu', 'Souvenir 200 item', NULL, 'Rove Gift', '2019-03-16'),
(9, 'Deltalovita Astrid', 'Undangan', NULL, 'Creatzy Paperless', '2019-03-16'),
(10, 'Tareh Rozzaq Adzdziqri', 'Tuxedo 1 Set', NULL, 'Gester Bridal & Salon Smart Hair', '2019-01-01'),
(11, 'Stella Venezia', 'Bride Dress & Bridesmaid Dress', NULL, 'ChrisYen Wedding Boutique', '2019-04-06'),
(12, 'Gina Christina', 'Cincin Pernikahan', NULL, 'Grace Jewerly', '2019-05-25'),
(13, 'Hana Dwi Putri', 'Kebaya', NULL, 'Luminous Bridal Boutique', '2019-01-01'),
(14, 'Karina Aziqri', 'Dekorasi Rustic', NULL, 'FIORE & Co. Decoration', '2019-01-01'),
(15, 'Kiki Indah', 'Resepsi Pernikahan Internasional', NULL, 'Anantara Seminyak Bali Resort', '2019-02-02'),
(16, 'Mufida', 'Dekorasi Tradisional Jawa', NULL, 'Silverdust Decoration', '2019-05-25'),
(17, 'Nikita Savira', 'Ballroom 2000 Undangan', NULL, 'JW Marriott Hotel Surabaya', '2019-05-25'),
(18, 'Rizky Thomas', 'Souvenir 200 item', NULL, 'Rove Gift', '2019-06-29'),
(19, 'Adityo Permana', 'Katering 500 pax', NULL, 'Oma Thia', '2019-06-29'),
(20, 'Bima Aremedianto', 'Perhiasan Seserahan', NULL, 'Grace Jewerly', '2019-06-29');

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

--
-- Struktur dari tabel `tb_kota`
--

CREATE TABLE `tb_kota` (
  `id_kota` int(11) NOT NULL,
  `kab_kota` varchar(255) NOT NULL,
  `provinsi` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data untuk tabel `tb_kota`
--

INSERT INTO `tb_kota` (`id_kota`, `kab_kota`, `provinsi`) VALUES
(3, 'Jakarta Barat', 'DKI Jakarta'),
(31, 'Jakarta Pusat', 'DKI Jakarta'),
(2, 'Jakarta Selatan', 'DKI Jakarta'),
(1, 'Jakarta Timur', 'DKI Jakarta'),
(4, 'Jakarta Utara', 'DKI Jakarta'),
(18, 'Kabupaten Banyuwangi', 'Jawa Timur'),
(19, 'Kabupaten Blitar', 'Jawa Timur'),
(20, 'Kabupaten Gresik', 'Jawa Timur'),
(21, 'Kabupaten Jember', 'Jawa Timur'),
(12, 'Kabupaten Kudus', 'Jawa Tengah'),
(22, 'Kabupaten Malang', 'Jawa Timur'),
(5, 'Kota Bandung', 'Jawa Barat'),
(23, 'Kota Batu', 'Jawa Timur'),
(6, 'Kota Bekasi', 'Jawa Barat'),
(24, 'Kota Blitar', 'Jawa Timur'),
(7, 'Kota Bogor', 'Jawa Barat'),
(8, 'Kota Cimahi', 'Jawa Barat'),
(9, 'Kota Cirebon', 'Jawa Barat'),
(28, 'Kota Denpasar', 'Bali'),
(10, 'Kota Depok', 'Jawa Barat'),
(25, 'Kota Kediri', 'Jawa Timur'),
(13, 'Kota Magelang', 'Jawa Tengah'),
(26, 'Kota Malang', 'Jawa Timur'),
(14, 'Kota Salatiga', 'Jawa Tengah'),
(15, 'Kota Semarang', 'Jawa Tengah'),
(32, 'Kota Solo', 'Jawa Tengah'),
(27, 'Kota Surabaya', 'Jawa Timur'),
(16, 'Kota Surakarta', 'Jawa Tengah'),
(11, 'Kota Tasikmalaya', 'Jawa Barat'),
(17, 'Kota Tegal', 'Jawa Tengah'),
(30, 'Kupang', 'Nusa Tenggara Timur'),
(29, 'Mataram', 'Nusa Tenggara Barat');

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

--
-- Struktur dari tabel `tb_vendor`
--

CREATE TABLE `tb_vendor` (
  `id_vendor` bigint(20) NOT NULL,
  `nama_vendor` varchar(255) NOT NULL,
  `alamat_vendor` varchar(255) NOT NULL,
  `no_telp` varchar(15) NOT NULL,
  `id_wo` bigint(20) DEFAULT NULL,
  `nama_wo` varchar(255) NOT NULL,
  `nama_jenis` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data untuk tabel `tb_vendor`
--

INSERT INTO `tb_vendor` (`id_vendor`, `nama_vendor`, `alamat_vendor`, `no_telp`, `id_wo`, `nama_wo`, `nama_jenis`) VALUES
(1, 'Grace Jewerly', 'Malang Town Square Kav 24-26', '085239012332', 3, 'Sido Rabi Wedding Organizer', 'Jewelry'),
(2, 'Gifu Invitation & Souvenir', 'Ruko Boulevard no. 27 Jakarta Garden City', '081398408188', 2, 'Sido Rabi Wedding Organizer', 'Jewelry'),
(3, 'ISO DUO', 'Jl. Melati no 108', '085210456789', 3, 'Feliz Organizer', 'Photographer & Videographer'),
(4, 'Sono Kembang', 'Jl. Borobudur 99', '089874532111', 4, 'Oppie Wedding Organizer', 'Catering'),
(5, 'Philips Kwok', 'Foresta Albera D6-29', '081314666900', 5, 'My Story Event Organizer', 'Make Up Artist & Hair Do'),
(6, 'Sanggar Lisa Amanda', 'Jalan Mabes AL Delta V Cilangkap', '085239012332', 6, 'Martha Wedding Organizer', 'Make Up Artist & Hair Do'),
(7, 'Flo Make Up Artist', 'Cengkareng indah', '087885931386', 8, 'Bless Wedding', 'Make Up Artist & Hair Do'),
(8, 'Creatzy Paperless', 'Jl. Cokroaminoto Gg. Katalia III No. 9', '08814815899', 9, 'Bless Wedding Surabaya', 'Invitation'),
(9, 'Luminous Bridal Boutique', 'Kelapa Nias Raya QE 1 NO 7 Kelapa Gading Permai', '081213400688', 10, 'Majestic The Conceptual Wedding', 'Bridal'),
(10, 'JCL FOTO BRIDAL SALON', 'Jl. Boulevard Raya Blok QJ 1 No.05', '0811812171', 11, 'Glowy Wedding Organizer', 'Bridal'),
(11, 'Gester Bridal & Salon Smart Hair', 'Kompleks Kelapa Gading Square D 26-27, Jl. Ry Boulevard Barat', '08155000463', 12, 'Andie Oyong Project', 'Bridal'),
(12, 'ChrisYen Wedding Boutique', 'Jl Marina Indah Raya, Rukan PIK Cordoba Blok H No 59', '087877980808', 13, 'Finest Organizer', 'Dress & Attire'),
(13, 'New Melati Salon Bali', 'Jl. Blambangan no.1 Denpasar', '081246323393', 14, 'Hilda by Bridestory', 'Dress & Attire'),
(14, 'Hanaliewear', 'Jl. Abbey Road 2 No 11', '087884302788', 15, 'Edelweis Organizer', 'Dress & Attire'),
(15, 'Silverdust Decoration', 'Jl. Wanagiri No.1H, Jimbaran, Kuta ', '03614463008', 16, 'Varawedding', 'Decoration & Lighting'),
(16, 'Joelle Decoration', 'Jln Jomas no 21A', '08176670477', 17, 'White Roses Planner', 'Decoration & Lighting'),
(17, 'FIORE & Co. Decoration', 'Jl. Merawan, Pangkalan Jati', '087874738884', 18, 'PRIDE Organizer', 'Decoration & Lighting'),
(18, 'Home Native Photography', 'Jl. Kl Yos Sudarso Lingkungan 14a', '082216670582', 19, 'Vinz Production', 'Photographer & Videographer'),
(19, 'Kairos Works', 'Citra Garden City 6 Fine Homes, Blok L5 No.3', '08170863305', 20, 'Azka Wedding Organizer', 'Photographer & Videographer'),
(20, 'Anantara Seminyak Bali Resort', 'Jl. Abimanyu (Dhyana Pura) Seminyak', '0361737773', 21, 'SEDJOLI WEDDING ORGANIZER', 'Venue'),
(21, 'JW Marriott Hotel Surabaya', 'Jalan Embong Malang 85-89', '08113640166', 22, 'Sugar Planner', 'Venue'),
(22, 'The Vida Ballroom', ' Jl. Raya Perjuangan No. 8', '081212695519', 23, 'Point One Wedding Organizer', 'Venue'),
(23, 'Royal Ballroom The Springs Club', 'Jalan Springs Boulevard Blok C/01 , Summarecon Serpong', '02129171515', 24, 'Alfida Wedding', 'Venue'),
(24, 'Table d\'Or', 'Jalan Pengubengan Kauh no. 69', '082144928410', 25, 'Red Hat Organizer', 'Catering'),
(25, 'Oma Thia\'s Kitchen Catering', 'Jalan Gunung Salak Gang Puri Ratu No. 23', '08161180055', 26, 'Lily Wedding Services', 'Catering');

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

--
-- Struktur dari tabel `tb_wo`
--

CREATE TABLE `tb_wo` (
  `id_wo` bigint(20) NOT NULL,
  `nama_wo` varchar(255) NOT NULL,
  `alamat_wo` varchar(255) NOT NULL,
  `no_telp` varchar(15) NOT NULL,
  `layanan` varchar(255) NOT NULL,
  `kab_kota` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data untuk tabel `tb_wo`
--

INSERT INTO `tb_wo` (`id_wo`, `nama_wo`, `alamat_wo`, `no_telp`, `layanan`, `kab_kota`) VALUES
(1, 'Indies Organizer Event and Wedding Planner', 'Jalan Sigura-gura VI', '03414561888', 'Event Planner & Wedding Planner', 'Kota Bekasi'),
(2, 'Sido Rabi Wedding Organizer', 'Perumahan Pandanwangi No 10', '085239012331', 'Pernikahan Tradisional, Pernikahan Modern', 'Kota Malang'),
(3, 'Feliz Organizer', 'N3 Perum Graha Mulya', '081333439990', 'Wedding Planner, Wedding Consultant', 'Kota Bogor'),
(4, 'Oppie Wedding Organizer', 'Jl. Sudimoro Kav.4 Perum ABM Malang', '085236147361', 'Pernikahan Tradisional, Pernikahan Modern, Dekorasi', 'Kota Malang'),
(5, 'My Story Event Organizer', 'Jl. Borobudur No.3G, Mojolangu', '08121745428', 'Wedding Planner, Brithday Planner, Event Consultant', 'Kota Malang'),
(6, 'Martha Wedding Organizer', 'Surabaya City, East Java', '081234464078', 'Wedding Planner, Wedding Consultant', 'Kota Surabaya'),
(7, 'Elita Wedding Organizer', 'Perumahan Permata Jingga G19', '082333960285', 'Traditional Wedding Consultant & Planner', 'Kota Malang'),
(8, 'Bless Wedding Surabaya', 'Surabaya City, East Java', '081230583174', 'Wedding Planner, Wedding Consultant', 'Kota Surabaya'),
(9, 'Bless Wedding', 'Jl. Cipta Menanggal Utara No.68, Menanggal', '081230583174', 'Pernikahan Tradisional, Pernikahan Modern, Pernikahan Internasional.', 'Kota Surabaya'),
(10, 'Majestic The Conceptual Wedding', 'Jl. Raya Ngagel Jaya No.59, Pucang Sewu', '(031) 5051116', 'Wedding Consultant, Wedding Planner, MC', 'Kota Surabaya'),
(11, 'Glowy Wedding Organizer', 'Menara Kuningan Lt. 11', '081380405525', 'Wedding Planner', 'Jakarta Barat'),
(12, 'Andie Oyong Project', 'l P.Tubagus Angke Komp Taman Harapan Indah Blok G 3/3 ', '087888392016', 'Persiapan daftar tamu, Pilihan venue, Perencanaan resepsi, Paket pernikahan', 'Jakarta Barat'),
(13, 'Finest Organizer', 'Jl. Mutiara Palem Blok A 19 No. 1 , Cengkareng', '08119780242', '	Wedding Planner, Wedding Organizer, and Reception Only', 'Jakarta Utara'),
(14, 'Hilda by Bridestory', 'Bellezza Shopping Arcade', '08118672444', 'Wedding Consultant', 'Jakarta Timur'),
(15, 'Edelweis Organizer', 'Kebon Kacang 1 No. 73 ', '0818809267', 'Wedding Planner Wedding Organizer Event Organizer', 'Jakarta Pusat'),
(16, 'Varawedding', 'Jl. Wanagiri No. I H Jimbaran ', '0361 4463008', 'Gaya dan Konsep Pernikahan, Persiapan daftar tamu, Pilihan venue, Perencanaan resepsi, Paket pernikahan', 'Kota Denpasar'),
(17, 'White Roses Planner', 'Pertokoan Diponegoro Megah', '081805515604', 'Pilihan venue, Perencanaan resepsi, Gaya dan Konsep Pernikahan', 'Kota Denpasar'),
(18, 'PRIDE Organizer', 'Cikawao Permai Kav. B1/1 ', '08112182810', 'Wedding Planner, Wedding Consultant', 'Kota Bandung'),
(19, 'Vinz Production', 'Ruko Yosodipuro B6, Jalan Yosodipuro 135', '081915357005', 'Perencanaan pernikahan lengkap mulai dari penganggaran, pencarian & pemilihan vendor, membuat jadwal acara.', 'Kota Solo'),
(20, 'Azka Wedding Organizer', 'Sukamulih Kec. Sariwangi', '081321264958', 'Persiapan daftar tamu, Pilihan venue, Perencanaan resepsi, Gaya dan Konsep Pernikahan.', 'Kota Tasikmalaya'),
(21, 'SEDJOLI WEDDING ORGANIZER', 'Griya Indah Bogor, blok L no 6', '08128424524', ' pernikahan internasional, Pernikahan tradisional, Upacara pernikahan dengan tata cara agama tertentu.', 'Kota Bogor'),
(22, 'Sugar Planner', 'Graha Yasa no : D9 Ungaran', '082137425401', 'Hiasan dekorasi, Event Decor, Perencanaan acara, Desain bunga', 'Kota Semarang'),
(23, 'Point One Wedding Organizer', 'Tambak Mas 19 No 76', '087832041313', 'Persiapan daftar tamu, Pilihan venue, Perencanaan resepsi', 'Kota Salatiga'),
(24, 'Alfida Wedding', 'Jl Damar VI Terusan No 12, Pekayon Jaya, Bekasi Selatan', '081210941212', 'Resepsi, Akad nikah, Prewedd, Garden Party', 'Kota Bekasi'),
(25, 'Red Hat Organizer', 'Taman Bromo Jl. Gunung Merak No.9 Lippo Village', '085210456789', 'Wedding Planner, Wedding Consultant, Wedding Organizer, Entertainment & Photobooth', 'Kota Bekasi'),
(26, 'Lily Wedding Services', 'Jalan Raya Uluwatu 97x , Badung', '08589663125', 'Pilihan venue, Perencanaan resepsi', 'Kota Denpasar'),
(27, 'Piong Wedding', 'Jl. Raya Kalimulya No.95', '08545698712', 'ersiapan daftar tamu, Pilihan venue, Perencanaan resepsi', 'Kota Depok'),
(28, 'My Dream Wedding Organizer', 'Perum Griya Mangli Blok O No. 7', '08567718886', 'Persiapan daftar tamu, Pilihan venue, Perencanaan resepsi, Gaya dan Konsep Pernikahan', 'Kabupaten Jember'),
(29, 'Kanaya Wedding Planner', 'Jl. Lumbu Timur Raya no 30 Rt.01/32, Kel. Bojong Rawalumbu', '021-0997867', 'Daftar detail tugas, Persiapan daftar tamu, Pilihan venue, Perencanaan resepsi', 'Kota Bekasi');

--
-- Indexes for dumped tables
--

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

--
-- Indexes for table `tb_jenis`
--
ALTER TABLE `tb_jenis`
  ADD PRIMARY KEY (`id_jenis`);

--
-- Indexes for table `tb_konsumen`
--
ALTER TABLE `tb_konsumen`
  ADD PRIMARY KEY (`id_konsumen`),
  ADD KEY `id_vendor` (`id_vendor`);

--
-- Indexes for table `tb_kota`
--
ALTER TABLE `tb_kota`
  ADD PRIMARY KEY (`kab_kota`),
  ADD UNIQUE KEY `id_kota` (`id_kota`);

--
-- Indexes for table `tb_vendor`
--
ALTER TABLE `tb_vendor`
  ADD PRIMARY KEY (`id_vendor`),
  ADD KEY `id_wo` (`id_wo`);

--
-- Indexes for table `tb_wo`
--
ALTER TABLE `tb_wo`
  ADD PRIMARY KEY (`id_wo`),
  ADD UNIQUE KEY `nama_wo` (`nama_wo`),
  ADD KEY `kab_kota` (`kab_kota`);

--
-- AUTO_INCREMENT for dumped tables
--

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

--
-- AUTO_INCREMENT for table `tb_jenis`
--
ALTER TABLE `tb_jenis`
  MODIFY `id_jenis` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

--
-- AUTO_INCREMENT for table `tb_konsumen`
--
ALTER TABLE `tb_konsumen`
  MODIFY `id_konsumen` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;

--
-- AUTO_INCREMENT for table `tb_kota`
--
ALTER TABLE `tb_kota`
  MODIFY `id_kota` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=33;

--
-- AUTO_INCREMENT for table `tb_vendor`
--
ALTER TABLE `tb_vendor`
  MODIFY `id_vendor` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=34;

--
-- AUTO_INCREMENT for table `tb_wo`
--
ALTER TABLE `tb_wo`
  MODIFY `id_wo` bigint(20) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=30;

--
-- Ketidakleluasaan untuk tabel pelimpahan (Dumped Tables)
--

--
-- Ketidakleluasaan untuk tabel `tb_konsumen`
--
ALTER TABLE `tb_konsumen`
  ADD CONSTRAINT `tb_konsumen_ibfk_1` FOREIGN KEY (`id_vendor`) REFERENCES `tb_vendor` (`id_vendor`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Ketidakleluasaan untuk tabel `tb_vendor`
--
ALTER TABLE `tb_vendor`
  ADD CONSTRAINT `tb_vendor_ibfk_1` FOREIGN KEY (`id_wo`) REFERENCES `tb_wo` (`id_wo`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Ketidakleluasaan untuk tabel `tb_wo`
--
ALTER TABLE `tb_wo`
  ADD CONSTRAINT `tb_wo_ibfk_1` FOREIGN KEY (`kab_kota`) REFERENCES `tb_kota` (`kab_kota`);
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 */;


text:
-- Copyright (c) 2014, 2015, Oracle and/or its affiliates. All rights reserved.
--
-- This program is free software; you can redistribute it and/or modify
-- it under the terms of the GNU General Public License as published by
-- the Free Software Foundation; version 2 of the License.
--
-- This program is distributed in the hope that it will be useful,
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
-- GNU General Public License for more details.
--
-- You should have received a copy of the GNU General Public License
-- along with this program; if not, write to the Free Software
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA

DROP PROCEDURE IF EXISTS ps_setup_reset_to_default;

DELIMITER $$

CREATE DEFINER='mariadb.sys'@'localhost' PROCEDURE ps_setup_reset_to_default (
       IN in_verbose BOOLEAN
    )
    COMMENT '
             Description
             -----------

             Resets the Performance Schema setup to the default settings.

             Parameters
             -----------

             in_verbose (BOOLEAN):
               Whether to print each setup stage (including the SQL) whilst running.

             Example
             -----------

             mysql> CALL sys.ps_setup_reset_to_default(true)\\G
             *************************** 1. row ***************************
             status: Resetting: setup_actors
             DELETE
             FROM performance_schema.setup_actors
              WHERE NOT (HOST = \'%\' AND USER = \'%\' AND ROLE = \'%\')
             1 row in set (0.00 sec)

             *************************** 1. row ***************************
             status: Resetting: setup_actors
             INSERT IGNORE INTO performance_schema.setup_actors
             VALUES (\'%\', \'%\', \'%\')
             1 row in set (0.00 sec)
             ...

             mysql> CALL sys.ps_setup_reset_to_default(false)\\G
             Query OK, 0 rows affected (0.00 sec)
            '
    SQL SECURITY INVOKER
    NOT DETERMINISTIC
    MODIFIES SQL DATA
BEGIN
    SET @query = 'DELETE
                    FROM performance_schema.setup_actors
                   WHERE NOT (HOST = ''%'' AND USER = ''%'' AND ROLE = ''%'')';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_actors\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'INSERT IGNORE INTO performance_schema.setup_actors
                  VALUES (''%'', ''%'', ''%'', ''YES'', ''YES'')';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_actors\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'UPDATE performance_schema.setup_instruments
                     SET ENABLED = sys.ps_is_instrument_default_enabled(NAME),
                         TIMED   = sys.ps_is_instrument_default_timed(NAME)';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_instruments\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'UPDATE performance_schema.setup_consumers
                     SET ENABLED = IF(NAME IN (''events_statements_current'', ''events_transactions_current'', ''global_instrumentation'', ''thread_instrumentation'', ''statements_digest''), ''YES'', ''NO'')';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_consumers\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'DELETE
                    FROM performance_schema.setup_objects
                   WHERE NOT (OBJECT_TYPE IN (''EVENT'', ''FUNCTION'', ''PROCEDURE'', ''TABLE'', ''TRIGGER'') AND OBJECT_NAME = ''%''
                     AND (OBJECT_SCHEMA = ''mysql''              AND ENABLED = ''NO''  AND TIMED = ''NO'' )
                      OR (OBJECT_SCHEMA = ''performance_schema'' AND ENABLED = ''NO''  AND TIMED = ''NO'' )
                      OR (OBJECT_SCHEMA = ''information_schema'' AND ENABLED = ''NO''  AND TIMED = ''NO'' )
                      OR (OBJECT_SCHEMA = ''%''                  AND ENABLED = ''YES'' AND TIMED = ''YES''))';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_objects\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'INSERT IGNORE INTO performance_schema.setup_objects
                  VALUES (''EVENT''    , ''mysql''             , ''%'', ''NO'' , ''NO'' ),
                         (''EVENT''    , ''performance_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''EVENT''    , ''information_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''EVENT''    , ''%''                 , ''%'', ''YES'', ''YES''),
                         (''FUNCTION'' , ''mysql''             , ''%'', ''NO'' , ''NO'' ),
                         (''FUNCTION'' , ''performance_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''FUNCTION'' , ''information_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''FUNCTION'' , ''%''                 , ''%'', ''YES'', ''YES''),
                         (''PROCEDURE'', ''mysql''             , ''%'', ''NO'' , ''NO'' ),
                         (''PROCEDURE'', ''performance_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''PROCEDURE'', ''information_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''PROCEDURE'', ''%''                 , ''%'', ''YES'', ''YES''),
                         (''TABLE''    , ''mysql''             , ''%'', ''NO'' , ''NO'' ),
                         (''TABLE''    , ''performance_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''TABLE''    , ''information_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''TABLE''    , ''%''                 , ''%'', ''YES'', ''YES''),
                         (''TRIGGER''  , ''mysql''             , ''%'', ''NO'' , ''NO'' ),
                         (''TRIGGER''  , ''performance_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''TRIGGER''  , ''information_schema'', ''%'', ''NO'' , ''NO'' ),
                         (''TRIGGER''  , ''%''                 , ''%'', ''YES'', ''YES'')';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: setup_objects\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;

    SET @query = 'UPDATE performance_schema.threads
                     SET INSTRUMENTED = ''YES''';

    IF (in_verbose) THEN
        SELECT CONCAT('Resetting: threads\n', REPLACE(@query, '  ', '')) AS status;
    END IF;

    PREPARE reset_stmt FROM @query;
    EXECUTE reset_stmt;
    DEALLOCATE PREPARE reset_stmt;
END$$

DELIMITER ;


text:
IF OBJECT_ID('finance.create_payment_card') IS NOT NULL
DROP PROCEDURE finance.create_payment_card;

GO

CREATE PROCEDURE finance.create_payment_card
(
    @payment_card_code      national character varying(12),
    @payment_card_name      national character varying(100),
    @card_type_id           integer
)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF NOT EXISTS
    (
        SELECT * FROM finance.payment_cards
        WHERE payment_card_code = @payment_card_code
    )
    BEGIN
        INSERT INTO finance.payment_cards(payment_card_code, payment_card_name, card_type_id)
        SELECT @payment_card_code, @payment_card_name, @card_type_id;
    END
    ELSE
    BEGIN
        UPDATE finance.payment_cards
        SET
            payment_card_code =     @payment_card_code,
            payment_card_name =     @payment_card_name,
            card_type_id =          @card_type_id
        WHERE
            payment_card_code =     @payment_card_code;
    END;
END;

GO


text:
CREATE TABLE users
(
    id    INT         NOT NULL PRIMARY KEY,
    name  VARCHAR(50) NOT NULL,
    email VARCHAR(50)
);

INSERT INTO users (id, name, email)
VALUES (1, 'loic', 'loic@mail.com'),
       (2, 'jean', null),
       (3, 'tim', 'tim@mail.com');

CREATE TABLE categories
(
    id   INT         NOT NULL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

INSERT INTO categories (id, name)
VALUES (1, 'Tech'),
       (2, 'Political');

CREATE TABLE posts
(
    id       INT           NOT NULL PRIMARY KEY,
    title    VARCHAR(50)   NOT NULL,
    text     VARCHAR(4096) NOT NULL,
    date     TIMESTAMP     NOT NULL,
    author   INT           NOT NULL REFERENCES users (id),
    category INT REFERENCES categories (id)
);

INSERT INTO posts (id, title, text, date, author, category)
VALUES (1, 'Happy new year', 'The awful year', TIMESTAMP '2019-12-31 23:59:00', 1, null),
       (2, 'First 2020 post', 'bla bla', TIMESTAMP '2020-01-01 12:00:00', 1, null),
       (3, 'SQL Queries', 'Using jOOQ and Doobie', TIMESTAMP '2020-07-18 16:32:00', 2, 1);

CREATE TABLE featured
(
    post_id INT       NOT NULL REFERENCES posts (id),
    by      INT       NOT NULL REFERENCES users (id),
    start   TIMESTAMP NOT NULL,
    stop    TIMESTAMP NOT NULL,
    UNIQUE (post_id, by)
);

CREATE TABLE kinds
(
    char        CHAR(4),
    varchar     VARCHAR(50),
    timestamp   TIMESTAMP,
    date        DATE,
    boolean     BOOLEAN,
    int         INT,
    bigint      BIGINT,
    double      DOUBLE PRECISION,
    a_long_name INT
);

INSERT INTO kinds (char, varchar, timestamp, date, boolean, int, bigint, double, a_long_name)
VALUES ('char', 'varchar', TIMESTAMP '2020-08-05 10:20:00', DATE '2020-08-05', TRUE, 1, 10, 4.5, 0);


text:
-- using self join (cross join)
SELECT
  t1.name
  ,t1.grade AS 'CS106B'
  ,t2.grade AS 'CS229'
  ,t3.grade AS 'CS224N'
FROM CourseGrade AS t1
    ,CourseGrade AS t2
    ,CourseGrade AS t3
WHERE t1.name = t2.name
  AND t2.name = t3.name
  AND t1.course = 'CS106B'
  AND t2.course = 'CS229'
  AND t3.course = 'CS224N';

-- using self join (inner join)
SELECT
  t1.name
  ,t1.grade AS 'CS106B'
  ,t2.grade AS 'CS229'
  ,t3.grade AS 'CS224N'
FROM CourseGrade AS t1
JOIN CourseGrade AS t2
  ON t1.course = 'CS106B'
 AND t2.course = 'CS229'
 AND t1.name = t2.name
JOIN CourseGrade AS t3
  ON t3.course = 'CS224N'
 AND t2.name = t3.name;

-- using case statement and aggregation
SELECT
  name
  ,MAX(CASE WHEN course = 'CS106B' THEN grade ELSE NULL END) AS 'CS106B'
  ,MAX(CASE WHEN course = 'CS229' THEN grade ELSE NULL END) AS 'CS229'
  ,MAX(CASE WHEN course = 'CS224N' THEN grade ELSE NULL END) AS 'CS224N'
FROM CourseGrade
GROUP BY name;


text:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		richa
-- Create date: 2013-03-06
-- Description:	Update Azure entity index entry
-- =============================================
USE IndexDatastore;
IF OBJECT_ID('dbo.UpdateEntityIndexEntry') IS NULL
	EXEC('CREATE PROCEDURE dbo.UpdateEntityIndexEntry AS BEGIN SET NOCOUNT ON; END')
GO

ALTER PROCEDURE dbo.UpdateEntityIndexEntry
	@ExternalEntityId UNIQUEIDENTIFIER,
	@Version INT,
	@TimeStamp DATETIME,
	@StorageAccountName VARCHAR(120),
	@TableName VARCHAR(120),
	@Partition VARCHAR(120),
	@RowId UNIQUEIDENTIFIER,
	@AssociationList dbo.AssociationListParam READONLY,
	@LastModifiedUser NVARCHAR(120),
	@SchemaVersion INT,
	@ExternalName NVARCHAR(120),
	@ExternalType NVARCHAR(120),
	@EntityCategory NVARCHAR(120),
	@Active BIT
AS
BEGIN
	SET NOCOUNT ON;

	-- Adding the record to the key fields table and updating the entity index must succeed together.
	-- Either can fail if an intervening update has caused the entity version to roll to the version
	-- we are trying to add.
	BEGIN TRANSACTION UpdateEntityIndex

	-- For now we always update storage type
	DECLARE @StorageType VARCHAR(20);
	EXEC dbo.StorageAccount_GetStorageType @StorageAccountName, @StorageType OUTPUT;

	-- Add new entry to the key fields table.
	-- Set up a table variable to receive the result that we don't want in our own result
	DECLARE @DropItOnTheFloor TABLE (
		xId UNIQUEIDENTIFIER,
		LocalVersion INT,
		StorageAccountName VARCHAR(120),
		TableName VARCHAR(120),
		Partition VARCHAR(120),
		RowId UNIQUEIDENTIFIER,
		VersionTimestamp DATETIME);
	INSERT @DropItOnTheFloor EXEC dbo.KeyFields_InsertAzureKeyFields @ExternalEntityId, @StorageAccountName, @TableName, @Partition, @RowId, @Version, @TimeStamp

	IF NOT @@ERROR = 0
	BEGIN
		ROLLBACK TRANSACTION UpdateEntityIndex
		RETURN;
	END

	-- Add associations to association cache.
	-- Set up a table variable to receive the result that we don't want in our own result
	DECLARE @DropItOnTheFloorAssoc TABLE(
		xId uniqueidentifier,
		xIdTarget uniqueidentifier,
		Version int,
		ExternalName nvarchar(120),
		AssociationType varchar(15),
		Details nvarchar(240) NULL
	);
	INSERT @DropItOnTheFloorAssoc EXEC dbo.UpdateAssociationsCurrent @ExternalEntityId, @AssociationList

	IF NOT @@ERROR = 0
	BEGIN
		ROLLBACK TRANSACTION UpdateEntityIndex
		RETURN;
	END

	DECLARE @EntityExists INT;
	SELECT @EntityExists = COUNT(*) FROM dbo.EntityId WHERE xId = @ExternalEntityId;

	IF @EntityExists = 0
		BEGIN
			-- Insert the entity index entry
			DECLARE @InitialVersion INT = 0;
			DECLARE @InitialActive BIT = 1;
			DECLARE @WriteLock BIT = 0;
			INSERT INTO dbo.EntityId (xId, StorageType, Version, CreateDate, LastModifiedDate, WriteLock, HomeStorageAccountName,
				LastModifiedUser, SchemaVersion, ExternalName, ExternalType, EntityCategory, Active)
			VALUES (@ExternalEntityId, @StorageType, @InitialVersion, @TimeStamp, @TimeStamp, @WriteLock, @StorageAccountName,
				 @LastModifiedUser, @SchemaVersion, @ExternalName, @ExternalType, @EntityCategory, @InitialActive)
		END
	ELSE
		BEGIN
			-- If we are incrementing the version by more than one an intervening update has occured on this record
			-- and we should fail
			DECLARE @CurrentVersion INT;
			SELECT @CurrentVersion = @Version - 1;

			-- Update the entity index entry
			UPDATE dbo.EntityId
			SET Version = @Version, LastModifiedDate = @TimeStamp, HomeStorageAccountName = @StorageAccountName, StorageType = @StorageType,
				LastModifiedUser = @LastModifiedUser, SchemaVersion = @SchemaVersion, ExternalName = @ExternalName, ExternalType = @ExternalType,
				EntityCategory = @EntityCategory, Active = @Active
			WHERE xId = @ExternalEntityId AND Version = @CurrentVersion;
		END

	IF @@ROWCOUNT = 0
	BEGIN
		ROLLBACK TRANSACTION UpdateEntityIndex
		RAISERROR ('Update failed to modify the entity index record.', 16, 1);
		RETURN;
	END

	COMMIT TRANSACTION UpdateEntityIndex

	RETURN;
END
GO


text:
BEGIN TRANSACTION;

-- Description: Adds a pair of many-to-many tables to indicate contributors
-- and administrators of projects.
-- This is the first-pass at a permissions mechanism.  It probably won't last
-- too long, because I'd like to use role-based permissions in the long-term.
-- However, I don't want to put the design of that mechanism ahead of basic
-- permissions.  So this is a short-term solution to be refined later.

CREATE TABLE project_to_contributor_user (
    project_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, project_id),
    CONSTRAINT fk_project_has_contributors FOREIGN KEY (user_id) REFERENCES user,
    CONSTRAINT fk_user_has_contributor_to FOREIGN KEY (project_id) REFERENCES project
);

CREATE TABLE project_to_administrator_user (
    project_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    PRIMARY KEY (user_id, project_id),
    CONSTRAINT fk_project_has_administrators FOREIGN KEY (user_id) REFERENCES user,
    CONSTRAINT fk_user_has_administrator_of FOREIGN KEY (project_id) REFERENCES project
);

COMMIT TRANSACTION;


text:
/*We want to reward our users who have been around the longest.
Find the 5 oldest users.*/
SELECT * FROM users
ORDER BY created_at
LIMIT 5;

/*What day of the week do most users register on?
We need to figure out when to schedule an ad campgain*/
SELECT date_format(created_at,'%W') AS 'day of the week', COUNT(*) AS 'total registration'
FROM users
GROUP BY 1
ORDER BY 2 DESC;

/*version 2*/
SELECT
    DAYNAME(created_at) AS day,
    COUNT(*) AS total
FROM users
GROUP BY day
ORDER BY total DESC
LIMIT 2;

/*We want to target our inactive users with an email campaign.
Find the users who have never posted a photo*/
SELECT username
FROM users
LEFT JOIN photos ON users.id = photos.user_id
WHERE photos.id IS NULL;

/*We're running a new contest to see who can get the most likes on a single photo.
WHO WON??!!*/
SELECT users.username,photos.id,photos.image_url,COUNT(*) AS Total_Likes
FROM likes
JOIN photos ON photos.id = likes.photo_id
JOIN users ON users.id = likes.user_id
GROUP BY photos.id
ORDER BY Total_Likes DESC
LIMIT 1;

/*version 2*/
SELECT
    username,
    photos.id,
    photos.image_url,
    COUNT(*) AS total
FROM photos
INNER JOIN likes
    ON likes.photo_id = photos.id
INNER JOIN users
    ON photos.user_id = users.id
GROUP BY photos.id
ORDER BY total DESC
LIMIT 1;

/*Our Investors want to know...
How many times does the average user post?*/
/*total number of photos/total number of users*/
SELECT ROUND((SELECT COUNT(*)FROM photos)/(SELECT COUNT(*) FROM users),2);

/*user ranking by postings higher to lower*/
SELECT users.username,COUNT(photos.image_url)
FROM users
JOIN photos ON users.id = photos.user_id
GROUP BY users.id
ORDER BY 2 DESC;

/*Total Posts by users (longer versionof SELECT COUNT(*)FROM photos) */
SELECT SUM(user_posts.total_posts_per_user)
FROM (SELECT users.username,COUNT(photos.image_url) AS total_posts_per_user
		FROM users
		JOIN photos ON users.id = photos.user_id
		GROUP BY users.id) AS user_posts;

/*total numbers of users who have posted at least one time */
SELECT COUNT(DISTINCT(users.id)) AS total_number_of_users_with_posts
FROM users
JOIN photos ON users.id = photos.user_id;

/*A brand wants to know which hashtags to use in a post
What are the top 5 most commonly used hashtags?*/
SELECT tag_name, COUNT(tag_name) AS total
FROM tags
JOIN photo_tags ON tags.id = photo_tags.tag_id
GROUP BY tags.id
ORDER BY total DESC;

/*We have a small problem with bots on our site...
Find users who have liked every single photo on the site*/
SELECT users.id,username, COUNT(users.id) As total_likes_by_user
FROM users
JOIN likes ON users.id = likes.user_id
GROUP BY users.id
HAVING total_likes_by_user = (SELECT COUNT(*) FROM photos);

/*We also have a problem with celebrities
Find users who have never commented on a photo*/
SELECT username,comment_text
FROM users
LEFT JOIN comments ON users.id = comments.user_id
GROUP BY users.id
HAVING comment_text IS NULL;

SELECT COUNT(*) FROM
(SELECT username,comment_text
	FROM users
	LEFT JOIN comments ON users.id = comments.user_id
	GROUP BY users.id
	HAVING comment_text IS NULL) AS total_number_of_users_without_comments;

/*Mega Challenges
Are we overrun with bots and celebrity accounts?
Find the percentage of our users who have either never commented on a photo or have commented on every photo*/

SELECT tableA.total_A AS 'Number Of Users who never commented',
		(tableA.total_A/(SELECT COUNT(*) FROM users))*100 AS '%',
		tableB.total_B AS 'Number of Users who likes every photos',
		(tableB.total_B/(SELECT COUNT(*) FROM users))*100 AS '%'
FROM
	(
		SELECT COUNT(*) AS total_A FROM
			(SELECT username,comment_text
				FROM users
				LEFT JOIN comments ON users.id = comments.user_id
				GROUP BY users.id
				HAVING comment_text IS NULL) AS total_number_of_users_without_comments
	) AS tableA
	JOIN
	(
		SELECT COUNT(*) AS total_B FROM
			(SELECT users.id,username, COUNT(users.id) As total_likes_by_user
				FROM users
				JOIN likes ON users.id = likes.user_id
				GROUP BY users.id
				HAVING total_likes_by_user = (SELECT COUNT(*) FROM photos)) AS total_number_users_likes_every_photos
	)AS tableB;

/*Find users who have ever commented on a photo*/
SELECT username,comment_text
FROM users
LEFT JOIN comments ON users.id = comments.user_id
GROUP BY users.id
HAVING comment_text IS NOT NULL;

SELECT COUNT(*) FROM
(SELECT username,comment_text
	FROM users
	LEFT JOIN comments ON users.id = comments.user_id
	GROUP BY users.id
	HAVING comment_text IS NOT NULL) AS total_number_users_with_comments;

/*Are we overrun with bots and celebrity accounts?
Find the percentage of our users who have either never commented on a photo or have commented on photos before*/

SELECT tableA.total_A AS 'Number Of Users who never commented',
		(tableA.total_A/(SELECT COUNT(*) FROM users))*100 AS '%',
		tableB.total_B AS 'Number of Users who commented on photos',
		(tableB.total_B/(SELECT COUNT(*) FROM users))*100 AS '%'
FROM
	(
		SELECT COUNT(*) AS total_A FROM
			(SELECT username,comment_text
				FROM users
				LEFT JOIN comments ON users.id = comments.user_id
				GROUP BY users.id
				HAVING comment_text IS NULL) AS total_number_of_users_without_comments
	) AS tableA
	JOIN
	(
		SELECT COUNT(*) AS total_B FROM
			(SELECT username,comment_text
				FROM users
				LEFT JOIN comments ON users.id = comments.user_id
				GROUP BY users.id
				HAVING comment_text IS NOT NULL) AS total_number_users_with_comments
	)AS tableB